title: Sybase: trying to lock a record on select so another caller does not get the same one
body: <p>I have a simple table in Sybase, let's say it looks as follows:</p>
  
  <pre><code>CREATE TABLE T
  (
     name VARCHAR(10),
     entry_date datetime,
     in_use CHAR(1)
  )
  </code></pre>
  
  <p>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.</p>
  
  <p>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'.</p>
  
  <p>Is there a better way to get the next record, lock it, and update it all in one atomic step?</p>
  
  <hr>
  
  <pre><code>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
  </code></pre>