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
– Neil Fenwick, Commented Aug 26, 2011 at 10:52
@Neil Fenwick: not needed: this isn't an isolation level issue — gbn
– gbn, Commented Aug 26, 2011 at 10:54

Community · Accepted Answer · 2017-05-23 12:26:54Z

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, 2017 at 12:26

CommunityBot

11 silver badge

answered Aug 26, 2011 at 10:50

gbn

434k84 gold badges602 silver badges690 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Lajos Arpad Over a year ago

Thank you for your answer, I'll think about this and will send you a reply soon. Thanks again.

Lajos Arpad Over a year ago

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.

Collectives™ on Stack Overflow

SQL server table locking and unlocking issue

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related