Table Lock in sql server - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Table Lock in sql server

Author

Topic

laptop545
Starting Member

11 Posts

Posted - 2011-11-28 : 12:38:39

I have an issue regarding the table locks in sql server. Here is what i want to achieve :

I have a table on a central database. 5 process from 5 different servers access this table through a stored procedure. Here is what the stored procedure do :


SELECT TOP 1 * FROM tbl_name WHERE Status = 0 ORDER BY id

Do some checkings..(very small though)

IF condition met:
      UPDATE tbl_name  SET Status = 1 Where id = (picked above)
Else
      UPDATE tbl_name  SET Status = 2 Where id = (picked above)

Now my concern is as all the 5 process from different servers access the same table, there might be a chance that when one process has taken a row and before it updates the Status to 1 or 2 another process might take the same row. So, i want to acquire a clock or something else on that table, so that everything is serial. Thanks.

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2011-11-28 : 23:05:28

Try something like this


BEGIN TRAN

SELECT TOP 1 *
  FROM tbl_name WITH(TABLOCKX)
 WHERE Status = 0
 ORDER BY id

-- Do some checkings..(very small though)

IF condition met:
      UPDATE tbl_name  SET Status = 1 Where id = (picked above)
Else
      UPDATE tbl_name  SET Status = 2 Where id = (picked above)

COMMIT

For us, there is only the trying. The rest is not our business. ~T.S. Eliot

Muhammad Al Pasha

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources