Will it form a deadlock for two sessions to execute only one SQL each?

Shulou Source: shulou.com Published: 2022-05-31 13:26:57 10月02日 Update

This article introduces whether only one SQL in two sessions will form a deadlock. The content is very detailed. Interested friends can use it for reference. I hope it will be helpful to you.

1, problem

Is it possible for two sessions to execute only one SQL each, resulting in a deadlock?

2. Test assumption

For a large table (such as 1 million records), when two sessions are updated from both ends (head and tail) of a large table, it is possible to wait for each other to possess resources, resulting in a deadlock.

3, test

3.1 Test version:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit Production

PL/SQL Release 11.2.0.3.0-Production

CORE 11.2.0.3.0 Production

TNS for IBM/AIX RISC System/6000: Version 11.2.0.3.0-Production

NLSRTL Version 11.2.0.3.0-Production

3.2 create a test table

Create table tmp_x (x int,y int)

Insert data:

Insert into tmp_x

Select rownum,rownum

From dual

Connect by level1

Select * from table (dbms_xplan.display (null,null,'Advanced'))

Plan hash value: 4167283686

| | 0 | UPDATE STATEMENT | | 999k | 9765K | 4340 (2) | 00:00:01 |

| | * 2 | INDEX RANGE SCAN | IDX_TMP_X_X | 999k | 9765K | 2254 (2) | 00:00:01 |

Predicate Information (identified by operation id):

2-access ("X" > 1)

Column Projection Information (identified by operation id):

2-(upd=3; cmp=2) "T" .ROWID [ROWID,10], "X" [NUMBER,22], "Y" [NUMBER,22]

Perform SQL:

Update / * + index_asc (t idx_tmp_x_x) * / tmp_x t set yearly 1 where x > 1

-- session 2

View the execution plan (using the prompt index_desc,CARDINALITY):

Explain plan for update / * + index_desc (t idx_tmp_x_x) CARDINALITY (t 1000) * / tmp_x t set yearly 2 where x /

SID EVENT BLOCKING_SESSION

1894 gc current request

2324 gc current request

SQL > /

SID EVENT BLOCKING_SESSION

1894 gc current request

2324 db file sequential read

SQL > /

SID EVENT BLOCKING_SESSION

1894 db file sequential read

2324 db file scattered read

SQL > /

SID EVENT BLOCKING_SESSION

1894 log buffer space

2324 log buffer space

SQL > /

SID EVENT BLOCKING_SESSION

1894 gc current request

2324 gc current request

SQL > /

SID EVENT BLOCKING_SESSION

1894 enq: TX-row lock contention

2324 enq: TX-row lock contention

SQL > /

SID EVENT BLOCKING_SESSION