Deadlock: an example

Some days ago, I encountered an "ORA-00060: Deadlock Detected" exception.

In this post I would like to summarize what I found. In order to do this, I simulated a typical example using two session (green and red) and on table.

The table is "NAMES" and ti contains following rows.

Figure 01 - Table NAMES

Session 01, in red, session 02 in green, both make an update on two different rows

Picture 02 - Session 01, update the row with ID=1

Picture 03 - Session 02, update the row with ID=10

Without committing, the red guy (session 01) update the row with ID=10 (already updated by session 02)

Picture 04 - Session 01, update ID=10 and wait

Because the row with ID=10 is already updated by the green guy (session 02 didn't commit), the session 01 has to wait. At the same way, the green guy, update the row with ID=1 (previosly updated by session 01)

Picture 05 - Session 02, update ID=1 and wait

As you can see session 01 and session 02, are locking each other. Because of this "strange" situation, the deadlock, Oracle resolves it for everyone. Oracle rollback the statement who started the deadlock.

Pay attention: the session who started the deadlock (session 01/red) isn't the one run the last command (session 02/green): it is the first one locked the row owned by the other session.

Picture 06 - Session 01 will rollback the last statement

Also, Oracle will be rollback, not the entire transaction (I mean the "SELECT FOR UPDATE, here) but only the statement that generate the deadlock (I mean the "UPDATE"). In fact, the session 01, is still waiting. This means that the session 01 is still locking the row with ID=1 row.

This is what you can see in the alert log file.

Picture 07 - Extract of the trace file (1)

Picture 08 - Extract of the trace file (2)

Give a deep view inside the database: what happens after the rollback due to deadlock?

Following query help us to understand what is uppening:.

Picture 09 - Query the data dictionary

Joining V$SESSION with V$SQL, you can see that the session 01 is just running the UPDATE with ID=1 (actually he run the "SELECT FOR UPDATE", that is, his first statement).

Picture 10 - V$LOCK

Interesting is querying the V$LOCK. Based on information of orafaq, I had following informations.

Both sessions make an exclusive lock (IMODE=6), but the green guy also requested an exclusive lock (REQUEST=6). As you can see, the REQUEST=6 of the session 02 is made on the same resource of the IMODE=6 made by session 01: ID1=262284 and ID2=3375 These values are the same in V$SESSION (P2 and P3).

In V$LOCK_TYPE view you can see the meaning of the LOCK TYPE.

Picture 11 - Explaining the lock

Anyway the Doc ID 29787.1 on MOS, can give you more information

Picture 12 - Doc ID 29787.1

Pictures 07 and 08, show us an extract from a trace file of the Oracle 12c. The output of the previous version is different

Picture 13 - The alert.log output in 11c is different than 12c

In the alert log, you can read explicitally the "Deadlock detection". In order to understand the old output (Picture 12), you can read the Doc ID 62365.1 on MOS.

Picture 14 - Extract from Doc ID 62365.1

Update (2) - 2019/04/23
Following the statement I used to create and populate the table in this post.

create table names (id number, name varchar2(16), surname varchar2(16));
insert into names values (1, 'James', 'Smith');
insert into names values (2, 'Richard', 'Tylor');
insert into names values (3, 'Mark', 'Edwards');
insert into names values (4, 'Brian', 'Turner');
insert into names values (5, 'Laura', 'Harrison');
insert into names values (6, 'Sandra', 'Ward');
insert into names values (7, 'Jennifer', 'Clark');
insert into names values (8, 'Ruth', 'Cox');
insert into names values (9, 'Helen', 'Palmer');
insert into names values (10, 'Diana', 'Reynolds');

Update (1) - 2019/04/23

I have reviewed part of this post, trying to write better English.