一文分析SQL Server中事务使用的锁

USE [Test]
Go

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[UserTable](
	[id] [varchar](36) NOT NULL,
	[name] [varchar](256) NULL,
	[code] [varchar](256) NULL,
	[createtime] [datetime] NULL,
	[lastmodifytime] [datetime] NULL,
PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
USE [Test]
GO
INSERT [dbo].[UserTable] ([id], [name], [code], [createtime], [lastmodifytime]) VALUES (N'5E4B68B0-71B8-43FB-B6B4-8E9D43A30589', N'test1', N'123456', CAST(N'2022-06-29T18:02:21.517' AS DateTime), CAST(N'2022-06-29T18:02:21.517' AS DateTime))
GO

set transaction isolation level repeatable read
set statistics profile on 
begin tran
select * from usertable where  id='5E4B68B0-71B8-43FB-B6B4-8E9D43A30589'

select request_session_id,resource_type,request_status,request_mode,resource_description,
case resource_type
when 'Page' then OBJECT_NAME(p.object_id)
when 'object' then OBJECT_NAME(lock.resource_associated_entity_id)
when 'database' then (select name from master..SysDatabases where dbid=resource_database_id)
when 'key' then object_name(p.object_id)
end 
as objectName from sys.dm_tran_locks lock
left join sys.partitions p 
on p.hobt_id=lock.resource_associated_entity_id
order by lock.request_session_id

begin tran

insert into UserTable (id,code,name,createtime,lastmodifytime)
values(newid(),'test2','测试用户2',getdate(),getdate())

begin tran 
update UserTable set lastmodifytime=GETDATE()  where id ='06757850-68D6-416C-B3D1-FD3B29BAD4BB'

create nonclustered index idx_UserTable_Name on UserTable(name)
create nonclustered index idx_UserTable_LastModifyTime on UserTable(lastmodifytime)

begin tran 
set statistics profile on 
update UserTable set lastmodifytime=GETDATE()  where name like '%test%'

with indexs 
as (
SELECT  索引名称 = a.name ,
        表名 = c.name ,
        索引字段名 = d.name ,
        a.indid
FROM    sysindexes a
        JOIN sysindexkeys b ON a.id = b.id
                               AND a.indid = b.indid
        JOIN sysobjects c ON b.id = c.id
        JOIN syscolumns d ON b.id = d.id
                             AND b.colid = d.colid
WHERE   a.indid NOT IN ( 0, 255 )  
AND   c.name='UserTable' --查指定表 
)
select request_session_id,resource_type,request_status,request_mode,resource_description,
case resource_type
when 'Page' then OBJECT_NAME(p.object_id)
when 'object' then OBJECT_NAME(lock.resource_associated_entity_id)
when 'database' then (select name from master..SysDatabases where dbid=resource_database_id)
when 'key' then object_name(p.object_id)
end 
as objectName,index_id,i.索引名称 from sys.dm_tran_locks lock
left join sys.partitions p on p.hobt_id=lock.resource_associated_entity_id
left join indexs  i on i.indid=index_id
order by lock.request_session_id