Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am not even sure this question is necessary but I am curious to know everyone's thoughts. I have two databases on the same server, dbFoo, dbBar. dbFoo has the following table please note this is a way dumbed down example, and the syntax may not be correct as I am rushing and way more interested in the answer to the underlying issue then the code to do it...

CREATE TABLE dbo.CodeNumbers(
CodeNumbersID INT IDENTITY (1,1) NOT NULL PRIMARY KEY,
CodeValue VARCHAR(30) NOT NULL
IsUsed BIT NOT NULL DEFAULT(0)
);

dbo.CodeNumbers gets populated with a monthly CSV provided, the import method of your choice is already written to get them in there. We are NEVER given a duplicate code.

Lets assume for arguments sake we have 10,000,000 rows in the table. that all follow this format when imported:

1, 'ajdirjfisofklrlfo039402', 0 all the way till
10000000, 'fkeiir9489', 0

Now in dbBar I have 2 stored procedures the first should access the first un-used code in dbFoo, return it in an out variable and mark it as used. So I have something like:

CREATE PROCEDURE GetNextUseableCode
   @CodeOut VARCHAR(30) OUTPUT,
   @CID INT OUTPUT
AS
   SELECT @CID = CodeNumbersID, @CodeOut = CodeValue
   FROM dbFoo.dbo.CodeNumbers
   WHERE IsUsed = 0

   UPDATE dbFoo.dbo.CodeNumbers
   SET IsUsed = 1
   WHERE CodeNumbersID = @CID 

The code that calls the procedure from dbBar is accessed by 200k sessions a day at various times. When dbFoo.Codes has no more to return, its fine all is well and good, the application is simply told sorry no more check back tomorrow.

I have 3 main questions..

  1. Is there anything special I would need to have in the code to avoid race conditions, and if so what would be best to handle for this without bringing the system to its knees.

  2. Is their an efficient way to ensure the next code grabbed whenever the procedure is called, is the next one in chronological order within the ID column.

  3. Is there any other concerns that I am not looking at right now that could issue big problems, and what would be an eloquent way to handle this situation?

I understand this is a long pretty open ended question, I have some coded up solutions, but I feel like there are much better ways to get the results I want.

Thanks in advance as always for all help.

share|improve this question
3  
    
Have you considered using a message queue - that could be a better tool for this task. –  A-K Feb 9 at 14:37
    
The articles are very informative, but can you explain to me and sorry for the silly question but why a message queue would be better for the task have mentioned? I am not worried about the insertion of rows into dbo.Codes, Rows will also never be deleted from this table. The IsActive flag will be set to true once a row is used. my primary concern is how to keep multiple sessions from reading the same row and screwing over with an ugly failure the second session that happened to be unfortunate enough to read the same row. –  deadLock Feb 10 at 18:27

1 Answer 1

up vote 9 down vote accepted

There is nothing in your SELECT that dictates order at all. It is also not protected from two sessions reading the same row. To see that it is not safe:

  1. Create a throwaway table with a key column.

    CREATE TABLE dbo.GeneratedIDs(ID INT PRIMARY KEY);
    
  2. Run this code in a loop from two different sessions:

    SET NOCOUNT ON;
    
    DECLARE @DECLARE @CID INT, @CodeOUt VARCHAR(30);
    
    SELECT @CID = CodeNumbersID, @CodeOut = CodeValue
      FROM dbFoo.dbo.CodeNumbers
      WHERE IsUsed = 0
    
    UPDATE dbFoo.dbo.CodeNumbers
      SET IsUsed = 1
      WHERE CodeNumbersID = @CID 
    
    INSERT dbo.GeneratedIDs SELECT @CID;
    
    GO 100000
    
  3. Check the output for these - they'll happen:

    Msg 2627, Level 14, State 1
    Violation of PRIMARY KEY constraint 'PK_hexcode'. Cannot insert duplicate key in object 'dbo.GeneratedIDs'. The duplicate key value is (<some value>).

    Or you might see deadlocks if you wrap the SELECT/UPDATE in an explicit transaction:

    Msg 1205, Level 13, State 52
    Transaction (Process ID 57) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

