Geek Answers Handbook

Explain lock behavior in SQL Server

Why is this, with the default settings on Sql Server (so that transaction isolation level = read), this test:

CREATE TABLE test2 (
ID bigint,
name varchar(20)
)

then run this on one SSMS tab:

begin transaction SH
insert into test2(ID,name) values(1,'11') 
waitfor delay '00:00:30'
commit transaction SH

and this one is simultaneously on another tab:

select * from test2

the second choice is required to wait until the first is completed before return

We also tried this for the second request:

select * from test2 NOLOCK WHERE ID = 1

and tried to insert one ID in the first request and select a different identifier in the second.

Is this the result of a page lock? When running 2 queries, I also ran this:

select object_name(P.object_id) as TableName, resource_type, resource_description
from
sys.dm_tran_locks L join sys.partitions P on L.resource_associated_entity_id = p.hobt_id

and got this result set:

test2 RID 1: 12186: 5
test2 RID 1: 12186: 5
test2 PAGE 1: 12186
test2 PAGE 1: 12186

+2
2

, ??

read commited , , , , sql- ( tempdb )

BTW,

select * from test2


select * from test2 where id <> 1

, 1 , ,

+4

node 'crabbing':

  • node
  • node
  • node
  • node ( )

: "" . , .

SELECT ... FROM table; . , , , , "" , , , . , , , node , . , , , , 100%. , ( , ..), - , .

- , . , , , , : . , .

+3

More articles:


All Articles