SQL server table locking and unlocking issue

Question

I have a very interesting problem.

I have a table where I have data, like this: DVDSerialNumbers(ID, Create_Date, SerialNumber, CategoryID)

My application generates serial numbers for DVD's and I have a SQL command like this (issued by my application):

ExecuteQuery("select top {0} SerialNumber from DVDSerialNumbers where CategoryID = {1};" & 
"delete from DVDSerialNumbers where ID in (select top{0} ID from DVDSerialNumber where " & 
 "CategoryID = {1});", n, CategoryID)

ExecuteQuery returns the result of my select query but the delete command is executed too.

Basically I get no more than n SerialNumbers which have the given CategoryID and I delete their rows.

However, here I have a concurency problem. If the code above runs twice in the same time, it's possible that the results will be the same, however, the idea is to get a given SerialNumber from the table only once.

How can I make instance B to wait for instance A to finish this command? Should I lock the table? Or should I lock some rows? Or is there a better solution?

Thanks in advance for your help.

What is your database isolation level setting? You'll need to know this before trying to optimise queries. — Neil Fenwick, Aug 26 '11 at 10:52
@Neil Fenwick: not needed: this isn't an isolation level issue — gbn, Aug 26 '11 at 10:54

score 3 · Accepted Answer · edited May 23 '17 at 12:26

3

You need READPAST, UPDLOCK, ROWLOCK hints. See this for more: SQL Server Process Queue Race Condition

Then you'd need a transaction for the SELECT/DELETE. Instead, you can do this in one statement with an OUTPUT clause

ExecuteQuery("delete top ({0})
                  DVDSerialNumbers WITH (READPAST, UPDLOCK, ROWLOCK)
              OUTPUT DELETED.SerialNumber
              where CategoryID = {1}" & 
                , n, CategoryID)

edited May 23 '17 at 12:26

Community

1
1

answered Aug 26 '11 at 10:50

gbn

422,506
82
585
676

Thank you for your answer, I'll think about this and will send you a reply soon. Thanks again. – Lajos Arpad Aug 26 '11 at 10:59
In fact this is an excellent answer, I think this is the solution. There is a syntax error though regarding the CategoryID, but the idea is excellent. Thank you again. – Lajos Arpad Aug 26 '11 at 11:03

SQL server table locking and unlocking issue

1 Answers1