December 26, 2008 at 2:01 am
first, create a temporary table, and put 10000 records into it
-- code start--
create table test (id int primary key,col int)
set @i=1
while @i<=10000
begin
insert into test values(@i,@i)
set @i=@i+1
end
-- code end--
and then, exec a sql
-- code start--
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
update test set col=368 where id=368
checkpoint
begin tran
update test with(holdlock) set col=368 where id=368
waitfor delay '0:00:10'
rollback tran
-- code end--
create a new session and run sql within 10 sec.
-- code start--
select * from test where id=368
-- code end--
Do you find the problom ? why the second request was not suppended by the first request?
I use profile to monitor all locks of them, and find the second request not put S-Lock on the key( id=368), it just take a IS-Lock on page!
December 26, 2008 at 4:22 am
Do you find the problom ? why the second request was not suppended by the first request?
I don't find any problem. The seconds request is blocked by the first request for (< ) 10 seconds. After that it runs without any problem.
What problem did you expect? Deadlock?
December 26, 2008 at 5:28 am
I also ran it and was surprised to see that that the select query in the second window did not wait until the transaction in the first window will rollback. By the way, when I modified the script so the update statement will change the data in column col (in the original script it updated the value to the same value that was already in the column), the select statement in the second window was blocked for 10 seconds. I admit that I expected to see the same behavior regardless the value of the updated column. Maybe some can explain this.
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/
December 28, 2008 at 9:05 pm
yes, it's so surprising. How come the select statement in the second window does not put the S-Lock on the key? Does it just check the dirt pages on the key, instead of check whether there is Locks on the key?
January 20, 2009 at 7:46 pm
Anybody still on this question?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply