Shulou(Shulou.com)06/03 Report--

This article introduces the relevant knowledge of "how to solve the global deadlock and the problems caused by testing the global deadlock from the perspective of operation and maintenance". In the operation of actual cases, many people will encounter such a dilemma. Next, let the editor lead you to learn how to deal with these situations! I hope you can read it carefully and be able to achieve something!

First node

[oracle@rac2 ~] $sqlplus scott/tiger@192.168.15.101:1521/prod

SQL > select userenv ('sid') from dual

USERENV ('SID')

-

two hundred and seventy nine

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.

Second node

[oracle@rac2 ~] $sqlplus scott/tiger@192.168.15.102:1521/prod

SQL > select userenv ('sid') from dual

USERENV ('SID')

-

forty-nine

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.

Continue on the second node

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

It gets stuck because the row is held by the first node with an exclusive lock.

Continue on the first node

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

At this point, the last statement executed by the first node is interrupted and the deadlock is released.

Looking at the alarm log, this log appears in the first node alert, and the lmd process discovers and handles the deadlock

2021-04-07T09:49:33.906946+08:00

Global Enqueue Services Deadlock detected (DID = 8801). More information in file

/ oracle/db/base/diag/rdbms/prod/prod1/trace/prod1_lmd0_15769.trc.

Moving on to the second node, the lock waiting continues. Here, Oracle breaks the deadlock from a global point of view, but there is still lock waiting, which is a problem at the transaction level.

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

Continue with the first node and roll back the data

SQL > rollback

Rollback complete.

Look at the second node, where the transaction continues.

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

1 row updated.

At the second node we also rolled back the data to end the test.

SQL > rollback

Rollback complete.

We continue to look at the dump file of the global deadlock lmd, and we continue to analyze the log

[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

Oracle process number: 22

Unix process pid: 15769, image: oracle@rac1 (LMD0)

The beginning provides the system information, database version, operating system information, an example of breaking the global deadlock, and the process LMD0 of the operation.

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

Deadlock session and 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

Deadlock session and SQL

The following is the wait diagram of the global lock, the information is abundant, and the relationship between waiting and blocking is clear.

=

Global Wait-For-Graph (WFG) for GES Deadlock ID= [8801]

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 0x0pl 0x4)

GES Transaction ID: 26000-0001-00000046

}

Which is blocked by Lock 0x7ec0c318 (Transaction):

{

Lock Level: KJUSEREX

Resource Name: TX 0x130004.0x1455 (ext 0x0pl 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 0x0rem 0x2)

GES Transaction ID: 3A000-0002-00000091

}

Which is blocked by Lock 0x7d0ec528 (Transaction):

{

Lock Level: KJUSEREX

Resource Name: TX 0x5000e.0xed4 (ext 0x0rem 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 0x0rem 0x2)

GES Transaction ID: 24000-0002-000000A2

}

Which is blocked by Lock 0x7ec2dbd0 (Transaction):

{

Lock Level: KJUSEREX

Resource Name: TX 0x5000e.0xed4 (ext 0x0rem 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 0x0pl 0x4)

GES Transaction ID: 37000-0001-000003E2

}

Which is blocked by Lock 0x7d0ec918 (Transaction):

{

Lock Level: KJUSEREX

Resource Name: TX 0x130004.0x1455 (ext 0x0pl 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]

=

This section clearly describes the global deadlock waiting graph.

This is the end of the content of "how to solve the global deadlock and the problems caused by testing from the perspective of operation and maintenance". Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.