绑定完请刷新页面
取消
刷新

分享好友

×
取消 复制
从运维角度测试全局死锁以及带来的问题
2021-04-07 10:24:24

从运维角度测试全局死锁以及带来的问题

第一个节点
[oracle@rac2 ~]$ sqlplus scott/tiger@192.168.15.101:1521/prod

SQL> select userenv('sid') from dual;

USERENV('SID')
--------------
279

SQL> select serial# ,sid from v$session where sid=279;

SERIAL# SID
---------- ----------
64364 279

SQL> update emp set ename='test' where empno=7788;

1 row updated.

第二个节点
[oracle@rac2 ~]$ sqlplus scott/tiger@192.168.15.102:1521/prod

SQL> select userenv('sid') from dual;

USERENV('SID')
--------------
49

SQL> select serial# ,sid from v$session where sid=49;

SERIAL# SID
---------- ----------
24429 49

SQL> update emp set ename='test2' where empno=7369;

1 row updated.


继续在第二个节点
SQL> update emp set ename='test3' where empno=7788;

此时卡住,因为该行被第一个节点持有排他锁

继续在第一个节点

SQL>
SQL> update emp set ename='test1' where empno=7369;
update emp set ename='test1' where empno=7369
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource

此时第一个节点最后执行的语句被打断,解除了死锁

看告警日志,这个日志会在第一个节点alert中出现 ,此时lmd进程发现了并处理死锁
2021-04-07T09:49:33.906946+08:00
Global Enqueue Services Deadlock detected (DID = 8_0_1). More information in file
/oracle/db/base/diag/rdbms/prod/prod1/trace/prod1_lmd0_15769.trc.

继续看第二个节点,此时锁等待还是继续,这里只是Oracle从全局的角度打破了死锁,但是锁等待还是有,这时事务层面的问题。
SQL> update emp set ename='test3' where empno=7788;

继续第一个节点,回滚数据
SQL> rollback;

Rollback complete.

看第二个节点,此时事务继续执行。

SQL> update emp set ename='test3' where empno=7788;

1 row updated.

在第二个节点我们也回滚数据,结束这次测试。
SQL> rollback;

Rollback complete.


我们继续看全局死锁的lmd的dump文件,我们继续分析日志
[oracle@rac1 trace]$ cat /oracle/db/base/diag/rdbms/prod/prod1/trace/prod1_lmd0_15769.trc | more
Trace file /oracle/db/base/diag/rdbms/prod/prod1/trace/prod1_lmd0_15769.trc
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Build label: RDBMS_12.2.0.1.0_LINUX.X64_170125
ORACLE_HOME: /oracle/db/base/product/12.2
System name: Linux
Node name: rac1
Release: 4.14.35-1902.3.2.el7uek.x86_64
Version: #2 SMP Tue Jul 30 03:59:02 GMT 2019
Machine: x86_64
Instance name: prod1
Redo thread mounted by this instance: 0 <none>
Oracle process number: 22
Unix process pid: 15769, image: oracle@rac1 (LMD0)

开头部分提供了系统信息,数据库版本,操作系统信息,打断全局死锁的实例,该操作的进程LMD0

user session for deadlock lock 0x7ec2dbd0
sid: 279 ser: 64364 audsid: 3130108 user: 108/SCOTT
flags: (0x41) USR/- flags2: (0x40009) -/-/INC
flags_idl: (0x1) status: BSY/-/-/- kill: -/-/-/-
pid: 55 O/S info: user: grid, term: UNKNOWN, ospid: 4043
image: oracle@rac1
client details:
O/S info: user: oracle, term: pts/0, ospid: 4040
machine: rac1 program: sqlplus@rac1 (TNS V1-V3)
application name: SQL*Plus, hash value=3669949024
current SQL:
update emp set ename=:"SYS_B_0" where empno=:"SYS_B_1"
DUMP LOCAL BLOCKER: initiate state dump for DEADLOCK
possible owner[55.4043] on resource TX-0005000E-00000ED4-00000000-00000002

死锁会话和SQL

user session for deadlock lock 0x7d0ec918
sid: 46 ser: 25 audsid: 3130107 user: 108/SCOTT
flags: (0x41) USR/- flags2: (0x40009) -/-/INC
flags_idl: (0x1) status: BSY/-/-/- kill: -/-/-/-
pid: 38 O/S info: user: grid, term: UNKNOWN, ospid: 3874
image: oracle@rac1
client details:
O/S info: user: oracle, term: pts/3, ospid: 3872
machine: rac1 program: sqlplus@rac1 (TNS V1-V3)
application name: SQL*Plus, hash value=3669949024
current SQL:
update emp set ename=:"SYS_B_0" where empno=:"SYS_B_1"
DUMP LOCAL BLOCKER: initiate state dump for DEADLOCK
possible owner[38.3874] on resource TX-00130004-00001455-00000000-00000004

死锁会话和SQL

下面是全局锁的等待关系图,信息很充足,等待和阻塞关系很明了。
========================================================================
Global Wait-For-Graph(WFG) for GES Deadlock ID=[8_0_1]
------------------------------------------------------------------------
Victim : (instance=1, lock=0x7d0ecb28)
Start (master) Instance : 1
Number of Locks involved : 8
Number of Sessions involved : 4

