通过dbcc page解析哪一行数据被锁住了(二)
c_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)，到底是哪一行数据
[sql]
--查询(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锁住了。