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.

Identifying lock modes

The SQL in the post "Identify blocking session for an UPDATE",
bluefrog-oracle.blogspot.com/2011/11/identify-blocking-session-for-update.html
highlighted how to identify which sessions were blocking and which were waiting.

The sample output for the SQL in the above post would be something like;

BLOCKING_STATUS
-------------------------------------------------------------------
USER1@XX\HOST ( SID=214 )  is blocking USER1@XX\HOST ( SID=26 )

The scenario, illustrated with the above output, is that a session, connected as USER1, is blocking a session, also connected as USER1.

The SQL below shows more detail in relation to the types of
locks and modes that are causing blocking for each session.

The script is meant for running on a RAC server.

col User_Session  format a10
col object_name   format a40
col sid           format 99999999
col type          format a4
col serial#       format 999999999
col held          format 9999
col request       format 99999999
col Inst_ID       format 99999
col machine       format a30

set linesize 400
set pagesize 30000

select l.*, s.machine, s.osuser
from
    (
    select  /*+ all_rows */
            l.Inst_ID
           ,s.sid           SID
           ,s.username      User_Session
           ,s.serial#       Serial#
           ,l.type          type
           ,' '             object_name
           ,lmode           held
           ,request         request
    from
            gv$lock          l
           ,gv$session       s
           ,gv$process       p
    where
            s.sid           = l.sid
    and     s.username      <> ' '
    and     s.paddr         = p.addr
    and     l.type          <> 'TM'
    and    (l.type <> 'TX' or l.type = 'TX' and l.lmode <> 6)
    union
    select  /*+ all_rows */
            l.Inst_ID
           ,s.sid           SID
           ,s.Username      User_Session
           ,s.serial#       Serial#
           ,l.type          type
           ,object_name     object_name
           ,lmode           held
           ,request         request
    from
            gv$lock          l
           ,gv$session       s
           ,gv$process       p
           ,sys.dba_objects o
    where
            s.sid           = l.sid
    and     o.object_id     = l.id1
    and     l.type          = 'TM'
    and     s.username      <> ' '
    and     s.paddr         = p.addr
    union
    select  /*+ all_rows */
            l.Inst_ID
           ,s.sid SID
           ,s.Username      User_Session
           ,s.serial#       Serial#
           ,l.type          type
           ,'(Rollback='||rtrim(r.name)||')' object_name
           ,lmode           held
           ,request         request
    from
            gv$lock          l
           ,gv$session       s
           ,gv$process       p
           ,v$rollname       r
    where
            s.sid           = l.sid
    and     l.type          = 'TX'
    and     l.lmode         = 6
    and     trunc(l.id1/65536) = r.usn
    and     s.username      <> ' '
    and     s.paddr         = p.addr
   )          l
  ,v$session  s
where
          s.sid         = l.sid
order by l.Object_Name, l.Held
/

There are only two types of general locks, shared and exclusive.

A many to one relationship can exist between several shared locks and
a single resource, whereas only a one to one scenario can exist
between an exclusive lock and a single resource.

Oracle manages locks on resources through enqueues.
Enqueues as shared memory structures that serialize access to database
resources.

A database resource, in relation to data locks, can be an object structure
or a transaction.

Oracle has two types of data locks:

TX = Row Locks - Finest grain of locking possible.
Oracle stores locking information of a locked row
within the data block the row resides in.

A row lock automatically generates a table lock (TM), to prevent
DDL from occurring on the table while the transaction is in progress.

TM = Table locks - Acquired by a transaction (INSERT,UPDATE,DELETE,MERGE,LOCK TABLE or SELECT FOR UPDATE).

Table locks can be one of six modes, namely;

0 = none
1 = null 
2 = row-S (RS or SS)     
Row share lock or sub-share lock. 
Transaction holds locked rows and an update is pending.
Acquired when issuing either;
  SELECT FOR UPDATE;
  LOCK TABLE IN ROW SHARE mode;

Least restrictive, since other transactions can query, insert update or delete concurrently on the same table on rows other than the ones locked with SELECT for UPDATE.
Other transactions can therefore obtain RS, RX or SRX locks.
Prevents an exlusive table (X) lock however.

3 = row-X (RX or SX)
Row exclusive lock. Transaction holding locked rows has completed the update(s) to the table.
Acquired automatically with one of the following four statements;
  INSERT;
  UPDATE;
  DELETE;
  LOCK TABLE IN ROW EXCLUSIVE MODE;

More restrictive than row share lock, since other transactions cannot concurrently
lock the table in share mode (S), share exclusive mode (SRX) using one of the following statements;
  LOCK TABLE IN SHARE MODE
  LOCK TABLE IN EXCLUSIVE MODE
in addition to not permitting any other types of exclusive (X) locks.
RX is the default locking behavior for Oracle.

4 = share (S)
Share Table. Acquired when explicitly issuing the LOCK table in SHARE MODE statement.
Only allows other transactions to query the table in share mode (S).
Prevents other transactions from modifying the same table in SRX, RX and X modes.
Transactions can hold share (S) table locks concurrently, but a SELECT for UPDATE update can only occur if there are no other transactions in share mode (S) as well.

5 = S/Row-X (SRX or SSX) 
Share Row Exclusive. Acquired explicitly when issuing a LOCK TABLE in SHARE ROW EXCLUSIVE mode. Only a single transaction at a time can acquire a share row exclusive lock.
Other transactions can however query the table, but they cannot update the table.
Prevents all types of locks from other transactions. 
Permits SELECT FOR UPDATE locks within the same transaction after issuing the LOCK TABLE statement.

6 = Exclusive (X)        
Most restrictive. Prevents any DML on the table. Acquired with
LOCK TABLE IN EXCLUSIVE MODE. Only a single transaction can obtain an exclusive lock at a time. Querying can still occur though.


This is a sample output from the above query and one can clearly

INST_ID  SID USER_SESSI SERIAL# TYPE OBJECT_NAME HELD REQUEST
------- ---- ---------- ------- ---- ----------- ---- -------
      1  222 USER2        34254 TX                  0       6
      1  221 USER1         8366 TM   TEST_LOCK      3       0


The REQUEST column will indicate the mode of lock on a Resource
that is sought, which is currently held by another
session (indicated by HELD).

A Resource can be a table definition, a transaction or any structure
that is shareable. The allowable range of values will be the same as
per the HELD column.

Oracle uses enqueues as a locking mechanisms for queuing access to
resources.

Lock conversion occurs when a lock with a lower restrictiveness is converted
to one of higher restrictiveness. For example, if a SELECT for UPDATE
is issued, the initial locks on the table would be row share (RS).

When eventually the UPDATE WHERE CURRENT OF occurs, the RS lock will be converted
to a RX lock.