Pop entry - select and delete in the same statement (SQL Server 2005)

Question

I have a C # application that is used by about a dozen employees at a time. It simply selects the top row from the SQL Server 2005 table, deletes it, and then displays the data in the form.
Once a record is selected, it must be deleted, so 2 ppl do not capture and do not work on the same record. Right straight ahead ...

I found a sentence some time ago (I can’t find the site from which I received it, sorry) to make SELECT and DELETE in the same expression and execute SqlCommand.ExecuteReader () using this “compound”:

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); 

So far it has worked very well, but I feel like I just got lucky. We are hiring about 5 new ppl and I would like to make sure that this will continue to work.

I am interested in hearing the opinions of more experienced veterinarians in this area.

Should I stick with the approach “If you haven’t broken it, don’t fix it”? Or should I activate my game and use some recordings or saved procs ??

Any suggestions will be openly accepted. If necessary, I can provide additional information about a C # table or application.

Answer 1

I would suggest using an optimistic concurrency control instead. Do not delete the entry, but instead store the timestamp (or other version control technique) that will be captured when selected. Then, if the user is editing something, you can check that the timestamp has not changed. If there is one, ask the user and reload the new data. If this does not happen, save the data and update the timestamp so that no one else overwrites your changes with the data.

Microsoft article optimistic about concurrency

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

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

• UserA and UserB pull up Joe data in user interface

• UserA modifies and saves Joe

Now the table looks like this:

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

• UserB modifies Joe version 1 data and saves it. Save sees that version is now 2 and suggests to UserB that the data has changed.

UPDATE

You have two options if the line cannot be accessed while the user is reading it.

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

The 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 lock user id) that you update right before you select the data. The only thing you then need to worry about is 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 may not be worth it if most of the time the data is deleted. I find this more useful if you just need to update the data. You can add logic that says if the same user tries to retrieve the data again, and then let them in, as that was probably a failure. Otherwise, you need to create an unlock system.

Answer 2

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

Answer 3

You can use the OUTPUT clause of a DELETE expression to delete a string and return it in the same expression, for example:

 delete queue output deleted.* where id = (select min(id) from queue) 

Assuming you have a table that looks something like this:

 create table Queue( ID bigint identity not null primary key, Data varchar(max) )