Geek Answers Handbook

Block a row from reading while sp is running

I have many .NET processes that scan messages from a SQL Server 2008 DB table and process them one at a time. I implement a simple SP to “lock” a line that is read by any one process, to avoid any two processes that process the same line.

BEGIN TRAN

SELECT @status = status FROM t WHERE t.id = @id
IF @status = 'L'
BEGIN
    -- Already locked by another process. Skip this
    RETURN 0
END
ELSE
    UPDATE t SET status = 'L' WHERE id = @id
    RETURN 1
END

COMMIT

However, this is not true: sometimes the string is "blocked" and processed twice. I suspect there is a concurrency problem: two processes reading the status before it updates it.

I think this can be resolved by implementing the read block in some way (i.e. make a READ transaction block), but I'm not sure how to do this. Can anyone help?

Thank you very much in advance

Ryan

+5
2

:

SELECT @status = status FROM t (UPDLOCK) WHERE t.id = @id

. .

, , , SELECT , , SELECT, UPDATE, SELECT, , .

UPDLOCK, SELECT , , , .

, , , , .

+2

UPDATE t SET status = 'L' WHERE id = @id and status <> 'L'
RETURN @@ROWCOUNT

, .

+3

More articles:


All Articles