Strange Locking Behaviour in SQL Server 2005

Posted by SQL Learner on Stack Overflow See other posts from Stack Overflow or by SQL Learner
Published on 2011-06-20T09:11:21Z Indexed on 2011/06/20 16:22 UTC
Read the original article Hit count: 147

Filed under:
|

Can anyone please tell me why does the following statement inside a given stored procedure returns repeated results even with locks on the rows used by the first SELECT statement?

BEGIN TRANSACTION

DECLARE @Temp TABLE ( ID INT )

INSERT INTO @Temp SELECT ID FROM SomeTable WITH (ROWLOCK, UPDLOCK, READPAST) WHERE SomeValue <= 10
INSERT INTO @Temp SELECT ID FROM SomeTable WITH (ROWLOCK, UPDLOCK, READPAST) WHERE SomeValue >= 5

SELECT * FROM @Temp

COMMIT TRANSACTION

Any values in SomeTable for which SomeValue is between 5 and 10 will be returned twice, even though they were locked in the first SELECT. I thought that locks were in place for the whole transaction, and so I wasn't expecting the query to return repeated results. Why is this happening?

© Stack Overflow or respective owner

Related posts about sql-server-2005

Related posts about locking