Hi all, I'm trying to solve an ora-4020 (See ora-4020.ora-code.com) (deadlock) issue; the trace file (sorry if messed) shows:
ksqded1: deadlock detected via did DEADLOCK DETECTED Current SQL statement for this session: Update <TABLE> set <FIELD1>='<VALUE>' where <FIELD2> like '<STRING>%' 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-00040015-0000305b 13 11 X 10 14 X TX-0007000b-0000309e 10 14 X 13 11 X session 11: DID 0001-000D-00000001 session 14: DID 0001-000A-00000001 session 14: DID 0001-000A-00000001 session 11: DID 0001-000D-00000001 Rows waited on: Session 14: obj - rowid = 0000147E - AAABR+AAKAAAAJIAAH Session 11: obj - rowid = 0000147E - AAABR+AAKAAAzEeAAH
The trace file shows clearly that session 11 and 14 are blocking each other.
Note that <STRING> can be very long, but it's almost certain that this is not causing the problem.
Database version is 8.0.5 on Solaris 8 sparc.
So, I'm looking at the piece of source containing the affected code (I'm not the developer neither a skilled one) and I have seen something strange, that is a sql package containing many procedures with insert/update statements and none of these insert/update was followed by a commit; I asked the developer for this matter and she said that a commit would prevent a possible rollback of database transaction.
Now, my questions are: 1. Is it correct an insert/update without a commit into a sql package? If yes, when are the inserted/updated data commited? 2. Would this the possible cause of the deadclock, as the table indexes could be locked by a large number of records inserted/updated? 3. Is this the correct way to get the choice of performing a rollback?
