为什么数据库有时候不能定位阻塞(Blocker)源头的SQL语句

简介: 原文:为什么数据库有时候不能定位阻塞(Blocker)源头的SQL语句    在SQL Server数据库或OACLE数据库当中,通常一个会话持有某个资源的锁,而另一个会话在请求这个资源,就会出现阻塞(blocking)。
原文: 为什么数据库有时候不能定位阻塞(Blocker)源头的SQL语句

    在SQL Server数据库或OACLE数据库当中,通常一个会话持有某个资源的锁,而另一个会话在请求这个资源,就会出现阻塞(blocking)。这是DBA经常会遇到的情况。当出现SQL语句的阻塞时,很多人想查看阻塞的源头(哪个SQL语句阻塞了哪个SQL),这样方便直观、简洁明了的定位问题。但是很多时候,很多场景,我们通过SQL语句并不能或者说不容易定位到阻塞者(Blocker)的SQL语句,当然我们可以很容易找到被阻塞的SQL语句,以及它在等待的锁资源。下面我们先分析一下SQL Server数据库的这类场景,然后分析一下ORACLE数据库的这类场景。如有不足的地方,敬请指出。

 

在SQL Server当中,我们先准备下面测试环境(测试用的表和数据)。

 
  
  
 
  
  
 
  
  
 
  
  
 
  
  
 
  
  
 
  
  
 
  
  
 
  
  
 
  
  
 
  
  
 
  
  
 
  
  
 
   
 

 

场景1:我们构造这样一个简单的场景,例如如下:

 

在会话81中执行下面SQL语句

BEGIN TRAN
    
    UPDATE Test SET NAME='Tina' WHERE ID=1000;

 

 

在会话72中执行下面SQL语句

SELECT * FROM TEST;

 

 

在另外一个会话窗口执行下面语句,查看阻塞(blocker)者和被阻塞者的SQL语句(这里能够定位到阻塞者(blocker)的SQL语句)。如下所示

SELECT wt.blocking_session_id                    AS BlockingSessesionId
      ,sp.program_name                           AS Blocking_ProgramName
      ,COALESCE(sp.LOGINAME, sp.nt_username)     AS Blocking_HostName    
      ,ec1.client_net_address                    AS ClientIpAddress
      ,db.name                                   AS DatabaseName        
      ,wt.wait_type                              AS WaitType                    
      ,ec1.connect_time                          AS BlockingStartTime
      ,wt.WAIT_DURATION_MS/1000                  AS WaitDuration
      ,ec1.session_id                            AS BlockedSessionId
      ,h1.TEXT                                   AS BlockedSQLText
      ,h2.TEXT                                   AS BlockingSQLText
FROM sys.dm_tran_locks  AS tl WITH(NOLOCK)
INNER JOIN sys.databases AS db  WITH(NOLOCK)
  ON db.database_id = tl.resource_database_id
INNER JOIN sys.dm_os_waiting_tasks AS wt  WITH(NOLOCK)
  ON tl.lock_owner_address = wt.resource_address
INNER JOIN sys.dm_exec_connections  ec1 WITH(NOLOCK)
  ON ec1.session_id = tl.request_session_id
INNER JOIN sys.dm_exec_connections  ec2 WITH(NOLOCK)
  ON ec2.session_id = wt.blocking_session_id
LEFT OUTER JOIN master.dbo.sysprocesses AS sp WITH(NOLOCK)
  ON SP.spid = wt.blocking_session_id
CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1 
CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2 

 

但是这个场景是一个非常理想化的场景,实际场景中,可能会话81接下来会去执行其它SQL语句,它并不会一直停留在这个SQL语句上,例如,我们在会话81中执行SELECT GETDATE();这个SQL语句

BEGIN TRAN
    
 UPDATE Test SET NAME='Tina' WHERE ID=1000;
 
 SELECT GETDATE();

 

如上所示,此时查到的Blocker者的SQL语句为"SELECT GETDATE();", 而这个SQL其实和被阻塞的SQL没有半毛关系。即使使用sp_WhoIsActive这样专业的SQL亦是如此。

 

当然我们可以查看其等待的锁对象信息,这也是我们所能追踪、捕获的。如下所示:

