Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #1590

Re: High concurrency SELECT / UPDATE procedure

Show all headers | View raw

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

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Find similar | Unroll thread

Thread

High concurrency SELECT / UPDATE procedure Mau C <nobody@hotmail.com> - 2011-02-10 11:15 +0100
  Re: High concurrency SELECT / UPDATE procedure Erland Sommarskog <esquel@sommarskog.se> - 2011-02-12 00:04 +0100

csiph-web