一些锁的示例

 

use userDatabase
go
if exists(select 1 from sys.tables where name='t')
    drop table t
create table t(c1 int,c2 int)
create index index1 on t(c1)
insert into t values(1,1)

 

--UPDATE lock

set transaction isolation level repeatable read
begin tran
update t set c2=6 where c1=1
select resource_type,resource_description,resource_associated_entity_id,request_mode,request_type,request_status  
from sys.dm_tran_locks where request_session_id=@@spid   order by resource_type
rollback
go

 

--SIX

set transaction isolation level repeatable read 
begin tran 
select *from t with (paglock) 
update t set c2=6 where c1=1 
select resource_type,resource_description,resource_associated_entity_id,request_mode,request_type,request_status 
from sys.dm_tran_locks where request_session_id=@@spid order by resource_type 
rollback 
go 

 

 

 

--SIU

 

set transaction isolation level repeatable read 
begin tran 
select *from t with (paglock,index=index1) 
update t set c2=6 where c1=1 
select resource_type,resource_description,resource_associated_entity_id,request_mode,request_type,request_status 
from sys.dm_tran_locks where request_session_id=@@spid order by resource_type 
rollback 
go

 

--1:167为索引页,1:165为数据页

 

 

--UIX

set transaction isolation level repeatable read 
begin tran 
update t with(paglock) set c2=6 where c1=1 
update t set c1=6 where c1=1 
select resource_type,resource_description,resource_associated_entity_id,request_mode,request_type,request_status 
from sys.dm_tran_locks where request_session_id=@@spid order by resource_type 
rollback 
go 

 

 

 

 

 

 

 

Request_status

create table ##session(sessionID int) 

 

WAIT

Time	Transaction 1		Transaction 2
1	set transaction isolation level  read committed		set transaction isolation level  read committed
2			Begin tran
3	Delete from ##session go insert into ##session values(@@SPID) go		update t set c2=6 where c1=1
4	select *from t
5			select resource_type,resource_description,resource_associated_entity_id,request_mode,request_type,request_status from sys.dm_tran_locks where request_session_id in (select sessionID from ##session)
6			rollback

   

 

 

CONVERT

Time	Transaction 1		Transaction 2
1	set transaction isolation level  read committed		set transaction isolation level repeatable read
2			Begin tran
3	Delete from ##session go insert into ##session values(@@SPID)		select *from t
4	update t set c2=6 where c1=1
5			select resource_type,resource_description,resource_associated_entity_id,request_mode,request_type,request_status from sys.dm_tran_locks where request_session_id in (select sessionID from ##session)
6			rollback