SQL Server的锁相关内容

--连接持有的锁
sp_lock

----连接持有的锁（动态管理视图）
select * from sys.dm_tran_locks

--锁在哪个表，哪个索引上面
select
request_session_id, resource_type, resource_associated_entity_id, 
request_status, request_mode, resource_description, p.object_id, OBJECT_NAME(p.object_id) as object_name,
 p.* from sys.dm_tran_locks 
left join sys.partitions p on sys.dm_tran_locks.resource_associated_entity_id = p.hobt_id
where resource_database_id = DB_ID('AdventureWorks')
ORDER BY request_session_id, resource_type, resource_associated_entity_id


--设置后 将会返回执行计划表
set statistics profile on
select * from Books

 

select * from Student with (holdlock) --表锁：其他事务可读取，不可更新和删除
select * from Student with (tablockx) --表锁：其他事务不可 读取、更新和删除

select * from Student with (rowlock) where id = 2 --行锁