开发者社区> 潇湘隐者> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

SQL Server中如何定位Row Lock锁定哪一行数据

简介: 在SQL Server中有时候会使用提示(Hint)强制SQL使用行锁(Row Lock),前两天有个同事咨询了一个问题,如何定位Row Lock具体锁定了哪一行。其实这个问题只适合研究一下,实际意义并不大,因为找到、定位被锁定的行的代价开销较大,而意义却不怎么大,而且使用场景也很少。
+关注继续查看

在SQL Server中有时候会使用提示(Hint)强制SQL使用行锁(Row Lock),前两天有个同事咨询了一个问题,如何定位Row Lock具体锁定了哪一行。其实这个问题只适合研究一下,实际意义并不大,因为找到、定位被锁定的行的代价开销较大,而意义却不怎么大,而且使用场景也很少。那么下面我们来探讨、研究一下这个问题吧:

 

 

在会话窗口(会话ID=65)下执行下面SQL语句,模拟SQL Server使用行锁锁定某一行记录: 

 

USE AdventureWorks2012;
GO
 
SELECT  @@SPID;
 
BEGIN TRAN;
UPDATE  [dbo].[DatabaseLog] WITH ( ROWLOCK )
SET     TSQL = N'dddd'
WHERE   DatabaseLogID = 1;
--ROLLBACK;

 

 

 

在另外一个会话窗口使用下面SQL查询,我们能看到相关锁的一些信息,如下所示,但是这些信息还不够详细,我们还需要更详细的信息:

 

SELECT Db_name(RSC_DBID)                AS 'DATABASE_NAME', 
       CASE RSC_TYPE 
         WHEN 1 THEN 'null' 
         WHEN 2 THEN 'DATABASE' 
         WHEN 3 THEN 'FILE' 
         WHEN 4 THEN 'INDEX' 
         WHEN 5 THEN 'TABLE' 
         WHEN 6 THEN 'PAGE' 
         WHEN 7 THEN 'KEY' 
         WHEN 8 THEN 'EXTEND' 
         WHEN 9 THEN 'RID ( ROW ID)' 
         WHEN 10 THEN 'APPLICATION' 
       END                              AS 'REQUEST_TYPE', 
       CASE REQ_OWNERTYPE 
         WHEN 1 THEN 'TRANSACTION' 
         WHEN 2 THEN 'CURSOR' 
         WHEN 3 THEN 'SESSION' 
         WHEN 4 THEN 'ExSESSION' 
       END                              AS 'REQUEST_OWNERTYPE', 
       Object_name(RSC_OBJID, RSC_DBID) AS 'OBJECT_NAME', 
       PROCESS.HOSTNAME, 
       PROCESS.NT_DOMAIN, 
       PROCESS.NT_USERNAME, 
       PROCESS.PROGRAM_NAME, 
       SQLTEXT.TEXT 
FROM   sys.syslockinfo LOCK 
       JOIN sys.sysprocesses PROCESS 
         ON LOCK.REQ_SPID = PROCESS.SPID 
       CROSS apply sys.DM_EXEC_SQL_TEXT(PROCESS.SQL_HANDLE) SQLTEXT 
WHERE  PROCESS.SPID = 65 

 

 

clip_image001

 

 

查询sys.dm_tran_locks我们可以得到更详细的信息,例如,从resource_description中我们可以得到file_id=1, 页面编号为273,这个页面的第一条记录(0)

 

 

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 = 65;

 

 

 

clip_image002

 

准备下面脚本,为了后续我们定位到行锁锁定哪一行记录。准备好后面脚本后,我们就可以开始测试了。注意,需要开启跟踪DBCC TRACEON(3604)否则DBCC PAGE没有任何输出信息

 

 

IF EXISTS (SELECT * FROM sys.objects WHERE type='U' AND name='DBCC_PAGE_RESULT')
    DROP TABLE DBCC_PAGE_RESULT;
GO
 
CREATE TABLE DBCC_PAGE_RESULT
(
    [ParentObject]      NVARCHAR(200),
    [Object]          NVARCHAR(2000),
    [Field]          NVARCHAR(4000),
    [Value]          NVARCHAR(MAX)
)
GO
 
CREATE PROCEDURE PRC_DBCC_PAGE
(
 @dbid        INT,
 @filenum    INT,
 @pagenum     INT
)
AS
 
 DBCC PAGE(@dbid, @filenum,  @pagenum, 3) WITH TABLERESULTS;
 
GO
 
DBCC TRACEON(3604)
 
 
 
 
;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     dbo.DBCC_PAGE_RESULT 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   ttt.row = 0

 

 

如下截图所示,就可以找到行锁(Row Lock)锁定了row=0这行记录(注意,这里的行记录是从0开始的,而不是1),也就是DatabaseLogID=1的记录。如果1:273:2, 那么查询条件中row=2  这个表示这个页面的第几行记录。

                                                                                                                                                                                                                                                      

 

 

clip_image003

 

 

但是,有时候你锁定了一行,查询sys.dm_tran_locks时,你会发现resource_type为RID类型的记录有好几条,如下所示:

 

USE AdventureWorks2012;
GO
 
SELECT  @@SPID;
 
