Locking and Unlocking a SQL Server Table

Question

I have a very interesting problem.

I have a table where I have data, for example: DVDSerialNumbers (ID, Create_Date, SerialNumber, CategoryID)

My application generates serial numbers for the DVD, and I have an 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 also executes.

Basically, I get no more than n serial numbers that have a given category code, and I delete their lines.

However, here I have a problem with the agreement. If the above code is executed twice at the same time, it is possible that the results will be the same, but the idea is to get the given SerialNumber from the table only once.

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

Thanks in advance for your help.

Answer 1

You need the hints READPAST, UPDLOCK, ROWLOCK. See This For More: SQL Server Queue Queue Status

Then you need a transaction for SELECT / DELETE. Instead, you can do this in a single expression using the OUTPUT clause

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