We faced Oracle deadlock (ORA-00060) problem on live for our Java/J2EE based web application. The trace file is pasted below:
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 TX-00030013-0000CC59-00000000-00000000 106 408 X 76 171 X TX-000F001E-0001681C-00000000-00000000 76 171 X 106 408 X session 408: DID 0001-006A-007D5293 session 171: DID 0001-004C-001C2F48 session 171: DID 0001-004C-001C2F48 session 408: DID 0001-006A-007D5293 Rows waited on: Session 408: obj - rowid = 0002A2AC - AAAqKsAAUAABE3oAAB (dictionary objn - 172716, file - 20, block - 282088, slot - 1) Session 171: obj - rowid = 0002A2AC - AAAqKsAAUAABE2pAAA (dictionary objn - 172716, file - 20, block - 282025, slot - 0) ----- Information for the OTHER waiting sessions ----- Session 171: sid: 171 ser: 62179 audsid: 1760178 user: ******* flags: (0x8000045) USR/- flags_idl: (0x1) BSY/-/-/-/-/- flags2: (0x40009) -/-/INC pid: 76 O/S info: user: ****, term: UNKNOWN, ospid: 48169 image: ************** client details: O/S info: user: ****, term: unknown, ospid: 1234 machine: ********* program: JDBC Thin Client client info: ********************* application name: JDBC, hash value=3519240545 action name: ProcessDummySearchData-UpdateSearchData, hash value=560080304 current SQL: UPDATE TABLE1 SET COL3=:1 ,LASTMODIFIED=SYSDATE WHERE UUID=:2 AND COL2=:3 ----- End of information for the OTHER waiting sessions ----- Information for THIS session: ----- Current SQL Statement for this session (sql_id=635v1mk350vst) ----- update TABLE1 set COL3= :1 where COL4= :2 and COL5 = :3
If I try to apply "proposed fix" from below URL: I fail to understand how creating composite indexes (UUID, COL2) & (COL4, COL5) in TABLE1 can solve this issue? Can anyone throw some light on how to solve this issue? I understand that this particular issue is perhaps due to simultaneous update on same table/row.