The oldest posts, are written in Italian. If you are interested and you want read the post in English, please use Google Translator. You can find it on the right side. If the translation is wrong, please email me: I'll try to translate for you.

lunedì, agosto 21, 2017

Deadlock: an example

Some days ago, I encountered an "ORA-00060: Deadlock Detected" exception. So I would like to summarize what I found. In order to do this, I simulated this exception.

First of all, I have following table

Figure 01 - Table NAMES

At this point, I started two sessions,: 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

Now, without committing the previous operation, the red guy (session 01) update the row with ID=10

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. After, the green guy, update the row with ID=1

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

So, as you can see session 01 and session 02, are locking each other. Because of this stalemate, Oracle resolves it for everyone. It rollback the last statement of who (the session 01, in this case) started the deadlock.

Picture 06 - Session 01 will rollback the last statement

and just the last statement (the "UPDATE") will be rollbacked, not the entire transaction (I mean the "SELECT FOR UPDATE, here). In fact, the session 02, is still waiting. This means that the session 01 is still locking the row with ID=1 row.

The session 01, the one that started the deadlock, is the victim.

Picture 07 - Extract of the trace file (1)

Picture 08 - Extract of the trace file (2)

What happens inside the db after rollback due to deadlock? I check V$SESSION and V$TRANSACTION, In order to see the active sessions. Also I check the V$SQL tho check the current statement.

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

Nessun commento: