SQL Server SELECT/UPDATE Stored Procedure Weirdness

Question

I have a table I'm using as a work queue. Essentially, it consists of a primary key, a piece of data, and a status flag (processed/unprocessed). I have multiple processes trying to grab the next unprocessed row, so I need to make sure that they observe proper lock and update semantics to avoid race condition nastiness. To that end, I've defined a stored procedure they can call:

CREATE PROCEDURE get_from_q
AS
DECLARE @queueid INT
BEGIN TRANSACTION TRAN1
SELECT TOP 1 @queueid = id FROM MSG_Q WITH (updlock, readpast) WHERE MSG_Q.status=0
SELECT TOP 1 * FROM MSG_Q WHERE MSG_Q.id=@queueid
UPDATE MSG_Q SET status=1 WHERE id=@queueid
COMMIT TRANSACTION TRAN1

Note the use of "WITH (updlock, readpast)" to make sure that I lock the target row and ignore rows that are similarly locked already.

Now, the procedure works as listed above, which is great. While I was putting this together, however, I found that if the second SELECT and the UPDATE are reversed in order (i.e. UPDATE first then SELECT), I got no data back at all. And no, it didn't matter whether the second SELECT was before or after the final COMMIT.

My question is thus why the order of the second SELECT and UPDATE makes a difference. I suspect that there is something subtle going on there that I don't understand, and I'm worried that it's going to bite me later on.

Any hints?

Answer 1

Although not directly answering your question here, rather than reinventing the wheel and making life difficult for yourself, unless you enjoy it of course ;-), may I suggest that you look at using SQL Server Service Broker.

It provides an existing framework for using queues etc.

To find out more visit.

Service Broker Link

Now back to the question, I am not able to replicate your problem, as you will see if you execute the code below, data is returned regardless of the order os the select/update statement.

So your example above then.

create table #MSG_Q
(id int identity(1,1) primary key,status int)
insert into #MSG_Q select 0

DECLARE @queueid INT
BEGIN TRANSACTION TRAN1
SELECT TOP 1 @queueid = id FROM #MSG_Q WITH (updlock, readpast) WHERE #MSG_Q.status=0
UPDATE #MSG_Q SET status=1 WHERE id=@queueid
SELECT TOP 1 * FROM #MSG_Q WHERE #MSG_Q.id=@queueid
COMMIT TRANSACTION TRAN1

select * from #MSG_Q
drop table #MSG_Q

Returns the Results (1,1) and (1,1)

Now swapping the statement order.

create table #MSG_Q
(id int identity(1,1) primary key,status int)    
insert into #MSG_Q select 0

DECLARE @queueid INT
BEGIN TRANSACTION TRAN1
SELECT TOP 1 @queueid = id FROM #MSG_Q WITH (updlock, readpast) WHERE #MSG_Q.status=0
SELECT TOP 1 * FROM #MSG_Q WHERE #MSG_Q.id=@queueid
UPDATE #MSG_Q SET status=1 WHERE id=@queueid
COMMIT TRANSACTION TRAN1

select * from #MSG_Q
drop table #MSG_Q

Results in: (1,0), (1,1) as expected.

Perhaps you could qualify your issue further?

Answer 2

by default transactions are READ COMMITTED :

"Specifies that shared locks are held while the data is being read to avoid dirty reads, but the data can be changed before the end of the transaction, resulting in nonrepeatable reads or phantom data. This option is the SQL Server default."

http://msdn.microsoft.com/en-us/library/aa259216.aspx

I think you are getting nothing in the select because the record is still marked as dirty. You'd have to change the transaction isolation level OR, what I do is do the update first and then read the record, but to do this you have to flag the record w/ a unique value (I use a getdate() for batchs but a GUID would be what you probably want to use).

Answer 3

More experimentation leads me to conclude that I was chasing a red herring, brought about by the tools I was using to exec my stored procedure. I was initially using DBVisualizer (free edition) and Netbeans, and they both appear to be confused by something about the format of the results. DBVisualizer suggests that I'm getting multiple result sets back, and that the free edition doesn't handle that.

Since then, I grabbed the free MS SQL Server Management Studio Express and things work perfectly. For those interested, the URL to SMSE is here:

MS SQL Server SMSE

Don't forget to install the MSXML6 service pack, too:

MSXML Service Pack 1

So, totally my bad in this case. :-(

Major thanks and kudos to you guys for your answers though. You helped me confirm that what I was doing should work, which lead me to the change I had to make to actually "solve" the issue. Thanks ever so much!

Answer 4

One more point-- including a "SET NOCOUNT ON" in the stored procedure fixed things for all ODBC clients. Apparently the rowcounts for the first select was confusing the ODBC clients, and telling SQL Server to not return that value makes things work perfectly...