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: PROD\pc 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;