Forum OpenACS Development: Oracle Deadlock error

Collapse
1: 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
===================================================


    
  

  

        




    

  

    Collapse
  

  
      

        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.
    
  

  

        




    

  

    Collapse
  

  
      

        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.