"Pop" Record -- Select And Delete In The Same Statement (SQL Server 2005)

Question

I have a C# application that is used by about a dozen employees simultaneously. It simply SELECTs the top record from a SQL Server 2005 table, DELETE it, then displays the data on a form.
As soon as the record is selected it needs to be deleted so 2 ppl don't grab and work on the same record.
Fairly Straight Forward...

I found a suggestion a while ago (cant find the site i got it from, sorry) to do a SELECT and DELETE in the same statement and execute a SqlCommand.ExecuteReader() with this "compound" statement:

SELECT TOP 1 * 
FROM Call_Table 
WHERE Call_Table.hold_call_till <= GetDate() 
ORDER BY Call_Table.attempts ASC, Call_Table.custno ASC;
DELETE 
FROM Call_Table
WHERE Call_Table.custno = (SELECT TOP 1 Call_Table.custno 
                           FROM Call_Table 
                           WHERE Call_Table.hold_call_till <= GetDate() 
                           ORDER BY Call_Table.attempts ASC, Call_Table.custno ASC);

Its been working quite well so far, but i feel like i've just been lucky. We're hiring about 5 new ppl, and i would like to totally sure this will continue to work.

Im interested to hear the opinion of more seasoned vets in this area.

Should I stick with the "If its not broken, dont fix it" approach?? Or should I step up my game and use some kind of record_locks or stored procs??

Any suggestions will be openly accepted. I can supply more info about the table or C# application if necessary.

Answer 1

I would suggest using some sort of optimistic concurrency control instead. Do not delete the record, but instead store a timestamp (or some other versioning technique) which will be grabbed on select. Then, if the user edits something you can check to make sure the timestamp has not changed. If it has, then prompt the user and reload the new data. If it has not, then save the data, and update the timestamp so that anybody else with the data will not overwrite your changes.

Microsoft's article on optimistic concurrency

Here is my version of a graphical example (using a numbering system instead of timestamps for the versioning)

Name|PhoneNum|Version
Joe |123-1234|1

• UserA and UserB pull up Joe's data in your UI

• UserA modifies and saves Joe

Now the table looks like this:

Name|PhoneNum|Version
Joe |555-5555|2

• UserB modifies the Version 1 of Joe's data and saves. The save sees that the Version is now 2 and prompts UserB that the data has changed.

UPDATE

You have two options if the row cannot be accessed while the User is reading it.

• You can do what you are already doing, but you do not need the select...delete due to OUTPUT (as suggested above already). With this, you can delete and get the value.

Code:

DECLARE @CallInfo TABLE (/*Call_Table Schema*/)

DELETE Call_Table
OUTPUT DELETED.* INTO @CallInfo 
WHERE Call_Table.custno = (SELECT TOP 1 Call_Table.custno 
                       FROM Call_Table 
                       WHERE Call_Table.hold_call_till <= GetDate() 
                       ORDER BY Call_Table.attempts ASC, Call_Table.custno ASC)

--The @CallInfo will have the info that you just deleted, so pass that back to the UI
SELECT * FROM @CallInfo

• You can add a lock column (using the user id of the person locking) that you update right before you select the data. The only thing you then need to worry about it if the user crashes or does something where they do not reset the lock. I have used this system in the past, but the complexity might not be worth it if most of the time the data is deleted. I find this more useful if you need to simply update data. You can add logic that says, if the same user tries to get the data again, then let them since it was probably a crash. Otherwise, you then need to create an unlocking system.

Answer 2

Seems like what you need is hint UPDLOCK coupled with the READPAST hint as described here http://www.adathedev.co.uk/2010/03/queue-table-processing-in-sql-server.html