db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Oystein Grovlen - Sun Norway <Oystein.Grov...@Sun.COM>
Subject Re: why are table locks being made?
Date Fri, 11 Jan 2008 07:44:05 GMT
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

Mime
View raw message