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