准备数据

创建表并插入数据

 CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerName NVARCHAR(50),
    OrderAmount DECIMAL(10, 2)
);

INSERT INTO Orders (OrderID, CustomerName, OrderAmount)
VALUES
    (1, 'Alice', 100.00),
    (2, 'Bob', 200.00),
    (3, 'Charlie', 150.00);

场景模拟

  1. 开始一个新的事务并更新一行数据，但不提交事务。
BEGIN TRAN
UPDATE Orders SET OrderAmount = 120.00 WHERE OrderID = 1;
  1. 在另一个查询窗口中，尝试更新同一行数据
-- 这将被阻塞，因为第一个事务还没有提交
BEGIN TRANSACTION;
UPDATE Orders SET OrderAmount = 122.00 WHERE OrderID = 1;
  1. 在另一个查询窗口中，尝试更新
BEGIN TRANSACTION;
SELECT * FROM Orders WITH (UPDLOCK) WHERE OrderID = 1;

查看并分析

SELECT 
    request_session_id AS SessionID,
    resource_type AS ResourceType,
    resource_description AS ResourceDescription,
    request_mode AS RequestMode,
    request_status AS RequestStatus
FROM sys.dm_tran_locks
WHERE resource_database_id = DB_ID()

image-1714401100636

sp_lock

image-1714401263496

使用 dbcc page 定位数据，需要先打开 DBCC TRACEON(3604) 。

dbcc page 四个参数：dbid,fileid,pageid,1|2|3(输出详细级别)

image-1714401384792

 在生产环境下，拿到数据后，记得及时关闭 DBCC TRACEOFF(3604)

如果形成blocked 链，可以通过sp_who2 ,sysprocesses表来快速定位会话信息。
再通过dbcc inputbuffer(spid)查看会话缓存中的语句。
也可以通过DM表关联定位。

select t1.resource_type as [资源锁定类型]
	,db_name(resource_database_id) as [数据库名]
	,t1.resource_associated_entity_id as [锁定的对象]
	,t1.request_mode as [等待者需求的锁定类型]
	,t1.request_session_id as [等待者sid]  
	,t2.wait_duration_ms as [等待时间]	
	,(select text from sys.dm_exec_requests as r  
		cross apply sys.dm_exec_sql_text(r.sql_handle) 
		where r.session_id = t1.request_session_id) as [等待者要执行的批次]
	,(select substring(qt.text,r.statement_start_offset/2+1, 
			(case when r.statement_end_offset = -1 
			then datalength(qt.text) 
			else r.statement_end_offset end - r.statement_start_offset)/2+1) 
		from sys.dm_exec_requests as r
		cross apply sys.dm_exec_sql_text(r.sql_handle) as qt
		where r.session_id = t1.request_session_id) as [等待者正要执行的语法]
	 ,t2.blocking_session_id as [锁定者sid] 
     ,(select text from sys.sysprocesses as p		
		cross apply sys.dm_exec_sql_text(p.sql_handle) 
		where p.spid = t2.blocking_session_id) as [锁定者的语法]
	from 
	sys.dm_tran_locks as t1, 
	sys.dm_os_waiting_tasks as t2
where 
	t1.lock_owner_address = t2.resource_address

image-1714401803675

写在后面

会话阻塞是比较常的数据库问题，它与数据库死锁Deadlock不同，阻塞blocked 如果阻塞源会话不能执行完成的话，后面的等待会话链不会自动消失。通过跟踪分析会话，定位阻塞源才能及时修复问题。解决办法与解决死锁问题一样。