“UPDATE SET ROW” with UNIQUE constraints can cause ORA-00060 deadlock

While developing some software that inserts and updates a lot of records in an Oracle database I used UPDATE SET ROW a number of times:

create table tmp_martin (
  id number(10,0) primary key
, description varchar2(50)
, start_date date
);

procedure update_row(
  p_row in tmp_martin%rowtype
)
is
begin
  update tmp_martin set row = p_row where id = p_row.id;
end update_row;

This code ran fine – until I fired 5 sessions in parallel to get some more serious throughput. About 10% of the UPDATE’s failed due to ORA-00060 Deadlock detected. How is that possible?

The trace-file (check your UDUMP directory, it should be there), showed this:

*** 2013-07-10 09:40:42.251
DEADLOCK DETECTED ( ORA-00060 )

[Transaction Deadlock]

The following deadlock is not an ORACLE error. It is a
deadlock due to user error in the design of an application
or from issuing incorrect ad-hoc SQL. The following
information may aid in determining the deadlock:

Deadlock graph:
                       ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TM-0005bd31-00000000        35      76    SX   SSX       37     118    SX   SSX
TM-0005bd31-00000000        37     118    SX   SSX       35      76    SX   SSX

session 76: DID 0001-0023-000003F4	session 118: DID 0001-0025-00000244 
session 118: DID 0001-0025-00000244	session 76: DID 0001-0023-000003F4 

Rows waited on:
  Session 76: no row
  Session 118: no row

----- Information for the OTHER waiting sessions -----
Session 118:
  sid: 118 ser: 447 audsid: 4251663 user: 87/user
    flags: (0x41) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
    flags2: (0x40009) -/-/INC
  pid: 37 O/S info: user: oracle, term: UNKNOWN, ospid: 515
    image: oracle@host
  client details:
    O/S info: user: user, term: pc, ospid: 5100:4560
    machine: PRODpc program: sqlplus.exe
    client info: sid
    application name: package, hash value=3412811928
    action name: convert, hash value=3491084592
  current SQL:
  UPDATE TMP_MARTIN SET "ID" = :B1, "DESCRIPTION" = :B2, "START_DATE" = :B3 WHERE ID = :B1

----- End of information for the OTHER waiting sessions -----

Information for THIS session:

----- Current SQL Statement for this session (sql_id=ga5n48159sxhp) -----
  UPDATE TMP_MARTIN SET "ID" = :B1, "DESCRIPTION" = :B2, "START_DATE" = :B3 WHERE ID = :B1

The trace file indicated that both sessions processed “no row” at the moment, so that was no clue. I was sure that each session had its own discrete set of records, so it was impossible that both sessions tried to update the same set of rows, thus causing the deadlock.

However, the current SQL put me on the right track: it shows that “UPDATE SET ROW” is expanded by Oracle to include all individual columns. It turns out that the primary key (column ID) is also updated. Although it never changes in practice, it is included in the UPDATE statement (causing it to be updated to itself).

To update a primary key, Oracle needs a full table lock to ensure data integrity. And because both transactions can not acquire a full table lock at the same time, a deadlock occurred.

Luckily, the solution is simple: just write out all columns that you are actually updating.

procedure update_row(
  p_row in tmp_martin%rowtype
)
is
begin
  update tmp_martin set
    description = p_row.description
  , start_date = p_row.start_date
  where id = p_row.id;
end update_row;

See also Ask Tom for an explanation of this error.