icon-arrow icon-check icon-mail icon-phone icon-facebook icon-linkedin icon-youtube icon-twitter icon-cheveron icon-download icon-instagram play close icon-arrow-uturn icon-calendar icon-clock icon-search icon-chevron-process icon-skills icon-knowledge icon-kite icon-education icon-languages icon-tools icon-experience icon-coffee-cup
Werken bij Whitehorses
Blog 12/07/2013

“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
  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

[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:

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

Information for THIS session:

----- Current SQL Statement for this session (sql_id=ga5n48159sxhp) -----

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
  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.

Overzicht blogs

Geen reacties

Geef jouw mening

Reactie plaatsen

Reactie toevoegen

Jouw e-mailadres wordt niet openbaar gemaakt.


  • Geen HTML toegestaan.
  • Regels en alinea's worden automatisch gesplitst.
  • Web- en e-mailadressen worden automatisch naar links omgezet.

Wil je deel uitmaken van een groep gedreven en ambitieuze integratiespecialisten? Stuur ons jouw cv!