如何解決從運維角度測試全局死鎖以及帶來的問題

本篇內容介紹了“如何解決從運維角度測試全局死鎖以及帶來的問題”的有關知識，在實際案例的操作過程中，不少人都會遇到這樣的困境，接下來就讓小編帶領大家學習一下如何處理這些情況吧！希望大家仔細閱讀，能夠學有所成！

第一個節點
[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]
========================================================================
這個部分將全局死鎖等待圖描述得很清楚。

“如何解決從運維角度測試全局死鎖以及帶來的問題”的內容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業相關的知識可以關注億速云網站，小編將為大家輸出更多高質量的實用文章！