<Database name="Test">
  <Locks>
    <Lock request_mode="S" request_status="GRANT" request_count="1" />
  </Locks>
  <Objects>
    <Object name="Test" schema_name="dbo">
      <Locks>
        <Lock resource_type="OBJECT" request_mode="IS" request_status="GRANT" request_count="1" />
        <Lock resource_type="PAGE" page_type="*" request_mode="IS" request_status="GRANT" request_count="1" />
        <Lock resource_type="RID" page_type="*" request_mode="S" request_status="WAIT" request_count="1" />
      </Locks>
    </Object>
  </Objects>
</Database>

 

这种场景,如果只是某个会话发出的即席查询,那么你几乎已经很难捕获到阻塞的源头UPDATE Test SET NAME='Tina' WHERE ID=1000这个SQL语句了。除非你结合其它一些手段,逆向推断。

 

场景2:上面查找SQL阻塞的SQL语句,有时候只能定位到某一个存储过程或一大段即席查询SQL。

 

例如,下面一个构造的存储过程,一个用户正在一个会话当中执行它,

CREATE PROCEDURE PRC_TEST
AS 
BEGIN 
 
    BEGIN TRAN TR1
 
    UPDATE Test SET NAME='YourName' WHERE ID=1000;
 
    SELECT * FROM sys.sysprocesses WHERE spid=@@SPID;
 
    WAITFOR DELAY '00:00:20';
 
    COMMIT TRAN TR1;
 
END
GO 

 

另外一个用户在另外一个会话执行下面查询SQL语句

 
SELECT * FROM TEST;

 

查看阻塞的历史记录

 

你会看到捕获的是整个存储过程,当然这个测试案例很容易知道是那个SQL语句阻塞了,实际的存储过程可能业务很复杂,SQL语句也非常多,你想从一个存储过程里面找到阻塞者(Blocker)的SQL语句其实是非常麻烦的。需要你仔细甄别,当存储过程的业务逻辑复杂,SQL语句非常多时,这是一个头痛的事情

其实遇到这些场景,我们大可不必一定要查看阻塞这(Blocker)的具体SQL,我们只需要查看被阻塞者,等待的锁对象资源的相关信息即可,你可以大致判断到底是一个什么类型的SQL导致了这类阻塞。

 

 

那么我们接下来看看ORACLE数据库场景吧。我们先准备一个测试环境(测试表和相关数据)

 

CREATE TABLE "TEST"."TEST" 
(    "ID" NUMBER, 
    "NAME" VARCHAR2(12)
);
 
INSERT INTO TEST
SELECT 1001, 'jimmy' FROM DUAL UNION ALL
SELECT 1002, 'Kerry' FROM DUAL;
 
COMMIT;

 

 

接下来我们在会话窗口一执行下面SQL:

[oracle@DB-Server ~]$ sqlplus test/test
 
SQL*Plus: Release 11.2.0.1.0 Production on Tue Aug 30 10:16:43 2016
 
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
 
SQL> show user;
USER is "TEST"
SQL> UPDATE TEST SET NAME='KKK' WHERE ID =1001;
 
1 row updated.
 
SQL> 

 

在另外一个会话窗口二执行下面SQL

[oracle@DB-Server ~]$ sqlplus test/test
 
SQL*Plus: Release 11.2.0.1.0 Production on Tue Aug 30 10:17:22 2016
 
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
 
SQL> show user;
USER is "TEST"
SQL> UPDATE TEST SET NAME='Ken' WHERE ID =1001;

 

然后我们在第三个窗口执行下面SQL语句,查看阻塞和被阻塞的SQL语句