To get around this problem, and to ensure that the ID you get is the lowest one available, you could do this:

BEGIN TRANSACTION;

SELECT TOP (1) @CID = ...
FROM dbFoo.dbo.CodeNumbers WITH (XLOCK, HOLDLOCK)
WHERE IsUsed = 0
ORDER BY CodeNumbersID;

UPDATE ...

COMMIT TRANSACTION;

Note that I added an explicit transaction and also XLOCK/HOLDLOCK hints to prevent two simultaneous sessions from reading the same row. Of course, this has an impact on concurrency (which, unfortunately, is exactly what you want and need here).

Other ways to do this include just updating the row and then using a table variable to capture values from the OUTPUT clause:

DECLARE @x TABLE(CodeOut VARCHAR(30), CID INT);

;WITH x AS 
(
  SELECT TOP (1) CodeNumbersID, CodeValue, IsUsed
    FROM dbFoo.dbo.CodeNumbers WITH (XLOCK, HOLDLOCK)
    WHERE IsUsed = 0
    ORDER BY CodeNumbersID
)
UPDATE x SET IsUsed = 1
  OUTPUT inserted.CodeValue, inserted.CodeNumbersID INTO @x;

SELECT @CodeOut = CodeOut, @CID = CID FROM @x;

Per Paul's update, yes, you could also do this without the table variable:

;WITH x AS 
(
  SELECT TOP (1) CodeNumbersID, CodeValue, IsUsed
    FROM dbFoo.dbo.CodeNumbers WITH (XLOCK, HOLDLOCK)
    WHERE IsUsed = 0
    ORDER BY CodeNumbersID
)
UPDATE x 
  SET @CodeOut = x.CodeValue, @CID = x.CodeNumbersID, IsUsed = 1;

(Though I am not all that fond of this syntax; not sure why. Might be the same reason I always forget it exists.)

You could change the caller to expect a resultset instead of two output parameters, but that is extra work too. In both cases you'd still need to ensure you got the lowest ID available, which likely means a CTE with a SELECT and the same hints. Some discussion here. I also talk about a similar approach in this blog post but I didn't get into anything about concurrency and two sessions trying to delete the same row at the same time. Obviously in that case only one of them can win, but with an UPDATE they could both manage to be successful (at least in theory).

To make things easier, you could relax the restriction that the "next" ID handed out is the lowest ID available. But you still need the isolation through the hints to ensure that two simultaneous sessions don't happen to read the same value (which could happen regardless of ordering). Hopefully with a suitable index these won't destroy concurrency.

share|improve this answer
    
I am going to try to implement the solution mentioned here. Just so I am clear using the XLOCK and HOLDLOCK hints, from what I have read will ensure that the same record cannot be read by another session, however it will not effect other sessions from pulling the next value. Is that correct? I want to ensure both no deadlocks happen as well as sessions waiting around for one session to finish. –  deadLock Feb 10 at 18:33
    
Aaron, that blog post is excellent by the way. I would love to add a link to it to my own blog if you do not mind. I have heard the question a million times. –  deadLock Feb 10 at 19:14
    
The solution seemed to work, however just to add one more item to the question. IF I am reading tech-net correctly wouldn't it be more efficient to use WITH(ROWLOCK) instead of the XLOCK,HOLDLOCK combination. If I am missing something let me know. It seems that the ROWLOCK hint will allow me to lock the row which is read and still allow other threads calling in to access the table allowing more concurrency. Is that not correct? As always thanks for your help and awesome explanation. –  deadLock Mar 18 at 22:52
    
@deadLock what does "more concurrency" really mean there? What row do you expect them to read in between the time you read TOP (1) ORDER BY CodeNumbersID and the time you update that row? Before it's updated, TOP (1) ORDER BY CodeNumbersID is the same row. –  Aaron Bertrand Mar 19 at 1:49
    
you raise a valid point. i guess the brain is not ALWAYS working. Thanks for the help. –  deadLock Mar 31 at 13:49

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.