update造成的死锁处理一例
现场环境是发生了表锁，update和delete某些记录都会卡住，但是select没问题，另外把这些数据都导出来到其他的表的dml操作都正常。
因为是集群环境只开始只注意了db1的解锁，忽略了db2的问题，后来发现问题后不想直接不分情况全部删除稍微测试了下定位出问题的session，减少数据损失。
1、检查死锁对象
集群环境执行：
select a.object_name,a.object_type,b.OBJECT_ID,b.SESSION_ID,b.INST_ID from dba_objects a,gv$locked_object b where a.object_id=b.object_id
单节点环境执行：
select a.object_name,a.object_type,b.OBJECT_ID,b.SESSION_ID from dba_objects a,v$locked_object b where a.object_id=b.object_id
SQL> col object_name format a20;
SQL> set lines 10000;
SQL> select a.object_name,a.object_type,b.OBJECT_ID,b.SESSION_ID,b.INST_ID from dba_objects a,gv$locked_object b where a.object_id=b.object_id
OBJECT_NAME OBJECT_TYPE OBJECT_ID SESSION_ID INST_ID
-------------------- ------------------- ---------- ---------- ----------
ZJDZ_T_DZCXXX TABLE 76076 672 2
ZJDZ_T_DZCXXX TABLE 76076 574 2
ZJDZ_T_DZCXXX TABLE 76076 480 2
ZJDZ_T_DZCXXX TABLE 76076 478 1
ZJDZ_T_DZCXXX TABLE 76076 382 1
ZJDZ_T_DZCXXX TABLE 76076 290 1
ZJDZ_T_DZJYXX TABLE 76078 672 2
ZJDZ_T_DZJYXX TABLE 76078 574 2
ZJDZ_T_DZJYXX TABLE 76078 480 2
ZJDZ_T_DZJYXX TABLE 76078 478 1
ZJDZ_T_DZJYXX TABLE 76078 382 1
OBJECT_NAME OBJECT_TYPE OBJECT_ID SESSION_ID INST_ID
-------------------- ------------------- ---------- ---------- ----------
ZJDZ_T_DZJYXX TABLE 76078 290 1
12 rows selected.
从结果来看有表ZJDZ_T_DZCXXX和表ZJDZ_T_DZJYXX产生了锁。最后一列的inst_id表示这个锁是在节点1还是节点2上的。
2、查看造成锁的语句：
集群环境执行：
select sql_text,INST_ID from gv$sql where hash_value in (select sql_hash_value from gv$session where sid in (select session_id from gv$locked_object));
单节点执行：
select sql_text from v$sql where hash_value in (select sql_hash_value from v$session where sid in (select session_id from v$locked_object));
SQL> select sql_text,INST_ID from gv$sql where hash_value in (select sql_hash_value from gv$session where sid in (select session_id from gv$locked_object));
SQL_TEXT INST_ID
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------
SELECT NVL(A.F_JQJE,0)F_JQJE,NVL(B.F_CXJE,0)F_CXJE,DECODE(NVL(A.F_JQJE,0),NVL(B.F_CXJE,0),1,0) C_HKBZ FROM (SELECT V_CXLSH,SUM(NVL(F_JE,0))F_JQJE FROM ZJDZ_T_DZYJXQ WHERE V_FFDM='JQ' AND V_CXLSH=:V_CXLSH GROUP BY V_CXLSH )A,(SELECT V_CXLSH,SUM(NVL(F_CXJE,0))F_CXJE FROM ZJDZ_T_DZCXXX WHERE N_HFBZ IS NULL AND V_CXLSH=:V_CXLSH GROUP BY V_CXLSH )B WHERE A.V_CXLSH=B.V_CXLSH(+) 1
SELECT NVL(A.F_JQJE,0)F_JQJE,NVL(B.F_CXJE,0)F_CXJE,DECODE(NVL(A.F_JQJE,0),NVL(B.F_CXJE,0),1,0) C_HKBZ FROM (SELECT V_CXLSH,SUM(NVL(F_JE,0))F_JQJE FROM ZJDZ_T_DZYJXQ WHERE V_FFDM='JQ' AND V_CXLSH=:V_CXLSH GROUP BY V_CXLSH )A,(SELECT V_CXLSH,SUM(NVL(F_CXJE,0))F_CXJE FROM ZJDZ_T_DZCXXX WHERE N_HFBZ IS NULL AND V_CXLSH=:V_CXLSH GROUP BY V_CXLSH )B WHERE A.V_CXLSH=B.V_CXLSH(+) 1
SELECT NVL(A.F_JQJE,0)F_JQJE,NVL(B.F_CXJE,0)F_CXJE,DECODE(NVL(A.F_JQJE,0),NVL(B.F_CXJE,0),1,0) C_HKBZ FROM (SELECT V_CXLSH,SUM(NVL(F_JE,0))F_JQJE FROM ZJDZ_T_DZYJXQ WHERE V_FFDM='JQ' AND V_CXLSH=:V_CXLSH GROUP BY V_CXLSH )A,(SELECT V_CXLSH,SUM(NVL(F_CXJE,0))F_CXJE FROM ZJDZ_T_DZCXXX WHERE N_HFBZ IS NULL AND V_CXLSH=:V_CXLSH GROUP BY V_CXLSH )B WHERE A.V_CXLSH=B.V_CXLSH(+) 2
UPDATE ZJDZ_T_DZJYXX SET F_HKJE=:F_HKJE,D_HKRQ=SYSDATE,C_HKBZ=:C_HKBZ,D_GXRQ=SYSDATE WHERE V_CXLSH=:V_CXLSH 1
UPDATE ZJDZ_T_DZJYXX SET F_HKJE=:F_HKJE,D_HKRQ=SYSDATE,C_HKBZ=:C_HKBZ,D_GXRQ=SYSDATE WHERE V_CXLSH=:V_CXLSH 1
UPDATE ZJDZ_T_DZJYXX SET F_HKJE=:F_HKJE,D_HKRQ=SYSDATE,C_HKBZ=:C_HKBZ,D_GXRQ=SYSDATE WHERE V_CXLSH=:V_CXLSH 1
UPDATE ZJDZ_T_DZJYXX SET F_HKJE=:F_HKJE,D_HKRQ=SYSDATE,C_HKBZ=:C_HKBZ,D_GXRQ=SYSDATE WHERE V_CXLSH=:V_CXLSH 1
UPDATE ZJDZ_T_DZJYXX SET F_HKJE=:F_HKJE,D_HKRQ=SYSDATE,C_HKBZ=:C_HKBZ,D_GXRQ=SYSDATE WHERE V_CXLSH=:V_CXLSH 1
UPDATE ZJDZ_T_DZJYXX SET F_HKJE=:F_HKJE,D_HKRQ=SYSDATE,C_HKBZ=:C_HKBZ,D_GXRQ=SYSDATE WHERE V_CXLSH=:V_CXLSH 1
UPDATE ZJDZ_T_DZJYXX SET F_HKJE=:F_HKJE,D_HKRQ=SYSDATE,C_HKBZ=:C_HKBZ,D_GXRQ=SYSDATE WHERE V_CXLSH=:V_CXLSH 1
UPDATE ZJDZ_T_DZJYXX SET F_HKJE=:F_HKJE,D_HKRQ=SYSDATE,C_HKBZ=:C_HKBZ,D_GXRQ=SYSDATE WHERE V_CXLSH=:V_CXLSH 2
11 rows selected.
从上面可以看到造成表锁的sql语句，这次主要考虑下面的update语句。从结果上开节点1上有7个update挂起，节点2有1个update挂起，至于在哪个造成的还需要进一步分析，当然也可以直接在这步删除所有的锁对象，包括节点1和节点2的锁。正常来说应该先确定出问题的session，只将该session kill。
3、查询造成行锁的session ID
集群执行：
select sid ,EVENT,INST_ID from gv$session_wait where event like '%row lock%';
单节点执行：
select sid ,EVENT from v$session_wait where event like '%row lock%';
SQL> select sid ,EVENT,INST_ID from gv$session_wait where event like '%row lock%';
SID EVENT INST_ID
---------- ------------------------------------------------------------ ----------
290 enq: TX - row lock contention 1
382 enq: TX - row lock contention 1
480 enq: TX - row lock contention 2
574 enq: TX - row lock contention 2
672 enq: TX - row lock contention 2
从上面可以看出发生行锁的session id是节点1的290、382 2个session，节点2的480、574、672 3个session。但是并不是所有的session都造成造成了表锁，而应该只有几个是源头，其他被动锁表。
4、确定哪个session造成其他session锁表的：
在步骤3查询出的sid中选择一个，选择290，执行查询看该session的锁情况：
集群执行：
select * from gv$lock where sid='290';
单节点执行：
select * from v$lock where sid='290';
从上面记录来看，存在tx和tm锁（tx锁表示行级锁，tm表示表级锁），这里考虑到有个别数据更新没问题，所以先考虑到行级锁即tx锁。
SQL> select * from gv$lock where sid='290' and type='TX';
注意这里的lmode和request想
lmode=6,request=0表示这个排它锁是有该session自行产生的，主动锁
lmode=0，request=6表示该session被其他session的锁影响而进入排队状态，先检查下由该session引起的锁是否对其他session产生影响，根据该记录的ID1和ID2值查看gv$lock：
（id1即为持有该锁的事务的回滚段号，id2为事务槽号）
SQL> select * from gv$lock where id1=524312 and id2=4128 ;
从上面记录来看sessionID为290的session并没有对其他的session产生被动锁。
再检查下该session进入排队状态的源头锁的session，同样通过id1和id2进行定位：
SQL> select * from gv$lock where id1=655390 and id2=103817;
从上面来看，request为0的session即节点1的S ID=478的session是造成节点1 SID=290的session进入队列的源头，为了证明我们查看下SID=478的session的锁或队列状态。
SQL> select * from gv$lock where sid='478';
从查询结果来看SID=478的session只产生锁并没有被其他session的锁影响而进入排队状态，基本可以确定锁的一个源头是节点1上SID=478的session，下面要做的就是查看下该session造成锁定的原因，看是在等待什么事件造成的锁：
SQL> select sid ,EVENT,INST_ID from gv$session_wait where sid=478 and INST_ID=1;
从结果来看该session一直处于等待状态没有提交事务导致表锁定，强行结束该session。
5、生成结束该sesson的语句：
select unique 'alter system kill session '||''''||a.sid||','||a.serial#||''''||';' from gv$session a where a.sid=478 and INST_ID=1;
执行该语句：
SQL> alter system kill session '478,19';
6、返回查看下锁的情况：
select a.object_name,a.object_type,b.OBJECT_ID,b.SESSION_ID,b.INST_ID from dba_objects a,gv$locked_object b where a.object_id=b.object_id;
如果还有锁定的对象找上面步骤处理，另外需要注意oracle在执行事务的时候基本会产生锁，所以并不是以一看到有锁定对象就有问题，有些锁是正常的。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/7863502/viewspace-739389/ ，如需转载，请注明出处，否则将追究法律责任。
