Oracle LOCK internal mechanism and Best Practices series (5) provides an SQL example that causes a deadlock

An SQL example that causes a deadlock is provided.

Deadlock definition: in a broad sense, it includes the operating system application database. If two Process sessions hold each other's resources, they always wait for the other to release. This situation may cause a deadlock.
Misunderstanding: Session blocking is not a deadlock, because one of the sessions can continue to operate.
Release: Oracle automatically detects deadlocks and forcibly blocks the release.

LEO1 @ LEO1> create table p1 (x int primary key); create a p1 table and set the x field as the primary key.
Table created.
LEO1 @ LEO1> insert into leo1.p1 values (10); 138 sessions insert 10
1 row created.
LEO2 @ LEO1> insert into leo1.p1 values (20); 156 sessions insert 20
1 row created.
LEO1 @ LEO1> select sid, type, id1, id2, lmode, request, block from v $ lock where type in ('TT', 'tx ') order by 1, 2;
Sid type ID1 ID2 LMODE REQUEST BLOCK
----------------------------------------------------------------------
138 TM 73470 0 3 0 0
138 TX 327713 1124 6 0 0 138 has a TX exclusive lock, but the session is not blocked currently
156 TM 73470 0 3 0 0
156 TX 589825 945 6 0 0 156 also has a TX exclusive lock, but the session is not blocked currently
LEO1 @ LEO1> select object_name from dba_objects where object_id = 73470; Check that the normal tm tx lock exists on the p1 table, and the session of the other party is not blocked.
OBJECT_NAME
--------------------------------------------------------------------------------
P1
LEO1 @ LEO1> insert into leo1.p1 values (20); at this time, I inserted 20 more on the 138 session and found that the hang cannot move forward. Why? Let's take a look at the v $ lock view.
LEO1 @ LEO1> select sid, type, id1, id2, lmode, request, block from v $ lock where type in ('TT', 'tx ') order by 1, 2;
Sid ty ID1 ID2 LMODE REQUEST BLOCK
--------------------------------------------------------------
138 TM 73470 0 3 0 0
138 TX 589825 945 0 4 0 at this time, the 138 session will continue to be inserted, but this insert action is successful without blocking, but because the 138 | 156 session has the same modification Value
138 TX 327713 1124 6 0 0 is blocked because it violates the primary key constraint. It actually blocks the same modification value. Therefore, Level 4 locks are applied instead of Level 6 locks.
156 TM 73470 0 3 0 0
The 156 TX 589825 945 6 0 1 156 session is blocking the 138 session at this time, because the transaction of the 156 session is still in the pending state.

LEO2 @ LEO1> insert into leo1.p1 values (10); I also inserted 10 in the 156 session, then the deadlock effect will come out.
LEO1 @ LEO1> select sid, type, id1, id2, lmode, request, block from v $ lock where type in ('TT', 'tx ') order by 1, 2;
Sid ty ID1 ID2 LMODE REQUEST BLOCK
--------------------------------------------------------------
138 TM 73470 0 3 0 0
The 138 TX 327713 1124 6 0 1 138 session is blocking the 156 session at this time, because the transaction of the 138 session is still in the pending state.
156 TM 73470 0 3 0 0
156 TX 327713 1124 0 4 0 is actually blocking the same modification value, 156 session is applying for Level 4 lock
156 TX 589825 945 6 0 0
LEO1 @ LEO1> insert into leo1.p1 values (20); let's take a look at the 138 session error. Oracle automatically detects deadlocks and forcibly blocks the release.
Insert into leo1.p1 values (20)
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource detected deadlock when waiting for resources-> release of) Please note that, just release the first lock, but the second lock is still waiting, so we need to release it manually.

Summary: we have discussed several lock mechanisms above. The idea we advocate is to first think about why the lock will happen. What are the functions of the lock if it doesn't work, this kind of heuristic thinking can make us remember deeply.
LOCK: exclusively occupies business resources to ensure read consistency and maintain transaction integrity
LOCK purpose: no locks are available without concurrency, and no locks are generated when a user operates the database.


Leonarding
2012.11.28
Tianjin & winter
Sharing Technology ~ Achieving dreams
Blog: www.leonarding.com
 

This article is from the "leonarding Blog" Blog, please be sure to keep this source http://leonarding.blog.51cto.com/6045525/1073554