BEGIN TRAN;
UPDATE  [dbo].[DatabaseLog] WITH ( ROWLOCK )
SET     TSQL = N'dddd'
WHERE   DatabaseLogID = 21;
--ROLLBACK;

 

 

clip_image004

 

 

其实真正是数据页的只有resource_description=1:273:4 这行记录, 也就是说这行记录位于Page Number=273下的第5条记录

 

clip_image005

 

 

其它一些页面,例如 1,295;  1,279等都不是数据页,如下截图所示:m_type的值表示这个是数据页、索引页、IAM页等等。具体参考

 

m_type

·         This is the page type. The values you’re likely to see are:

o   1 – data page. This holds data records in a heap or clustered index leaf-level.

o   2 – index page. This holds index records in the upper levels of a clustered index and all levels of non-clustered indexes.

o   3 – text mix page. A text page that holds small chunks of LOB values plus internal parts of text tree. These can be shared between LOB values in the same partition of an index or heap.

o   4 – text tree page. A text page that holds large chunks of LOB values from a single column value.

o   7 – sort page. A page that stores intermediate results during a sort operation.

o   8 – GAM page. Holds global allocation information about extents in a GAM interval (every data file is split into 4GB chunks – the number of extents that can be represented in a bitmap on a single database page). Basically whether an extent is allocated or not. GAM = Global Allocation Map. The first one is page 2 in each file. More on these in this post.

o   9 – SGAM page. Holds global allocation information about extents in a GAM interval. Basically whether an extent is available for allocating mixed-pages. SGAM = Shared GAM. the first one is page 3 in each file. More on these in this post.

o   10 – IAM page. Holds allocation information about which extents within a GAM interval are allocated to an allocation unit (portion of a table or index). IAM = Index Allocation Map. More on these in this post.

o   11 – PFS page. Holds allocation and free space information about pages within a PFS interval (every data file is also split into approx 64MB chunks – the number of pages that can be represented in a byte-map on a single database page. PFS = Page Free Space. The first one is page 1 in each file. More on these in this post.

o   13 – boot page. Holds information about the database. There’s only one of these in the database. It’s page 9 in file 1.

o   15 – file header page. Holds information about the file. There’s one per file and it’s page 0 in the file.

o   16 – diff map page. Holds information about which extents in a GAM interval have changed since the last full or differential backup. The first one is page 6 in each file.

o   17 – ML map page. Holds information about which extents in a GAM interval have changed while in bulk-logged mode since the last backup. This is what allows you to switch to bulk-logged mode for bulk-loads and index rebuilds without worrying about breaking a backup chain. The first one is page 7 in each file.

o   18 – a page that’s be deallocated by DBCC CHECKDB during a repair operation.

o   19 – the temporary page that ALTER INDEX … REORGANIZE (or DBCC INDEXDEFRAG) uses when working on an index.

o   20 – a page pre-allocated as part of a bulk load operation, which will eventually be formatted as a ‘real’ page.

 

 

clip_image006

 

clip_image007

 

 

 

 

 

参考资料:

 

http://blog.csdn.net/sqlserverdiscovery/article/details/13291629

https://www.sqlskills.com/blogs/paul/inside-the-storage-engine-anatomy-of-a-page/

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
如何本地修改SQL Server 2017数据库的默认端口
如何本地修改SQL Server 2017数据库的默认端口
255 0
SQL Server 数据库所有表增加同一列
SET @COLUMN_NAME = 'ColumnNameYouWantToAdd'; SET @COLUMN_DATATYPE = 'DataTypeOfColumn'; ------------------------------------------------Code----...
978 0
01. SQL Server 如何读写数据
原文:01. SQL Server 如何读写数据 一. 数据读写流程简要SQL Server作为一个关系型数据库,自然也维持了事务的ACID特性,数据库的读写冲突由事务隔离级别控制。无论有没有显示开启事务,事务都是存在的。
995 0
Java操作SQL Server数据库
//首先导入sql server2008的jar包:sqljdbc4.jar,注意在一般的电脑上即使做了这样的操作仍然是吧不能连接上SQL Server 2008的,如果想连上SQL SQLSERVER2008还要做一下配置:请看博文:http://hi.baidu.com/1032610746/item/a1a84d086a9225e43599023f    和   http://h
1678 0
SQL Server数据库的排序规则
1) 如果数据库的默认排序规则不是中文相关, 则数据库可能不接受任何中文字符, 输进去直接变乱码. 改变默认排序规则的位置: 数据库属性---选项----排序规则. (2) 如果两个表的排序规则不同, 则连接两表的时候, 会发生类似以下的错误:无法解决 equal to 运算中 "SQL_Latin1_General_CP1_CI_AS" 和 "Chinese_PRC_CI_AI" 之间的排序规则冲突。
645 0
SQL Server数据库表锁定原理以及如何解除锁定
http://www.searchdatabase.com.cn/showcontent_38568.htm
628 0
+关注
潇湘隐者
网名潇湘隐者/潇湘剑客、英文名Kerry,兴趣广泛,广泛涉猎,个性随意,不善言辞。执意做一名会写代码的DBA,混迹于IT行业
777
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
低代码开发师(初级)实战教程
立即下载
阿里巴巴DevOps 最佳实践手册
立即下载
冬季实战营第三期:MySQL数据库进阶实战
立即下载