如何查看被锁的是哪行数据?通过dbcc page可以。
要想明白这个问题:
首先,需要模拟阻塞问题,这里直接模拟了阻塞问题的一个比较极端的情况,就是死锁。
然后,需要知道如何监控死锁,否则,就算产生了死锁,你也不一定知道。这里在模拟死锁之前,通过SQL Profiler先来监控死锁问题。
接下来,我们可以通过sys.dm_tran_locks来获取更详细的阻塞信息。
最后,通过dbcc page来解析哪一行数据被锁住了。
下面就按照上面的步骤,一步一步来实现:
1、先监控死锁。
先选择SQL Profiler:
然后,新建跟踪,单击连接:
接下来,选择“事件选择”选项卡,点击“显示所有事件”复选框,在其中点击“Locks”事件,在“Deadlock graph”复选框,这样在发生死锁的时候,就会被监控到,而且以图像的方式显示死锁的信息,易于理解:
2、构造死锁。
先创建一个表
--建表
if OBJECT_ID('t1') is not null
drop table t1
go
create table t1
(
id int primary key,
v varchar(20)
)
insert into t1
select 1 ,'aa' union all
select 2 ,'bb' union all
select 3 ,'cc' union all
select 4 ,'dd' union all
select 5 ,'ee' union all
select 6 ,'ff'
开启会话X,在里面输入下面的代码:
--执行顺序.1
begin tran
update t1
set v = 'xx'
where id = 3
--执行顺序.1
--执行顺序.3
update t1
set v = 'yy'
where id = 6
--执行顺序.3
再次开启会话Y,在里面输入代码:
--执行顺序.2
begin tran
update t1
set v = 'mm'
where id = 6
--执行顺序.2
--执行顺序.4
update t1
set v = 'nn'
where id = 3
--执行顺序.4
然后,按照先执行会话X中的执行顺序1,然后执行会话Y中的执行顺序2,执行会话X中的执行顺序3,执行会话Y中的执行顺序4,一步一步执行。
当执行完这4个步骤后,在会话X中,显示了死锁,且会话X的事务被回滚:
同时,能在SQL Profiler中看到监控到的死锁:
从这个图中,我们可以看到详细的死锁信息,打叉的表示被回滚的会话,把鼠标放到椭圆上,会显示导致死锁的,这个会话正在运行的sql语句。
在长方形的框中,可以看到两个会话要获取X锁,左边的会话拥有下面方框中的键锁,右边的会话拥有上面的键锁,而当左边的会话想要获取上面的键锁是,被阻塞住了,而当右边的会话想要获取下面的键锁时,也被阻塞了,于是整个图像中形成了一个循环,也就导致了死锁。
3、获取更详细的阻塞信息。
注意,上面提到的会话X,这里是53,而会话Y是55,这个可以从上面图中,椭圆形中的“服务器进程ID”获得。
通过通过sys.dm_tran_locks,可以获取到更为详细的阻塞信息。
select resource_type,
resource_database_id, --数据库id
resource_description, --资源描述
resource_associated_entity_id, --资源关联实体id
request_mode, --请求模式
request_type, --请求类型
request_status,
request_session_id, --请求会话id
request_owner_type
from sys.dm_tran_locks
where request_session_id = 55
解析resource_associated_entity_id的值:
--1.查询resource_associated_entity_id的意义
select *
from sys.tables
where object_id = 837578022
/*
这个id是t1表
name object_id
t1 837578022
*/
--2.查询resource_associated_entity_id的意义
select *
from sys.partitions p
where p.hobt_id = 72057594041466880
/*
这个是表t1的一个b树
partition_id object_id index_id partition_number hobt_id
72057594041466880 837578022 1 1 72057594041466880
*/
4、通过dbcc page来解析sys.dm_tran_locks中resource_description列的值:
resource_type resource_database_id resource_description
PAGE 10 1:188
KEY 10 (b9b173bbe8d5)
KEY 10 (98ec012aa510)
为了能解析 resource_description的值,需要建立一个表和一个存储过程,注意在存储过程中引用的1:188,就是上面sys.dm_tran_locks中的结果:
--3.这里创建一个表,用来存放dbcc page的结果
if exists(select * from sys.tables where name = 'dbcc_page')
drop table dbcc_page
go
create table dbcc_page
(
ParentObject varchar(500),
Object varchar(2000),
Field varchar(1000),
Value nvarchar(max)
)
go
--创建一个存储过程
if exists(select * from sys.procedures where name = 'proc_dbcc_page')
drop procedure proc_dbcc_page
go
create procedure proc_dbcc_page
as
DBCC page(10, --数据库id : 10
1, --文件id: 1
188, --页id: 188
3) with tableresults
go
insert into dbcc_page
exec proc_dbcc_page
go
最后,我们查询一下,(b9b173bbe8d5)和(98ec012aa510),到底是哪一行数据
--查询(b9b173bbe8d5)和(98ec012aa510),到底是哪一行数据
;with t
as
(
select OBJECT,
Field,
value,
case when charindex('Column',object) > 0
then charindex('Column',object)
else charindex('Offset',object)
end as substring_len
from dbcc_page dp
where Object like 'Slot%Column%'
or
Field = 'KeyHashValue'
),
tt
as
(
select object,
field,
value,
cast(substring(object,len('Slot')+1,substring_len-len('Slot')-1) as int) as row
from t
),
ttt
as
(
select object,
field,
value,
row, --第几行
max(case when field = 'KeyHashValue'
then value
else ''
end) over(partition by row) as KeyHashValue
from tt
)
select *
from ttt
where KeyHashValue in ('(b9b173bbe8d5)', '(98ec012aa510)')
下面的是查询结果:
从上图中,我们能很清楚的看到(b9b173bbe8d5)和(98ec012aa510),就是id为3、6的两行数据,这两行数据最后被会话55锁住了。