SELECT dba_objects.object_name,
       locks_t.row#,
       locks_t.blocked_secs,
       locks_t.blocker_text,
       locks_t.blocked_text,
       locks_t.blocked_sql_text,
       locks_t.blocking_sql_text
  FROM (SELECT /*+ NO_MERGE */
               blocking_lock_session.username||'@'||blocking_lock_session.machine||'(SID='||blocking_lock_session.sid||') ['||
               blocking_lock_session.program||'/PID='||blocking_lock_session.process||']' as blocker_text,
               blocked_lock_session.username||'@'||blocked_lock_session.machine|| '(SID='||blocked_lock_session.sid||') ['||
               blocked_lock_session.program||'/PID='||blocked_lock_session.process||']' as blocked_text,
               blocked_lock_session.row_wait_obj#,
               blocked_lock_session.row_wait_file#,
               blocked_lock_session.row_wait_block#,
               blocked_lock_session.row_wait_row#,
               DBMS_ROWID.ROWID_CREATE (1,
                  blocked_lock_session.row_wait_obj#,
                  blocked_lock_session.row_wait_file#,
                  blocked_lock_session.row_wait_block#,
                  blocked_lock_session.row_wait_row#) row#,
               blocked_lock_session.seconds_in_wait blocked_secs,
               blocked_sql.sql_text blocked_sql_text,
               blocking_sql.sql_text blocking_sql_text
          FROM v$lock blocking_lock,
               v$session blocking_lock_session,
               v$lock blocked_lock,
               v$session blocked_lock_session,
               v$sql blocked_sql,
               v$sql blocking_sql
         WHERE blocking_lock.block = 1
           AND blocking_lock.id1 = blocked_lock.id1
           AND blocking_lock.id2 = blocked_lock.id2
           AND blocked_lock.request > 0
           AND blocking_lock.sid = blocking_lock_session.sid
           AND blocked_lock.sid = blocked_lock_session.sid
           AND blocked_lock_session.sql_id = blocked_sql.sql_id
           AND blocked_lock_session.sql_child_number = blocked_sql.child_number
           AND blocking_lock_session.PREV_SQL_ADDR(+)  =blocking_sql.ADDRESS
       ) locks_t,
       dba_objects
 WHERE locks_t.row_wait_obj# = dba_objects.object_id
ORDER BY locks_t.blocked_secs;

 

如果我们在会话窗口1,再执行一个语句,如下所示

 

SQL> show user;
USER is "TEST"
SQL> UPDATE TEST SET NAME='KKK' WHERE ID =1001;
 
1 row updated.
 
SQL> select * from dual;
 
D
-
X

 

此时捕获到的是select * from dual; 这个SQL跟被阻塞的SQL没有任何关系,当然如果你继续在会话窗口执行其它SQL语句,捕获的都是不相关的SQL语句,已经没有任何意义

 

出现这个问题,是因为当一个会话正在执行某个SQL语句,那么v$session视图中的SQL_ID记录的是正在执行SQL的SQL_ID,当会话空闲或执行其它SQL语句后,SQL_ID就会变化,PRE_SQL_ID记录上一个执行完的SQL的SQL_ID值,PREV_SQL_ADDR也是如此。如下英文所述

 

According to the Reference Manual entry for V$SESSION the SQL_ID column represents the current SQL statement being executed by a session.  If the session is idle there is no current SQL statement.  Also if a session performs an update then performs a query the SQL_ID would reflect the query and not the update which is the statement that is blocking.  There is in fact no query that is guaranteed to find the blocking SQL.  Unless the blocking statement is the current statement all you can find for sure I the blocking session

 

如果你不用SQL*Plus,使用PL/SQL Developer这个工具,你会看到BLOCKING_SQL_TEST永远都是begin sys.dbms_output.get_line(line => :line, status => :status); end; 这个是因为PL/SQL Developer在执行完SQL后,会调用其它SQL语句,当然SQL Developer不会有这样的问题。

 

所以综上述,想要找到阻塞的源头SQL语句,只用SQL查询,其实在很多场景是不太现实的,所以很多SQL语句都只给出阻塞者的会话信息或锁定对象信息。如下所示

 

会话ID为8的会话执行下面SQL

UPDATE TEST SET NAME='TEST' WHERE ID=1001;

 

会话ID为137的会话执行下面SQL

UPDATE TEST SET NAME='TES1' WHERE ID=1001;

然后我们使用get_locked_objects_rpt.sql查看被阻塞的SQL,以及锁定相关对象的信息(get_locked_objects_rpt.sql请参考get_locked_objects_rpt.sql

SQL> @get_locked_objects_rpt.sql
Enter value for 1: 6 
old  42:    AND locks_t.blocked_secs > &1
new  42:    AND locks_t.blocked_secs > 6
========= $Revision: 1.4 $ ($Date: 2013/09/16 13:15:22 $) ===========
Locked object : TEST
Locked row#   : AAASEkAAEAAAADVAAA
Blocked for   : 19 seconds
Blocker info. : TEST@GFG1\GET253194(SID=8) [plsqldev.exe/PID=17988:14616]
Blocked info. : TEST@get253194(SID=137) [SQL Developer/PID=17780]
Blocked SQL   : UPDATE TEST SET NAME='TES1' WHERE ID=1001
Found 1 blocked session(s).
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@DB-Server ~]$ 

然后我通过上面的Locked Object知道被锁定的对象为Test表的ROWID为AAASRCAAEAAAADVAAA的记录,如下所示

目录
相关文章
|
16天前
|
SQL 数据库
达梦数据库阻塞死锁及解锁
【10月更文挑战第6天】本文介绍了在达梦数据库中模拟和解决死锁的方法。首先通过创建表并插入数据但不提交事务,模拟了阻塞情况;接着利用V$TRXWAIT和V$SESSIONS视图查询阻塞信息,并通过SP_CLOSE_SESSION函数解决阻塞。最后讨论了死锁的成因及避免策略,强调了正确管理事务的重要性。
|
3月前
|
Java Apache Android开发
Struts 2的秘密武器:揭秘社区中隐藏的学习宝藏,让你从新手到高手的不归路!
【8月更文挑战第31天】Struts 2学习资源丰富,除官方文档外,TutorialsPoint和W3Schools等网站提供详尽教程;《Apache Struts 2实战》等书籍含全面实例。Udemy、Pluralsight及YouTube上视频课程众多,Apache Software Foundation亦有网络研讨会。实践方面,GitHub上的开源项目及个人小项目都是好选择。寻求帮助可访问Apache官方论坛、Stack Overflow等平台。
38 0
|
4月前
|
SQL DataWorks 关系型数据库
DataWorks产品使用合集之数据集成时源头提供数据库自定义函数调用返回数据,数据源端是否可以写自定义SQL实现
DataWorks作为一站式的数据开发与治理平台,提供了从数据采集、清洗、开发、调度、服务化、质量监控到安全管理的全套解决方案,帮助企业构建高效、规范、安全的大数据处理体系。以下是对DataWorks产品使用合集的概述,涵盖数据处理的各个环节。
|
5月前
|
SQL 监控 安全
代码审计-PHP原生开发篇&SQL注入&数据库监控&正则搜索&文件定位&静态分析
代码审计-PHP原生开发篇&SQL注入&数据库监控&正则搜索&文件定位&静态分析
|
6月前
|
存储 安全 关系型数据库
4个MySQL优化工具AWR,帮你准确定位数据库瓶颈!
4个MySQL优化工具AWR,帮你准确定位数据库瓶颈!
131 0
|
数据库
IntelliJ IDEA 如何快速的定位数据库某张表在哪里调用
IntelliJ IDEA 如何快速的定位数据库某张表在哪里调用
130 0
|
SQL 缓存 监控
MySQL慢查询:慢SQL定位、日志分析与优化方案,真心不错!
MySQL慢查询:慢SQL定位、日志分析与优化方案,真心不错!
MySQL慢查询:慢SQL定位、日志分析与优化方案,真心不错!
|
SQL 存储 关系型数据库
数据库优化——慢查询MySQL定位优化流程
如何定位并优化慢查询SQL?如何使用慢查询日志?本文带来慢查询例子演示,新手都能看懂!那查询语句慢怎么办?explain带你分析sql执行计划!当主键索引、唯一索引、普通索引都存在,查询优化器如何选择?本文带你一探究竟!
627 0
数据库优化——慢查询MySQL定位优化流程
|
SQL 关系型数据库 MySQL
详解MySQL慢SQL定位、分析
1.概述 解决慢SQL的问题无非3步: 定位慢SQL 分析慢SQL 优化慢SQL 本文将按顺序介绍前两步该怎么做,第三步将会在后续的文章中详细讨论。
741 0
|
SQL 关系型数据库 MySQL
MySQL慢查询日志:如何定位执行慢的sql语句
MySQL慢查询日志:如何定位执行慢的sql语句
440 0
MySQL慢查询日志:如何定位执行慢的sql语句