Forum OpenACS Development: Oracle Deadlock error
Posted by Barry Books on 04/02/02 10:27 PM
I got the following error emailed to me this morning. From what I can tell two people hit a page at the same time ( maybe a double click ) and Oracle deadlocked trying to create an object. Has anyone seen anything like this? I'm running ACS 4.2
DEADLOCK DETECTED Current SQL statement for this session: INSERT INTO ACS_OBJECTS ( OBJECT_ID,OBJECT_TYPE,CONTEXT_ID,CREATION_DATE,CREATION_USER,CREATION_ IP ) VALUES ( :b1,:b2,:b3,:b4,:b5,:b6 ) ----- PL/SQL Call Stack ----- object line object handle number name 4e0c6c64 68 package body IFS1.ACS_OBJECT 4deef8b8 14 package body IFS1.BV_DATASOURCE 4def173c 11 package body IFS1.FOLDER_EVENT 4cb7dcc0 68 package body IFS1.FOLDER_INSTANCE_REL 4df07530 782 package body IFS1.INSPECTION 4c36bb20 27 anonymous block 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-00010021-00000980 40 8 X 24 41 S TX-00070039-00000977 24 41 X 40 8 S session 8: DID 0001-0028-00000002 session 41: DID 0001-0018- 00000002 session 41: DID 0001-0018-00000002 session 8: DID 0001-0028- 00000002 Rows waited on: Session 41: no row Session 8: no row ===================================================
2: Response to Oracle Deadlock error (response to 1)
Posted by Barry Books on 04/10/02 05:24 PM
I'll answer my own question in case anyone else has the same problem. According to Oracle it's possible to deadlock doing inserts on tables with bitmap indexes. The above trace indicates this happend because the Rows waited on are "no row". It turns out acs_objects has a bitmap index on object_type ( I think this has been removed in openacs ). It't not clear to me why object_type should be a bitmap index anyway since there could be many different types. At any rate use bitmap indexes with care.
3: Response to Oracle Deadlock error (response to 1)
Posted by Don Baccus on 04/10/02 11:23 PM
Good detective work - you're right, we removed the bitmapped indexes in OACS 4.5 because it's an Enterprise Edition feature and we want to support Standard Edition, and because it didn't appear that bitmapped indexes were going to be a big win anyway in the 4x datamodel.