From: esquel@sommarskog.se   
      
   Mau C (nobody@hotmail.com) writes:   
   >   
   > PROCEDURE [pSelectTmpTable]   
   > AS   
   > BEGIN   
   >      SET NOCOUNT ON;   
   >      BEGIN TRANSACTION;   
   >      SELECT TOP 10 * FROM [tmp_table] WITH (XLOCK,ROWLOCK) WHERE status=0;   
   >      UPDATE [tmp_table] SET status=1 WHERE id IN (SELECT TOP 10 id FROM   
   > [tmp_table] WHERE status=0);   
   >      COMMIT TRANSACTION;   
   >      COMMIT;   
   > END   
   >   
   > Does it the right way to manipulate records in high concurrency context ?   
   > Do particularly the clauses (XLOCK,ROWLOCK) properly work under those   
   > requirements ?   
      
   There is a potential flaw here. Since there is no ORDER BY here, there is   
   no guarantee that the two SELECT TOP will produce the same rows. So that's   
   the first thing to fix, and an ORDER BY clause which sorts the rows on   
   something which is unique.   
      
   Then again you could use the TOP and OUTPUT clauses to your advantage:   
      
   UPDATE TOP (10) tmp_table   
   SET    status = 1   
   OUTPUT inserted.col1, inserted.col2, ...   
   WHERE  status = 0   
      
      
   --   
   Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se   
      
   Links for SQL Server Books Online:   
   SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx   
   SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)