Sybase:尝试锁定select上的记录，以便另一个调用者无法获得相同的记录 [英] Sybase: trying to lock a record on select so another caller does not get the same one

问题描述

我在Sybase中有一个简单的表，假设它看起来如下:

I have a simple table in Sybase, let's say it looks as follows:

CREATE TABLE T
(
   name VARCHAR(10),
   entry_date datetime,
   in_use CHAR(1)
)

我想根据"entry_date"的顺序获取下一个条目，并立即将"in_use"更新为"Y"，以指示该记录不可用于接下来的查询.路径尝试在我希望第二个查询被阻止的同时运行查询，这样它就不会获取相同的记录.

I want to get the next entry based on order of "entry_date" and immediately update "in_use" to "Y" to indicate that the record is not available to the next query that comes in. The kicker is that if two execution paths try to run the query at the same time I want the second one to block so it does not grab the same record.

问题是我发现，如果您具有ORDER BY子句，则无法在Sybase中执行"SELECT FOR UPDATE"，因此由于以下错误而导致无法创建以下存储的proc，这是由于以下原因导致的:选择-使用只读游标时错误地指定了'FOR UPDATE'.

The problem is I've found that you cannot do "SELECT FOR UPDATE" in Sybase if you have an ORDER BY clause, so the following stored proc cannot be created because of the following error due to the ORDER BY clause in the select - "'FOR UPDATE' incorrectly specified when using a READ ONLY cursor'.

是否有更好的方法来获取下一条记录，将其锁定并一步一步更新所有记录?

Is there a better way to get the next record, lock it, and update it all in one atomic step?

CREATE PROCEDURE dbo.sp_getnextrecord
@out1 varchar(10) out,
@out2 datetime out
AS
DECLARE @outOne varchar(10), @outTwo datetime

BEGIN TRANSACTION 

-- Here is the problem area Sybase does not like the
-- combination of 'ORDER BY' and 'FOR UPDATE' 
DECLARE myCursor CURSOR FOR
SELECT TOP 1 name, entry_date FROM T
WHERE in_use = 'N'
ORDER BY entry_Date asc FOR UPDATE OF in_use

OPEN myCursor

FETCH NEXT FROM myCursor
INTO @outOne, @outOne

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN

   UPDATE t SET IN_USE = 'Y' WHERE 
     name = @outOne AND entry_date = @outTwo

   SELECT @out1 = @outOne
   SELECT @out2 = @outTwo

   -- This is executed as long as the previous fetch succeeds.
   FETCH NEXT FROM myCursor
        INTO @outOne, @outTwo
END

CLOSE myCursor
DEALLOCATE myCursor

COMMIT TRANSACTION

推荐答案

由于您只选择了一行(TOP 1)，所以为什么不使用

since you are selecting just one row (TOP 1) why not just use a standard locking hint and forget the cursor:

BEGIN TRANSACTION

SELECT @PK=ID FROM YourTable WITH (UPDLOCK, HOLDLOCK, READPAST) WHERE ...

UPDATE ....
WHERE pk=@PK

COMMIT

如果您确实需要循环播放，请点击Google"CURSOR FREE LOOP"

if you really do need to loop, google "CURSOR FREE LOOP"

替换游标有哪些不同的方法?

您可以在使用SELECT上的锁定提示的同时，选择下一个MIN(PK)> @ CurrentPk进行循环.

you can loop by SELECTing the next MIN(PK)>@CurrentPk while using the locking hints on the SELECT.

这篇关于Sybase:尝试锁定select上的记录，以便另一个调用者无法获得相同的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！