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

Groups > comp.databases.ms-sqlserver > #1584 > unrolled thread

High concurrency SELECT / UPDATE procedure

Back to article view | Back to comp.databases.ms-sqlserver

Contents

  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

#1584 — High concurrency SELECT / UPDATE procedure

Hi,
I'm messing with this scenario:
- SQL Server 2005 (it should work on 2008 too)
- high concurrency jdbc client connections.

The code below is a "simplified" exctract of the real application to
make easier the example which is like a job scheduler or, if you prefer,
a "process starter" :

Requirements (steps)
1) it takes the first 10 available jobs (row=job)
2) it marks "taken" those rows and pass them to the external jdbc client
(as resultset)
3) the external client processes those jobs and, when each job is
completed, deletes the corresponding row.

The SQL code below refers to the 1) and 2) step only:

CREATE TABLE  [tmp_table] (
  id int IDENTITY(1,1) NOT NULL,
  field1 varchar(45) NOT NULL DEFAULT '',
  field2 varchar(45) NOT NULL DEFAULT '',
  status smallint NOT NULL DEFAULT '0', -- 0 free, 1 busy
  PRIMARY KEY  (id)
)


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 ?

Thanks in advance
Best regards,
M.

[toc] | [next] | [standalone]

#1590

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

[toc] | [prev] | [standalone]

Back to top | Article view | comp.databases.ms-sqlserver

csiph-web