Tuesday, October 1, 2013

UPDATE lock on table

I have query like this:



BEGIN TRAN
;WITH CTE AS(SELECT col1, col2,.... FROM dbo.table t1 INNER JOIN dbo.table t2...INNER JOIN table t3 WITH (UPDLOCK)...)
SELECT col1 from CTE c INNER JOIN CTE c1 ON ....




I have put UPDLOCK on one table, which I can see in other window. There is U key lock and IU PAGE lock on that table. Everything as expected.


But if I update the row which is selected in some other window, the update will succeed.


If i write just the select query which is inside CTE:



BEGIN TRAN
SELECT col1, col2,.... FROM dbo.table t1 INNER JOIN dbo.table t2...INNER JOIN table t3 WITH (UPDLOCK)...)



the similar U key lock and IU page lock is putted on the same table, but this time I can't update the same row.


How is that possible? Does it put update lock on some different row in first case, because the query is inside CTE?


How can I see, which row (id) is locked if I have resource description :(bffced99e570) beside key lock? Does anybody has the query?


Thanks, Simon


No comments:

Post a Comment