March 23, 2009 at 9:37 pm
Can someone explain why the following two code samples run in separate windows produces blocking? The insert transaction is inserting a different record than the update is looking for, yet they second Window is attempting to get a RID lock on the same resource and is blocked?
Window 1
--------------------------------------------
drop table dave
create table dave
(d1 varchar(50),
d2 varchar(50))
insert dave
values ('Sno','Snoopy')
insert dave
values ('Luc','Lucy')
BEGIN TRAN
insert dave WITH (ROWLOCK)
values ('CBro','Charlie Brown')
--ROLLBACK TRAN
Window 2
---------------------------------
begin tran
update dave
set d2 = 'Snoopy Jr.'
where d1 = 'Sno'
--ROLLBACK TRAN
March 23, 2009 at 11:19 pm
The update statement has to do a table scan in order to find which record met the specified criteria in the where clause. Since the first process is still in the middle of the transaction, it still locks the record that it inserted to the table. This causes the blocking that you see. If you’ll create an index on column d1, it won’t be blocked.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
March 24, 2009 at 6:05 am
Try looking at sys.dm_tran_locks to see what locks are being held. When I run it, I see an exclusive intent lock on the page from the first statement. That's causing the second statement to wait even though it only wants a single row. Because it needs to do a lock escalation due to the scan to find the right value, it's blocked until the other transaction clears. The index, as noted above, will change the behavior.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 24, 2009 at 11:16 am
Thanks, guys.
I should have realized about the scan for the update, but what threw me was that both processes were trying to get exclusive RID locks on the same row.
I'm not sure why the update table scan would need to do this, unless the conflict row was some heap header block or something. I would have thought a share or intent lock would be issued until the row to be updated was identified.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply