Shulou(Shulou.com)05/31 Report--

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

| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

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

| | 1 | UPDATE | TMP_X |

| | * 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

1894 enq: TX-row lock contention 2324

2324 enq: TX-row lock contention 1894

SQL > /

SID EVENT BLOCKING_SESSION

1894 enq: TX-row lock contention 2324

2324 enq: TX-row lock contention 1894

SQL > /

SID EVENT BLOCKING_SESSION

1894 enq: TX-row lock contention 2324

2324 enq: TX-row lock contention 1894

SQL > /

SID EVENT BLOCKING_SESSION

1894 enq: TX-row lock contention 2324

2324 enq: TX-row lock contention

SQL > /

SID EVENT BLOCKING_SESSION

1894 enq: TX-row lock contention 2324

2324 enq: TX-row lock contention

SQL > /

SID EVENT BLOCKING_SESSION

1894 enq: TX-row lock contention

2324 SQL*Net message from client

-- session 1

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

*

ERROR at line 1:

ORA-00060: deadlock detected while waiting for resource

3.4 Test conclusion

Two sessions each execute only one SQL, which can also form a deadlock.

On the two sessions to execute only one SQL will not form a deadlock to share here, I hope the above content can be of some help to you, you can learn more knowledge. If you think the article is good, you can share it for more people to see.

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.