SQL Server SELECT/UPDATE 存储过程怪异 [英] SQL Server SELECT/UPDATE Stored Procedure Weirdness

问题描述

我有一张表用作工作队列.本质上，它由一个主键、一段数据和一个状态标志(已处理/未处理)组成.我有多个进程试图获取下一个未处理的行，所以我需要确保它们观察到正确的锁定和更新语义，以避免竞争条件下的严重性.为此，我定义了一个他们可以调用的存储过程:

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;

注意使用WITH (updlock, readpast)"来确保我锁定目标行并忽略已经类似锁定的行.

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

现在，程序按照上面列出的方式工作，这很棒.然而，当我把它放在一起时，我发现如果第二个 SELECT 和 UPDATE 按顺序颠倒(即先更新然后选择)，我根本没有得到任何数据.不，第二个 SELECT 是在最后一次 COMMIT 之前还是之后并不重要.

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.

因此，我的问题是为什么第二个 SELECT 和 UPDATE 的顺序会有所不同.我怀疑那里有一些我不明白的微妙事情，我担心以后会咬我.

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.

有什么提示吗?

推荐答案

默认事务是 READ COMMITTED :

by default transactions are READ COMMITTED :

"指定在读取数据时持有共享锁，避免脏读，但可以在事务结束前更改数据，导致不可重复读或幻象数据.此选项是SQL Server默认的."

"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

我认为您在选择中没有得到任何东西，因为该记录仍被标记为脏.您必须更改事务隔离级别或，我所做的是先进行更新然后读取记录，但要做到这一点，您必须使用唯一值标记记录(我使用 getdate() 进行批处理但 GUID 可能是您想要使用的).

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

这篇关于SQL Server SELECT/UPDATE 存储过程怪异的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！