Have you tried see if forcing the use of an index through optimizer
overrides can improve the situation? I have never tried this myself, so
no guarantees.
--
Øystein
Geoff hendrey wrote:
OK, I understand why the deadlock happens, and it is exactly the scenario I
outlined in my previous email. Basically the deadlock is happening due to table
locks. The foreign key relationships to other tables are basically causing this
massive table-lock propogation into all the tables that are
foreign-key-connected through an ON DELETE SET NULL. I got a better deadlock
dump by adding these to the derby.properties:
derby.locks.monitor=true
derby.locks.deadlockTrace=true
derby.locks.deadlockTimeout=1
derby.locks.waitTimeout=1
here is the full deadlock dump. You can see all the other tables getting
table-locked. Therefore, the only solution I can see is to synchronize deletes
at the application layer. I did this, and naturally no more deadlocks. But I
still feel like I should not have to do that kind of application-layer
synchronization. Anyway, here is the full deadlock dump. Someone who
understands all the flags in the dump might be able to tell if there is a way
to avoid the deadlock through a different configuration of the database or
indexes, or foreign keys.
2008-01-11 04:13:42.624 GMT Thread[btpool0-2,5,main] (XID = 16673), (SESSIONID
= 0), (DATABASE = domains/geoff), (DRDAID = null), Cleanup action starting
2008-01-11 04:13:42.624 GMT Thread[btpool0-2,5,main] (XID = 16673), (SESSIONID = 0),
(DATABASE = domains/geoff), (DRDAID = null), Failed Statement is: DELETE FROM
GEOFF__BLOG__USER WHERE "PK"=385
ERROR 40XL2: A lock could not be obtained within the time requested. The lockTable dump is:
2008-01-11 04:13:42.607 GMT
XID |TYPE |MODE|LOCKCOUNT|LOCKNAME
|STATE|TABLETYPE / LOCKOBJ
|INDEXNAME / CONTAINER_ID / (MODE for LATCH only) |TABLENAME / CONGLOM_ID
|
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
*** The following row is the victim ***
16673 |TABLE |X |0 |Tablelock
|WAIT |T
|NULL |GEOFF__BLOG__USER
|
*** The above row is the victim ***
16673 |TABLE |IX |1 |Tablelock
|GRANT|T
|NULL
|GEOFF__BLOG__USER_REMINDERS |
16673 |TABLE |X |1 |Tablelock
|GRANT|T
|NULL
|GEOFF__BLOG__USER_REMINDERS |
16673 |TABLE |IX |3 |Tablelock
|GRANT|T
|NULL |GEOFF__BLOG__USER
|
16674 |TABLE |IX |2 |Tablelock
|GRANT|T
|NULL |GEOFF__BLOG__USER
|
16673 |TABLE |IX |1 |Tablelock
|GRANT|T
|NULL
|GEOFF__BLOG__USER_CONTACT |
16673 |TABLE |X |1 |Tablelock
|GRANT|T
|NULL
|GEOFF__BLOG__USER_CONTACT |
16673 |TABLE |IX |1 |Tablelock
|GRANT|T
|NULL
|GEOFF__BLOG__USER_NOTES |
16673 |TABLE |X |1 |Tablelock
|GRANT|T
|NULL
|GEOFF__BLOG__USER_NOTES |
16673 |TABLE |IX |1 |Tablelock
|GRANT|T
|NULL |GEOFF__BLOG__USER_PICS
|
16673 |TABLE |X |1 |Tablelock
|GRANT|T
|NULL |GEOFF__BLOG__USER_PICS
|
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2008-01-11 04:13:42.624 GMT Thread[btpool0-3,5,main] (XID = 16674), (SESSIONID
= 3), (DATABASE = domains/geoff), (DRDAID = null), Cleanup action starting
2008-01-11 04:13:42.624 GMT Thread[btpool0-3,5,main] (XID = 16674), (SESSIONID = 3),
(DATABASE = domains/geoff), (DRDAID = null), Failed Statement is: DELETE FROM
GEOFF__BLOG__USER WHERE "PK"=381
ERROR 40XL2: A lock could not be obtained within the time requested. The lockTable dump is:
2008-01-11 04:13:42.607 GMT
XID |TYPE |MODE|LOCKCOUNT|LOCKNAME
|STATE|TABLETYPE / LOCKOBJ
|INDEXNAME / CONTAINER_ID / (MODE for LATCH only) |TABLENAME / CONGLOM_ID
|
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
*** The following row is the victim ***
16674 |TABLE |IX |0 |Tablelock
|WAIT |T
|NULL |GEOFF__BLOG__USER_PICS
|
*** The above row is the victim ***
16673 |TABLE |IX |1 |Tablelock
|GRANT|T
|NULL
|GEOFF__BLOG__USER_REMINDERS |
16673 |TABLE |X |1 |Tablelock
|GRANT|T
|NULL
|GEOFF__BLOG__USER_REMINDERS |
16673 |TABLE |IX |3 |Tablelock
|GRANT|T
|NULL |GEOFF__BLOG__USER
|
16674 |TABLE |IX |2 |Tablelock
|GRANT|T
|NULL |GEOFF__BLOG__USER
|
16673 |TABLE |IX |1 |Tablelock
|GRANT|T
|NULL
|GEOFF__BLOG__USER_CONTACT |
16673 |TABLE |X |1 |Tablelock
|GRANT|T
|NULL
|GEOFF__BLOG__USER_CONTACT |
16673 |TABLE |IX |1 |Tablelock
|GRANT|T
|NULL
|GEOFF__BLOG__USER_NOTES |
16673 |TABLE |X |1 |Tablelock
|GRANT|T
|NULL
|GEOFF__BLOG__USER_NOTES |
16673 |TABLE |IX |1 |Tablelock
|GRANT|T
|NULL |GEOFF__BLOG__USER_PICS
|
16673 |TABLE |X |1 |Tablelock
|GRANT|T
|NULL |GEOFF__BLOG__USER_PICS
|
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Cleanup action completed
Cleanup action completed
