SQL Server SELECT / UPDATE Stored Weirdness Procedure

Question

I have a table that I use as a work queue. Essentially, it consists of a primary key, a piece of data, and a status flag (processed / not processed). I have several processes trying to capture the next raw string, so I have to make sure that they follow the correct semantics of locking and updating to avoid nasty things. To this end, I have defined a stored procedure that 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 line and ignore lines that are already locked.

, , . , , , SELECT UPDATE (.. UPDATE, SELECT), . , , SELECT COMMIT.

, SELECT UPDATE . , - , , , .

?

Answer 1

READ COMMITTED:

", , , , phantom. SQL Server ".

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

, , - . . , , , , ( getdate() GUID , , , ).

Answer 2

, , , ;-), SQL Server Service Broker.

..

, .

, , , , os select/update.

, .

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

(1,1) (1,1)

.

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

: (1,0), (1,1), .

, ?

Answer 3

, , , . DBVisualizer ( ) Netbeans, , , - . DBVisualizer , , .

MS SQL Server Management Studio Express, . URL SMSE :

MS SQL Server SMSE

MSXML6:

MSXML Service Pack 1

, .: - (

, , . , , , , , , "" . !

Answer 4

- "SET NOCOUNT ON" ODBC. -, rowcounts ODBC , SQL Server , ...