【Oracle数据库死锁问题整理】

笑锝没心没肺

已于 2025-01-02 11:21:49 修改

阅读量561

点赞数 9

CC 4.0 BY-SA版权

分类专栏： Oracle 文章标签：数据库

于 2025-01-02 10:50:13 首次发布

本文链接：https://blog.youkuaiyun.com/xdmxmf0/article/details/144876957

Oracle 专栏收录该内容

42 篇文章

订阅专栏

重点SQL：

SELECT DECODE(L.BLOCK, 0, 'Waiting', 'Blocking ->') USER_STATUS, --blocking 阻塞，Waiting等待 一组阻塞blocking与waiting的关系是1：n
       CHR(39) || S.SID || ',' || S.SERIAL# || CHR(39) SID_SERIAL, -- v$session的Session ID与Session serial number
       'alter system kill session ' || '''' || S.SID || ',' || S.SERIAL# || ',@' ||
       S.INST_ID || ''';' COMMAND,
       S.INST_ID, --实例ID，单机始终为1，rac有 1，2，3，4...
       S.USERNAME, --sschema
       S.MACHINE, --连接到数据库的主机名
       S.LOGON_TIME,
       TO_CHAR(S.SQL_EXEC_START, 'yyyy-mm-dd hh24:mi:ss') SQL_EXEC_START,
       S.TYPE,
       S.PREV_SQL_ID, --该session的上一个sql_id 可以关联gv$sql 拿到具体的sql文本
       S.SQL_ID, --该session正在执行的sql_id 可以关联gv$sql 拿到具体的sql文本
       DECODE(L.TYPE,
              'RT',
              'Redo Log Buffer',
              'TD',
              'Dictionary',
              'TM',
              'DML',
              'TS',
              'Temp Segments',
              'TX',
              'Transaction',
              'UL',
              'User',
              'RW',
              'Row Wait',
              L.TYPE) LOCK_TYPE, --锁的类型
       DECODE(L.LMODE,
              0,
              'None',
              1,
              'Null',
              2,
              'Row Share',
              3,
              'Row Excl.',
              4,
              'Share',
              5,
              'S/Row Excl.',
              6,
              'Exclusive',
              LTRIM(TO_CHAR(LMODE, '990'))) LOCK_MODE, --锁的模式
       CTIME, --时间单位秒
       D.OWNER, --阻塞或者锁定的对象owner 
       D.OBJECT_NAME, --阻塞或者锁定的对象
       L.ID1, --V$TRANSACTION中的XIDUSN字段和XIDSLOT字段 id1 和 id2 用来判断是否是一组blocking waiting
       L.ID2 --V$TRANSACTION中的XIDSQN字段 id1 和 id2 用来判断是否是一组blocking waiting
  FROM GV$LOCK L
  JOIN GV$SESSION S
    ON (L.INST_ID = S.INST_ID AND L.SID = S.SID)
  JOIN GV$LOCKED_OBJECT O
    ON (O.INST_ID = S.INST_ID AND S.SID = O.SESSION_ID)
  JOIN ALL_OBJECTS D
    ON (D.OBJECT_ID = O.OBJECT_ID)
 WHERE (L.ID1, L.ID2, L.TYPE) IN
       (SELECT ID1, ID2, TYPE FROM GV$LOCK WHERE REQUEST > 0)
 ORDER BY ID1, ID2, CTIME DESC;

SELECT 'alter system kill session ''' || L.SESSION_ID || ',' || S.SERIAL# ||
       ''';' SQL_COMMAND,
       L.SESSION_ID SID,
       S.SERIAL#,
       L.LOCKED_MODE,
       DECODE(L.LOCKED_MODE,
              0,
              'none',
              1,
              'null',
              2,
              'Row-S 行共享(RS)：共享表锁',
              3,
              'Row-X 行独占(RX)：行锁，Insert, Update, Delete',
              4,
              'Share 共享锁(S)：阻止其他DML操作',
              5,
              'S/Row-X 共享行独占(SRX)：阻止其他事务操作',
              6,
              'exclusive 独占(X)：独立访问使用，Alter table, Drop table, Drop Index, Truncate table') 锁模式,
       L.ORACLE_USERNAME 登录用户,
       L.OS_USER_NAME 机器用户名,
       S.MACHINE 机器名,
       S.TERMINAL 终端用户名,
       O.OBJECT_NAME 被锁对象名,
       S.LOGON_TIME 登录数据库时间
  FROM V$LOCKED_OBJECT L, ALL_OBJECTS O, V$SESSION S
 WHERE L.OBJECT_ID = O.OBJECT_ID
   AND L.SESSION_ID = S.SID
 ORDER BY SID, S.SERIAL#;

刨根问底：

在Oracle数据库中，死锁是指两个或多个会话因为互相等待对方释放锁而无法继续执行的情况。要定位死锁，可以通过查询GV$LOCK视图与其他相关视图相结合来完成。以下是通过GV$LOCK定位死锁的一般步骤：

检查死锁：首先，你需要确定数据库中是否存在死锁。Oracle数据库通常会在发生死锁时自动检测并解决死锁，选择一个会话作为牺牲品回滚其事务。

你可以通过查询DBA_WAITERS或V$SESSION视图来检查是否有会话正在等待资源。
```
SELECT * FROM DBA_WAITERS;
```
或者
```
SELECT s.sid, s.serial#, s.username, s.osuser, w.event, w.p1, w.p2, w.p3
FROM v$session s, v$session_wait w
WHERE s.sid = w.sid
AND w.event LIKE '%enq:%';
```
查找死锁的会话：如果有会话在等待，你可能需要进一步查找是否有死锁发生。使用GV$LOCK视图来查找持有和请求锁的会话。
```
SELECT l.sid, l.id1, l.id2, l.lmode, l.request, l.block
FROM gv$lock l
WHERE l.block = 1;
```
这将列出所有阻塞其他会话的锁。

确定死锁的会话：接下来，你可以通过查找相互阻塞的会话来确定死锁。

SELECT s1.sid "Waiting Session", w1.event "Waiting For", s2.sid "Holding Session"
FROM gv$lock l1, gv$session s1, gv$lock l2, gv$session s2, gv$session_wait w1
WHERE s1.sid = w1.sid
AND l1.block = 1
AND l1.sid = s1.sid
AND l2.id1 = l1.id1
AND l2.id2 = l1.id2
AND l2.request > 0
AND l2.sid = s2.sid;

这个查询将显示等待会话（Waiting Session）正在等待的事件（Waiting For）以及持有锁的会话（Holding Session）。

分析死锁：确定了死锁的会话后，你需要分析这些会话正在执行的SQL语句和事务，以确定死锁的原因。
```
SELECT sql_text
FROM gv$sqltext_with_newlines
WHERE hash_value = (SELECT sql_hash_value FROM gv$session WHERE sid = :sid)
ORDER BY piece;
```
将:sid替换为实际会话的SID。
解决死锁：通常，Oracle会自动解决死锁，选择一个会话作为牺牲品回滚其事务。如果需要手动解决，你可以考虑以下方法：
- 结束持有锁的会话（ALTER SYSTEM KILL SESSION ‘sid,serial#’）。
- 回滚会话的事务。
- 优化SQL语句和索引，减少锁的竞争。