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 :
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.Code:
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)
