Help with Data Concurrency - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Help with Data Concurrency

Author

Topic

safecoder@gmail.com
Starting Member

22 Posts

Posted - 2005-08-23 : 09:00:10

Hello...We have a problem with our stored procedure...We will call this stored procedure from application to insert a new record..but when two or more users hit the Create button @ same time, we have Data concurrency issue...Because the function is returning the same @ID value for two threads....

For some reasons, we cannot handle this on Application end, instead we have to handle in Data layer...

The function, GetUnusedTeamID , basically joins the lookup tables(seqids) and Teams table and returns the minimum "UNUSED" lookup value to be inserted ....

we used "ROWLOCK","NOLOCK","SERIALIZABLE"....But still we ended up getting duplicate value...I don't think the WHILE (@ErrorSave <> 0) is not looping if the insert fails...

Any hints? Thanks//

********************************************************************
CREATE PROCEDURE dbo.usp_createteam
@Desc varchar(30), @CreatedBy varchar(8), @Owner varchar(8) = NULL, @ID int OUTPUT AS
--set Transaction isolation level serializable
DECLARE @ErrorSave INT
SET @ErrorSave = 1
Begin Transaction
WHILE (@ErrorSave <> 0)
BEGIN
IF (@Owner is NULL)
BEGIN
set @ID = dbo.GetUnusedTeamID ( )
INSERT INTO Teams (TeamID,Description, Owner, CreatedBy, ModifiedBy)
VALUES (@ID,@Desc, @CreatedBy, @CreatedBy,@CreatedBy)
SET @errorSave = @@Error
END
Commit
GO

********************************************************************
CREATE FUNCTION dbo.GetUnusedTeamID ( )
RETURNS int AS
BEGIN
declare @rval int
set @rval = (select min(seqid) from seqids with(nolock) where seqid not in(select teamid from teams with(nolock)))
return (@rval)
END
********************************************************************

SamC
White Water Yakist

3467 Posts

Posted - 2005-08-23 : 10:44:49

SQL provides built-in mechanisms to create and return unique ID fields. A quick glance at your code shows a design that doesn't handle "race" conditions. It will return duplicate ID values as you have reported.

Without knowing your design constraints I don't know if it is better to rewrite using the SQL IDENTITY attribute to generate unique values or to try to close the holes in the code you have.

My knee jerk would be to redesign the Teams table such that the column TeamID was INT IDENTITY(N, M) UNIQUE. If you could live with that, the race conditions are handled.

If you need to fix the code you have, you would need to ensure that TeamID was already in the table when it was selected. Clear as mud? Something like:

CREATE FUNCTION dbo.GetUnusedTeamID ( )
RETURNS int AS
BEGIN
INSERT INTO Teams (TeamID)
  SELECT MIN(seqID) FROM seqids WHERE seqid NOT IN (SELECT TeamID FROM teams)
return (@@IDENTITY) -- This won't work. More on this later.
END

This solution inserts the TeamID in a single statement such that any "race" couldn't select the same value twice. The problem is, unless table Teams has a primary key IDENTITY on column TeamID, then @@IDENTITY isn't going to return the value of TeamID.

My next vote is to get rid of the function GetUnusedTeamID and perform the operation in the calling procedure like this:

CREATE PROCEDURE dbo.usp_createteam
@Desc varchar(30), @CreatedBy varchar(8), @Owner varchar(8) = NULL, @ID int OUTPUT AS
--set Transaction isolation level serializable
IF (@Owner is NULL)
BEGIN
  INSERT INTO Teams (TeamID,Description, Owner, CreatedBy, ModifiedBy)
    SELECT MIN(seqid), @Desc, @CreatedBy, @CreatedBy,@CreatedBy
      FROM seqids WHERE seqid NOT IN (SELECT TeamID FROM teams)
END
GO

safecoder@gmail.com
Starting Member

22 Posts

Posted - 2005-08-23 : 12:27:27

well...I forgot to mention...WE CANNOT USE IDENTITY COLUMN because of some "Mainframe program Restriction"....It is a long lengthy issue...But, yeah, bottomline is , we cannot use IDENTITY Column...

2) Keeping IDENTITY aside, Can we avoid this Race condition even if we move the Code out of function and into the Stored procedure inline code????

Thanks a ton for Reply...

SamC
White Water Yakist

3467 Posts

Posted - 2005-08-23 : 13:29:22

quote:
Originally posted by safecoder@gmail.com

Keeping IDENTITY aside, Can we avoid this Race condition even if we move the Code out of function and into the Stored procedure inline code????

Did you try the solution posted? Is there anything wrong with the solution above?

I overlooked returning the OUTPUT parameter @ID set to the value of the INSERTed column TeamID. I don't know if there's a way out of this problem without adding an IDENTITY column to access the row which was inserted. This added column should not create a problem for your mainframe requirements as it is not a returned value.

Keeping column TeamID as-is for your mainframe requirements, add a column to table Teams

CREATE Teams (
PKID INT IDENTITY(1,1) NOT NULL UNIQUE,
... other columns here

With column PKID, you can solve the problem like this:

CREATE PROCEDURE dbo.usp_createteam
@Desc varchar(30), 
@CreatedBy varchar(8), 
@Owner varchar(8) = NULL, 
@ID int OUTPUT 
AS
IF (@Owner is NULL) -- What happens if @Owner is not null?
BEGIN
  INSERT INTO Teams (TeamID,Description, Owner, CreatedBy, ModifiedBy)
    SELECT MIN(seqid), @Desc, @CreatedBy, @CreatedBy, @CreatedBy
      FROM seqids WHERE seqid NOT IN (SELECT TeamID FROM teams)

  SELECT @ID = TeamID -- Set the OUTPUT parameter value
    FROM Teams
    WHERE PKID = SCOPE_IDENTITY() -- Or WHERE PKID = @@IDENTITY, your choice
END
RETURN
GO

safecoder@gmail.com
Starting Member

22 Posts

Posted - 2005-08-24 : 08:55:53

Sorry for late reply..Was stuck with something...I guess it still comes up with the problem...Anyway, thanks for your help and time...Let me know if you come up with any idea.. In the mean time, i will keep working on it..thanks

SamC
White Water Yakist

3467 Posts

Posted - 2005-08-24 : 13:06:28

quote:
Originally posted by safecoder@gmail.com

I guess it still comes up with the problem

Hmmm... Post your solution that still comes up with the problem?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources