Implicit Savepoint when deadlock detection occurs.

The following example describes how Oracle
sets an implicit Savepoint after each UPDATE.

For this example, when a deadlock error occurs, ORA-00060, not all
DML statements in the transaction will therefore be rolled back,
but only the single statement within the session in which the deadlock was
detected.

The following illustrates the point;

Create a test table and data first;

create table a (x number);

insert all
  into a (x) values (1)
  into a (x) values (2)
select 1
from dual;

commit;

The data would look as follows;

SQL> select * from a;

         X
----------
         1
         2
        
Now issue a series of statements in two sessions to generate a deadlock error;

Time DML Statement                       Notes
==== =============                       =====          
     Session 1         Session 2
     =========         =========

t0   update a
     set    x = 3
     where  x = 1;
--------------------------------------------------------------------
t1                     update a
                       set    x = 4
                       where  x = 2;
--------------------------------------------------------------------
t2   update a                        At this point Session 1
     set    x = 6                    hangs, because Session 2
     where  x = 2;                   has issued an UPDATE on
                                     the same row at point t1.
--------------------------------------------------------------------
t3                     update a      At this point Session 2
                       set    x = 5  hangs as well.                        where  x = 1; Session 1 is now waiting
                                     for a row that Session 2 
                                     has locked while Session 2 
                                     is waiting for a row that 
                                     Session 1 has locked.
--------------------------------------------------------------------
t4  update                           At this point Oracle will
    a set   x = 6                    will detect a deadly
    where   x = 2;                   embrace (deadlock) due to
                                     a perpetual wait for
    update a set                     shareable resources as
        *                            described at point T3.
    ERROR at line 1:
    ORA-00060:
    deadlock detected
    while waiting for
    resource
--------------------------------------------------------------------
t5  commit;                          The waiting in Session 2
                                     ceases and the UPDATE
                                     shows as being
                                     successfully completed.
--------------------------------------------------------------------
t6  SQL> select *                    At this point the data
      2  from a;                     indicates that 1 was
                                     successfully updated to 3,
             X                       but 2 was not.
             -
             3
             2                                 
            
The reason that one of the updates appeared to have worked, when viewing
the data at point T6, is that Oracle created an implicit Savepoint at point
T2, so when the error was encountered at T5, the entire transaction was not
rolled back, but only the single UPDATE in which the deadlock was detected.

Session 2 would have continued to hang (wait) at point T4, even though a
partial rollback occurred to an implicit savepoint due to the deadlock detection, since the row, updated at T3, was locked at T0.

The UPDATE in session 2 was successful once a COMMIT was issued, at T5,
in session 1 however.

Use this SQL to view the number of deadlocks that have occurred within the database;

SQL> select name, value
  2  from v$sysstat
  3  where name = 'enqueue deadlocks';

NAME                                         VALUE
--------------------------------------- ----------
enqueue deadlocks                                3

To avoid deadlocks, always lock rows in the same order,

For example, for the above set of transactions, had the order
of UPDATES occurred as follows;


t0   update a
     set    x = 3
     where  x = 1;
------------------------------------
t1                     update a
                       set    x = 5
                       where  x = 1;
------------------------------------
t2   update a                      
     set    x = 6                  
     where  x = 2;                 
                                   
------------------------------------
t3                     update a    
                       set    x = 4
                       where  x = 2;

Then a deadlock error would not have occurred.