Deadlock occurs between 3 sessions
Hi,
I have a deadlock problem. I have a procedure that is called from a BEFORE_INSERT trigger. In that procedure, I have 3 INSERT statements into the same table and I lock the table with "lock table ADDR_UNIQUENESS_CHECK in EXCLUSIVE MODE;" before each insert since the table should not be updated during an insert.
When there are 2 sessions, there is no problem. But, when there are 3 or more sessions, the first session gets the EXCLUSIVE LOCK and the others get SHARED LOCK. When the first sessions updates the row and then releases the EXCLUSIVE LOCK, the other sessions can not get the EXCLUSIVE LOCK and deadlock occurs. What can I do to prevent this deadlock? Do I need to use another lock type before INSERTs?
I have a deadlock problem. I have a procedure that is called from a BEFORE_INSERT trigger. In that procedure, I have 3 INSERT statements into the same table and I lock the table with "lock table ADDR_UNIQUENESS_CHECK in EXCLUSIVE MODE;" before each insert since the table should not be updated during an insert.
When there are 2 sessions, there is no problem. But, when there are 3 or more sessions, the first session gets the EXCLUSIVE LOCK and the others get SHARED LOCK. When the first sessions updates the row and then releases the EXCLUSIVE LOCK, the other sessions can not get the EXCLUSIVE LOCK and deadlock occurs. What can I do to prevent this deadlock? Do I need to use another lock type before INSERTs?
0