User session identified by:
{
User Name : oracle
User Machine : rac1
OS Terminal Name : pts/3WN
OS Process ID : 3872
OS Program Name : sqlplus@rac1 (TNS V1-V3)
Application Name : SQL*Plusrac1 (TNS V1-V3)
Action Name : Automatic Report Flushuponment Statistics Flush
Current SQL : update emp set ename=:"SYS_B_0" where empno=:"SYS_B_1"
Session Number : 46
Session Serial Number : 25
Server Process ORAPID : 38
Server Process OSPID : 3874
Instance : 1
}
waiting for Lock 0x7d0ec918 (Transaction):
{
Lock Level : KJUSEREX
Resource Name : TX 0x130004.0x1455(ext 0x0,0x4)
GES Transaction ID : 26000-0001-00000046
}
which is blocked by Lock 0x7ec0c318 (Transaction):
{
Lock Level : KJUSEREX
Resource Name : TX 0x130004.0x1455(ext 0x0,0x4)
GES Transaction ID : 3A000-0002-00000091
}
owned by the
User session identified by:
{
User Name : oracle
User Machine : rac2
OS Terminal Name : pts/0WN
OS Process ID : 11818
OS Program Name : sqlplus@rac2 (TNS V1-V3)
Application Name : SQL*Plusrac2 (TNS V1-V3)
Action Name : ASH Progressive-Flusho instancestatistics Flush
Current SQL : update emp set ename=:"SYS_B_0" where empno=:"SYS_B_1"
Session Number : 49
Session Serial Number : 24429
Server Process ORAPID : 58
Server Process OSPID : 11820
Instance : 2
}
waiting for Lock 0x7d0ec918 (Transaction):
{
Lock Level : KJUSEREX
Resource Name : TX 0x5000e.0xed4(ext 0x0,0x2)
GES Transaction ID : 3A000-0002-00000091
}
which is blocked by Lock 0x7d0ec528 (Transaction):
{
Lock Level : KJUSEREX
Resource Name : TX 0x5000e.0xed4(ext 0x0,0x2)
GES Transaction ID : 24000-0002-000000A2
}
owned by the
User session identified by:
{
User Name : oracle
User Machine : rac2
OS Terminal Name : pts/1WN
OS Process ID : 14496
OS Program Name : sqlplus@rac2 (TNS V1-V3)
Application Name : SQL*Plusrac2 (TNS V1-V3)
Action Name : Auto-CPUUSAGE Actionhresholdst Statistics Flush
Current SQL : update emp set ename=:"SYS_B_0" where empno=:"SYS_B_1"
Session Number : 46
Session Serial Number : 39025
Server Process ORAPID : 36
Server Process OSPID : 14499
Instance : 2
}
waiting for Lock 0x7d0ec528 (Transaction):
{
Lock Level : KJUSEREX
Resource Name : TX 0x5000e.0xed4(ext 0x0,0x2)
GES Transaction ID : 24000-0002-000000A2
}
which is blocked by Lock 0x7ec2dbd0 (Transaction):
{
Lock Level : KJUSEREX
Resource Name : TX 0x5000e.0xed4(ext 0x0,0x2)
GES Transaction ID : 37000-0001-000003E2
}
owned by the
User session identified by:
{
User Name : oracle
User Machine : rac1
OS Terminal Name : pts/0WN
OS Process ID : 4040
OS Program Name : sqlplus@rac1 (TNS V1-V3)
Application Name : SQL*Plusrac1 (TNS V1-V3)
Action Name : KTSJ Slaveblespace Thresholds
Current SQL : update emp set ename=:"SYS_B_0" where empno=:"SYS_B_1"
Session Number : 279
Session Serial Number : 64364
Server Process ORAPID : 55
Server Process OSPID : 4043
Instance : 1
}
waiting for Lock 0x7d0ecb28 (Transaction):
{
Lock Level : KJUSEREX
Resource Name : TX 0x130004.0x1455(ext 0x0,0x4)
GES Transaction ID : 37000-0001-000003E2
}
which is blocked by Lock 0x7d0ec918 (Transaction):
{
Lock Level : KJUSEREX
Resource Name : TX 0x130004.0x1455(ext 0x0,0x4)
GES Transaction ID : 26000-0001-00000046
}
owned by the first user session of the WFG.
------------------------------------------------------------------------
End of Global WFG for GES Deadlock ID=[8_0_1]
========================================================================

这个部分将全局死锁等待图描述得很清楚。

总结:
全局死锁,属于业务逻辑有问题,从不不同实例,对相同对象得记录做操作,这个问题可以通过指定service让其从一个节点执行,但是这样依然会发生死锁,最终还是得从业务逻辑做调整,再者如果该问题业务逻辑无法修改,会生成大量得dump文件,根据情况不通产生得大小也有差异,但是这个问题很可能会撑爆Oracle得软件目录,所以还需要定时删除对应得dump文件,以防止实例挂掉!

分享好友

分享这个小栈给你的朋友们,一起进步吧。

Oracle运维新鲜事-技术与管理各占半边天
创建时间:2020-08-04 11:34:57
本技术栈旨在分享技术心得,运维趣事,故障处理经验,调优案例,故障处理涉及集群,DG,OGG,大家生产中遇到的问题基本都会囊括了,我会发布生产库遇到的故障,希望在交流中互助互益,共同提高,也希望大家讨论,如果您有生产中遇到的集群问题,也可以在这里提出来,一起讨论,现实中也帮助不少同学解决了生产库的故障。
展开
订阅须知

• 所有用户可根据关注领域订阅专区或所有专区

• 付费订阅:虚拟交易,一经交易不退款;若特殊情况,可3日内客服咨询

• 专区发布评论属默认订阅所评论专区(除付费小栈外)

栈主、嘉宾

查看更多
  • Abraham林老师
    栈主
  • 小雨滴
    嘉宾
  • hawkliu
    嘉宾
  • u_97a59a25246404
    嘉宾

小栈成员

查看更多
  • 栈栈
  • dapan
  • 小菜鸟___
  • hwayw
戳我,来吐槽~