查锁语句

查询数据库死锁

一、Oracle锁查询

(1)block_Session不为空时查询

col spid for a5
set linesize 200 pagesize 200
col username for a10
col event for a30
col program for a13
col machine for a10
col serial# for 99999
col sid for 99999
column object_name format a25
column sql_text format a40
alter session set nls_date_format='yyyymmdd hh24:mi:ss';
set echo on


WITH sessions AS
 (SELECT /*+materialize*/
   sid,
   a.serial#,
   a.username,
   machine,
   a.program,
   logon_time,
   blocking_session,
   row_wait_obj#,
   sql_id,
   spid,
   event
    FROM v$session a, v$process b
   where a.paddr = b.addr)
SELECT LPAD(' ', LEVEL) || sid sid,
       serial#,
       s.spid,
       s.username,
       s.machine,
       substr(s.program, 1, 12) program,
       object_name,
       s.logon_time,
       substr(sql_text, 1, 40) sql_text,
       event
  FROM sessions s
  LEFT OUTER JOIN dba_objects
    ON (object_id = row_wait_obj#)
  LEFT OUTER JOIN v$sql
 USING (sql_id)
 WHERE sid IN (SELECT blocking_session FROM sessions)
    OR blocking_session IS NOT NULL
CONNECT BY PRIOR sid = blocking_session
 START WITH blocking_session IS NULL;
           
           

(2)block_Session为空时查询
        
set linesize 500 pagesize 500
column "wait event" format a50 word_wrap
column "session" format a25
column "minutes" format 9999D9
column CHAIN_ID noprint
column N noprint
column l noprint
col username for a30
        
with w as
 (select chain_id,
         rownum n,
         level l,
         lpad(' ', level, ' ') ||
         (select instance_name from gv$instance where inst_id = w.instance) ||
         ' ''' || w.sid || ',' || w.sess_serial# || '@' || w.instance || '''' "session",
         lpad(' ', level, ' ') || w.wait_event_text || case
           when w.wait_event_text like 'enq: TM%' then
            ' mode ' || decode(w.p1,
                               1414332418,
                               'Row-S',
                               1414332419,
                               'Row-X',
                               1414332420,
                               'Share',
                               1414332421,
                               'Share RX',
                               1414332422,
                               'eXclusive') ||
            (select ' on ' || object_type || ' "' || owner || '"."' ||
                    object_name || '" '
               from all_objects
              where object_id = w.p2)
           when w.wait_event_text like 'enq: TX%' then
            (select ' on ' || object_type || ' "' || owner || '"."' ||
                    object_name || '" on rowid ' ||
                    dbms_rowid.rowid_create(1,
                                            data_object_id,
                                            relative_fno,
                                            w.row_wait_block#,
                                            w.row_wait_row#)
               from all_objects, dba_data_files
              where object_id = w.row_wait_obj#
                and w.row_wait_file# = file_id)
         end "wait event",
         w.in_wait_secs / 60 "minutes",
         s.username,
         s.program
    from v$wait_chains w
    join gv$session s
      on (s.sid = w.sid and s.serial# = w.sess_serial# and
         s.inst_id = w.instance)
  connect by prior w.sid = w.blocker_sid
         and prior w.sess_serial# = w.blocker_sess_serial#
         and prior w.instance = w.blocker_instance
   start with w.blocker_sid is null)
select *
  from w
 where chain_id in (select chain_id
                      from w
                     group by chain_id
                    having max("minutes") >= 1 and max(l) > 1)
 order by n;

        


---结果结果带Machine
        
with w as
 (select chain_id,
         rownum n,
         level l,
         lpad(' ', level, ' ') ||
         (select instance_name from gv$instance where inst_id = w.instance) ||
         ' ''' || w.sid || ',' || w.sess_serial# || '@' || w.instance || '''' "session",
         lpad(' ', level, ' ') || w.wait_event_text || case
           when w.wait_event_text like 'enq: TM%' then
            ' mode ' || decode(w.p1,
                               1414332418,
                               'Row-S',
                               1414332419,
                               'Row-X',
                               1414332420,
                               'Share',
                               1414332421,
                               'Share RX',
                               1414332422,
                               'eXclusive') ||
            (select ' on ' || object_type || ' "' || owner || '"."' ||
                    object_name || '" '
               from all_objects
              where object_id = w.p2)
           when w.wait_event_text like 'enq: TX%' then
            (select ' on ' || object_type || ' "' || owner || '"."' ||
                    object_name || '" on rowid ' ||
                    dbms_rowid.rowid_create(1,
                                            data_object_id,
                                            relative_fno,
                                            w.row_wait_block#,
                                            w.row_wait_row#)
               from all_objects, dba_data_files
              where object_id = w.row_wait_obj#
                and w.row_wait_file# = file_id)
         end "wait event",
         w.in_wait_secs / 60 "minutes",
         s.username,
         s.program,
		 s.machine
    from v$wait_chains w
    join gv$session s
      on (s.sid = w.sid and s.serial# = w.sess_serial# and
         s.inst_id = w.instance)
  connect by prior w.sid = w.blocker_sid
         and prior w.sess_serial# = w.blocker_sess_serial#
         and prior w.instance = w.blocker_instance
   start with w.blocker_sid is null)
select *
  from w
 where chain_id in (select chain_id
                      from w
                     group by chain_id
                    having max("minutes") >= 1 and max(l) > 1)
 order by n;
  

其它一些查锁语句:
SELECT sn.username,
       m.SID,
       sn.SERIAL#,
       m.TYPE,
       DECODE(m.lmode,
              0,
              'None',
              1,
              'Null',
              2,
              'Row Share',
              3,
              'Row Excl.',
              4,
              'Share',
              5,
              'S/Row Excl.',
              6,
              'Exclusive',
              lmode,
              LTRIM(TO_CHAR(lmode, '990'))) lmode,
       DECODE(m.request,
              0,
              'None',
              1,
              'Null',
              2,
              'Row Share',
              3,
              'Row Excl.',
              4,
              'Share',
              5,
              'S/Row Excl.',
              6,
              'Exclusive',
              request,
              LTRIM(TO_CHAR(m.request, '990'))) request,
       m.id1,
       m.id2
  FROM v$session sn, v$lock m
 WHERE (sn.SID = m.SID AND m.request != 0)
      --存在锁请求，即被阻塞
    OR (sn.SID = m.SID
       --不存在锁请求，但是锁定的对象被其他会话请求锁定
       AND m.request = 0 AND lmode != 4 AND
       (id1, id2) IN (SELECT s.id1, s.id2
                         FROM v$lock s
                        WHERE request != 0
                          AND s.id1 = m.id1
                          AND s.id2 = m.id2))
 ORDER BY id1, id2, m.request;
 
 
 
 select l.sid,
       l.type,
       (select lt.name
          from v$lock_type lt -- type
         where lt.type = l.type) 锁类型,
       l.lmode,
       decode(l.lmode,
              0,
              null,
              1,
              'null (NULL)',
              2,
              'row-S (SS)',
              3,
              'row-X (SX)',
              4,
              'share (S)', -- s 锁：共享锁
              5,
              'S/Row-X (SSX)',
              6,
              'exclusive (X)' -- x 锁：排他锁
              ) 锁模式
  from v$lock l
 where l.type in ('TM', 'TX') -- 通常关注该两个即可
 order by l.sid;



select l.sid         "会话ID",
       s.serial#     "会话序列号",
       p.spid        "会话进程号",
       l.type        "锁类型",
       s.username    "所属用户",
       s.machine     "客户端",
       o.object_name "被锁对象",
       o.object_type "被锁对象类型",
       l.ctime       "被锁时间(S)"
  from v$lock      l, -- addr
       v$session   s, -- saddr 
       dba_objects o, -- object_id 
       v$process   p -- addr 
 where s.sid = l.sid
   and o.object_id = l.id1
   and p.addr = s.paddr
   and l.type in ('TM', 'TX')
-- and s.username = ''


select sess.sid,
sess.serial#,
lo.oracle_username,
lo.os_user_name,
ao.object_name,
lo.locked_mode
from v$locked_object lo, dba_objects ao, v$session sess, v$process p
where ao.object_id = lo.object_id
and lo.session_id = sess.sid;
 
(3)查询等待时间超过1分钟的死锁
        
select *
  from (select 'blocker(' || lb.sid || ',' || sb.serial# || ':' ||
               sb.username || ':' || sb.machine || ':' ||
               round(sb.seconds_in_wait / 60, 1) || ':' || sb.event || ':' ||
               sb.program || ',' || lo.inst_id || ':' || sb.sql_id || ':' ||
               ')--->sql:' || qb.sql_text blockers,
               'waiter (' || lw.sid || ',' || sw.serial# || ':' ||
               sw.username || ':' || sw.machine || ':' ||
               round(sw.seconds_in_wait / 60, 1) || ':' || sw.event || ',' ||
               sw.program || ',' || lm.inst_id || ':' || sw.sql_id || ':' ||
               ')--->
       sql:' || qw.sql_text waiters,
               ao.object_name, -- 被锁表名
               ---lo.locked_mode, -- 死锁级别
               round(sw.seconds_in_wait / 60, 1) wait_time_mins,
               'alter system kill session ''' || lb.SID || ',' || sb.serial# || ',@' ||
               lo.inst_id || ''' immediate;' kill_blokers,
               'alter system kill session ''' || lw.SID || ',' || sw.serial# || ',@' ||
               lm.inst_id || ''' immediate;' kill_waiters
          from gv$lock          lb,
               gv$lock          lw,
               gv$session       sb,
               gv$session       sw,
               gv$sql           qb,
               gv$sql           qw,
               gv$locked_object lo,
               gv$locked_object lm,
               dba_objects      ao
         where lb.sid = sb.sid
           and lw.sid = sw.sid
           and sb.prev_sql_addr = qb.address
           and sw.sql_address = qw.address
           and lb.id1 = lw.id1
           and sw.lockwait is not null
           and sb.lockwait is null
           and lb.block = 1
           and ao.object_id = lo.object_id
           and lo.session_id = sb.sid
           and lm.session_id = sw.sid)
 where wait_time_mins > 1;
        
        
        
(4)批量解锁
        
select 'alter system kill session ''' || t.SID || ',' || t.SERIAL# || ''';' kill_sql
  from v$session t
 where t.STATUS = 'ACTIVE'
   and t.sql_id = 'gh07awufd5k98'
   and t.MODULE = 'JDBC Thin Client';
      
select 'kill -9 ' || p.spid
  from v$session s, v$process p
 where s.username is not null
   and s.paddr = p.addr
   and s.status = 'ACTIVE'
   and s.sid <> (select sid from v$mystat where rownum = 1)
   and sql_id = '31g8jw692q4gz'
 order by p.pid desc;

select 'alter system kill session ''' || s.sid || ',' || s.serial# ||
       ''' immediate;'
  from v$session s, v$process p
 where s.username is not null
   and s.paddr = p.addr
   and s.status = 'ACTIVE'
   and s.sid <> (select sid from v$mystat where rownum = 1)
   and sql_id = '31g8jw692q4gz';

select 'alter system kill session ''' || s.sid || ',' || s.serial# ||
       ''' immediate;'
  from v$session s, v$process p
 where s.username is not null
   and s.paddr = p.addr
   and s.status = 'ACTIVE'
   and s.sid <> (select sid from v$mystat where rownum = 1)
   and event = 'enq: TX - row lock contention'
   and s.username = 'MJIS';

select 'alter system kill session ''' || s.sid || ',' || s.serial# ||
       ''' immediate;'
  from v$session s, v$process p
 where s.username is not null
   and s.paddr = p.addr
   and s.status = 'ACTIVE'
   and s.sid <> (select sid from v$mystat where rownum = 1)
   and event = 'enq: TX - row lock contention'
   and s.username  in ('DPHIS','XPHIS','APHIS');

select 'kill -9 ' || p.spid
  from v$session s, v$process p
 where s.username is not null
   and s.paddr = p.addr
   and s.status = 'ACTIVE'
   and s.sid <> (select sid from v$mystat where rownum = 1)
   and event = 'enq: TX - row lock contention'
and s.username  in ('DPHIS','XPHIS','APHIS') 
 order by p.pid desc;
        
(5)查询未提交事务
---查询未提交的SQL语句
SELECT  S.SID
       ,S.SERIAL#
       ,S.USERNAME
       ,S.OSUSER 
       ,S.MACHINE
       ,S.PROGRAM 
       ,S.EVENT
       ,TO_CHAR(S.LOGON_TIME,'YYYY-MM-DD HH24:MI:SS') 
       ,TO_CHAR(T.START_DATE,'YYYY-MM-DD HH24:MI:SS') 
       ,S.LAST_CALL_ET 
       ,S.BLOCKING_SESSION   
       ,S.STATUS
       ,( 
              SELECT Q.SQL_TEXT 
              FROM    V$SQL Q 
              WHERE  Q.LAST_ACTIVE_TIME=T.START_DATE 
              AND    ROWNUM<=1) AS SQL_TEXT   
FROM   V$SESSION S, 
       V$TRANSACTION T  
WHERE  S.SADDR = T.SES_ADDR;


---查看plsql dev没有提交的锁定:
set linesize 300 pagesize 300
col machine for a22
col event for a32
col program for a25
col osuser for a18
col sid for 999999
SELECT s.sid,
       s.serial#,
       s.username,
       s.osuser,
       s.machine,
       s.program,
       s.event
  FROM v$session s, v$locked_object lo
 WHERE s.sid = lo.session_id;
        
select paddr,sid,serial#,status from v$session where sid=1142;
PADDR		     SID    SERIAL# STATUS
---------------- ------- ---------- --------
00000018627843D0    1142      11183 ACTIVE

select spid,pid from v$process where addr='00000018627843D0';
SPID				PID
------------------------ ----------
101238				 92

ps -ef|grep 101238
oracle   101238      1 52 Sep09 ?        4-03:34:20 oracleYJDB2 (LOCAL=NO)
oracle   118496 118408  0 16:19 pts/1    00:00:00 grep --color=auto 101238

        
---查历史锁
select user_id,
       session_id,
       session_serial#,
       sql_id,
       event,
       session_state,
       blocking_session,
       blocking_session_serial#
  from dba_hist_active_sess_history
 where SAMPLE_TIME between
       TO_TIMESTAMP('2025-03-14 16:00:00', 'YYYY-MM-DD HH24:MI:SS') and
       TO_TIMESTAMP('2025-03-14 16:42:00', 'YYYY-MM-DD HH24:MI:SS')
   and event = 'enq: TX - row lock contention';
        
        
select b.username,
       a.session_id,
       a.session_serial#,
       a.sql_id,
       a.event,
       a.session_state,
       a.blocking_session,
       a.blocking_session_serial#
  from dba_hist_active_sess_history a,dba_users b
 where a.SAMPLE_TIME between
       TO_TIMESTAMP('2025-03-14 15:00:00', 'YYYY-MM-DD HH24:MI:SS') and
       TO_TIMESTAMP('2025-03-14 16:00:00', 'YYYY-MM-DD HH24:MI:SS')
   and a.event = 'enq: TX - row lock contention' and a.user_id = b.user_id;

二、Mysql查看死锁

(1)processlist命令查看

show processlist/show full 

🖋️ show processlist查看state列是否有锁表语句


(2)INNODB_LOCKS系统表查看

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
 
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

🖋️ lock_table列显示当前被锁定的表

三、SQL Server查询死锁

(1)查看锁管理器资源

select request_session_id,
       resource_type as type,
	   resource_database_id,
	   request_status
from sys.dm_tran_locks;