我有一个存储过程,它选择1条记录.可以从不同PC上的几个不同应用程序调用存储过程.这个想法是存储过程带回了需要处理的下一条记录,如果两个应用程序同时调用存储过程,则不应该返回相同的记录.我的查询如下,我正在尝试尽可能高效地编写查询(sql 2008).它可以比这更有效地完成吗？
CREATE PROCEDURE GetNextUnprocessedRecord
AS
BEGIN
SET NOCOUNT ON;
--ID of record we want to select back
DECLARE @iID BIGINT
-- Find the next processable record,and mark it as dispatched
-- Must be done in a transaction to ensure no other query can get
-- this record between the read and update
BEGIN TRAN
SELECT TOP 1
@iID = [ID]
FROM
--Don't read locked records,only lock the specific record
[MyRecords] WITH (READPAST,ROWLOCK)
WHERE
[Dispatched] is null
ORDER BY
[Received]
--Mark record as picked up for processing
UPDATE
[MyRecords]
SET
[Dispatched] = GETDATE()
WHERE
[ID] = @iID
COMMIT TRAN
--Select back the specific record
SELECT
[ID],[Data]
FROM
[MyRecords] WITH (NOLOCK,READPAST)
WHERE
[ID] = @iID
END
解决方法
使用READPAST锁定提示是正确的,您的sql看起来没问题.
我添加使用XLOCK虽然也是HOLDLOCK / SERIALIZABLE
... [MyRecords] WITH (READPAST,ROWLOCK,XLOCK) ...
这意味着您获得了ID,并在您继续并更新它时专门锁定该行.
编辑：在Dispatched和Received列上添加索引以使其更快.如果[ID](我认为它是PK)没有聚集,则INCLUDE [ID].并过滤索引,因为它是sql 2008
你也可以使用这个结构,它可以在没有XLOCK或HOLDLOCK的情况下一次完成
UPDATE
MyRecords
SET
--record the row ID
@id = [ID],--flag doing stuff
[Dispatched] = GETDATE()
WHERE
[ID] = (SELECT TOP 1 [ID] FROM MyRecords WITH (ROWLOCK,READPAST) WHERE Dispatched IS NULL ORDER BY Received)
