为什么数据库有时候不能定位阻塞(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的记录,如下所示

目录
相关文章
|
18天前
|
SQL 开发框架 .NET
ASP.NET连接SQL数据库:详细步骤与最佳实践指南ali01n.xinmi1009fan.com
随着Web开发技术的不断进步,ASP.NET已成为一种非常流行的Web应用程序开发框架。在ASP.NET项目中,我们经常需要与数据库进行交互,特别是SQL数据库。本文将详细介绍如何在ASP.NET项目中连接SQL数据库,并提供最佳实践指南以确保开发过程的稳定性和效率。一、准备工作在开始之前,请确保您
93 3
|
1天前
|
SQL 数据采集 监控
局域网监控电脑屏幕软件:PL/SQL 实现的数据库关联监控
在当今网络环境中,基于PL/SQL的局域网监控系统对于企业和机构的信息安全至关重要。该系统包括屏幕数据采集、数据处理与分析、数据库关联与存储三个核心模块,能够提供全面而准确的监控信息,帮助管理者有效监督局域网内的电脑使用情况。
10 2
|
6天前
|
SQL JSON Java
没有数据库也能用 SQL
SPL(Structured Process Language)是一款开源软件,允许用户直接对CSV、XLS等文件进行SQL查询,无需将数据导入数据库。它提供了标准的JDBC驱动,支持复杂的SQL操作,如JOIN、子查询和WITH语句,还能处理非标准格式的文件和JSON数据。SPL不仅简化了数据查询,还提供了强大的计算能力和友好的IDE,适用于多种数据源的混合计算。
|
16天前
|
SQL 数据库
达梦数据库阻塞死锁及解锁
【10月更文挑战第6天】本文介绍了在达梦数据库中模拟和解决死锁的方法。首先通过创建表并插入数据但不提交事务,模拟了阻塞情况;接着利用V$TRXWAIT和V$SESSIONS视图查询阻塞信息,并通过SP_CLOSE_SESSION函数解决阻塞。最后讨论了死锁的成因及避免策略,强调了正确管理事务的重要性。
|
7天前
|
SQL 数据库
SQL数据库基础语法入门
[link](http://www.vvo.net.cn/post/082935.html)
|
14天前
|
SQL 存储 关系型数据库
mysql 数据库空间统计sql
mysql 数据库空间统计sql
31 0
|
18天前
|
SQL 存储 监控
串口调试助手连接SQL数据库的技巧与方法
串口调试助手是电子工程师和软件开发人员常用的工具,它能够帮助用户进行串口通信的调试和数据分析
|
18天前
|
SQL 存储 数据采集
如何把问卷录入SQL数据库
将问卷数据录入SQL数据库是一个涉及数据收集、处理和存储的过程
|
18天前
|
SQL 存储 关系型数据库
用SQL语句创建数据库:基础指南与操作示例4p7.0535yinshua.com
一、引言在当今数字化时代,数据库已成为各类应用的核心组成部分,用于存储、管理和检索大量数据。结构化查询语言(SQL)作为一种用于管理关系数据库系统的标准编程语言,广泛应用于数据库的创建、查询、更新和管理。本文将详细介绍如何使用SQL语句创建数据库,涵盖基本概念、操作步骤及常见示例,帮助读者快速掌握相
|
18天前
|
SQL 开发框架 .NET
ASP.NET连接SQL数据库:实现过程与关键细节解析an3.021-6232.com
随着互联网技术的快速发展,ASP.NET作为一种广泛使用的服务器端开发技术,其与数据库的交互操作成为了应用开发中的重要环节。本文将详细介绍在ASP.NET中如何连接SQL数据库,包括连接的基本概念、实现步骤、关键代码示例以及常见问题的解决方案。由于篇幅限制,本文不能保证达到完整的2000字,但会确保