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
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
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
![](https://i-blog.csdnimg.cn/blog_migrate/468f4929b17a13d4f8fbb660a0bb32d3.png)
--SIU
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
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
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 |