Processing analysis of blocking when Oracle inserts the same row of records at the same time

(4th number Lock analysis of the unique key constraints)

A previous customer suggested that when the insert was hang to live, the problem is likely to be locked. For such a question I usually think of a few common views: v$lock/v$transaction/v$session/v$sql, careful observation can find out where the problem lies.

Well, now we go to analyze the above phenomenon, one of the conventional means to analyze the problem is to simulate the recurrence of failure, I will try to simulate:

1. The session number for which the current operation is detected is 125

Sql> select Sid from V$mystat where Rownum=1;

Sid

----------

125

2. Create a table and insert data

Sql> CREATE TABLE T_gyj (ID int primary key,name VARCHAR2 (10));

Table created.

sql> INSERT INTO T_GYJ values (1, ' gyj1 ');

1 row created.

Note that this is not a commit (commit).

3. Open another session, the session number is 17

Sql> select Sid from V$mystat where Rownum=1;

Sid

----------

17

4. Inserting the same record on session 17th was blocked.

sql> INSERT INTO T_GYJ values (1, ' gyj1 ');

Here is the above mentioned to insert the same line of records, creating a blocking phenomenon, the phenomenon of how do we judge the analysis?

5. Start analysis, just mention several views v$lock,v$transaction,v$session,v$sql, these views are required by the DBA.

In order to facilitate the query, I am in the above operation of the session number is deliberately shown to show the number 125th and 17th session.

It's blocked, and we usually think of it as a lock.

OK, I'll open another session:

Sql> select Sid from V$mystat where Rownum=1;

Sid

----------

19

Sql> Set Linesize 1000

Sql> SELECT * from V$lock where SID in (125,17);

ADDR kaddr SID TY ID1 ID2 lmode REQUEST CTIME block

---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------

00000003a445f998 00000003a445f9f0-AE 100 0 4 0 883 0

00000003a44601d0 00000003a4460228 AE 100 0 4 0 595 0

00000003a44602a0 00000003a44602f8 TX 327681 1022 0 4 474 0

fffffd7ffc9f2040 fffffd7ffc9f20a0 TM 74868 0 3 0 474 0

fffffd7ffc9f2040 fffffd7ffc9f20a0 TM 74868 0 3 0 565 0

00000003a2cff908 00000003a2cff980 TX 458765 764 6 0 474 0

00000003a2d3e928 00000003a2d3e9a0-TX 327681 1022 6 0 565 1