Hello all,I am trying to debug a lock time out issue. After turning on debug as advised in the FAQ[1], I am trying to make sense of the lock table dump that derby wrote to the log file when the lock timed out occurred.
The test case involves two threads one inserting to Database, while other thread reads from the same database.
The isolation level is default which I think is Read Committed[2].Now what i do not understand is that the read query (XID 8520) is waiting to obtain a Shared (S) lock on a Row that the insert thread (XID 8519) has obtained exclusive lock for. How could this be possible in a Read committed mode ?
Also the same test case passes when run using Oracle database. Enclosed with the email is the lock table in question. Copy pasting here the two XIDs in question : $ cat lockTableDump.txt | grep "(3,259)" 8520 |ROW |S |0 |(3,259) 8519 |ROW |X |1 |(3,259) The select query of XID 8520 is a sub query.The replication scenario currently runs on our application database, and i do not have a unit test case. But can someone please advice on what situations in a Read Committed isolation level this kind of locking may occur.
The Derby version is the latest 10.2. Thank you, -Prashant [1] : http://db.apache.org/derby/faq.html#debug_lock_timeout [2] : http://db.apache.org/derby/docs/10.2/devguide/cdevconcepts15366.html
ERROR 40XL2: A lock could not be obtained within the time requested. The
lockTable dump is:
2007-02-22 06:03:42.890 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 ***
8520 |ROW |S |0 |(3,259)
|WAIT |T
|NULL
|DEKOH_PORTAL_DIO_TABLE |
*** The above row is the victim ***
8519 |ROW |X |1 |(8,61)
|GRANT|T
|NULL
|DEKOH_PHOTO_PHOTO_TABLE |
8519 |ROW |X |1 |(8,60)
|GRANT|T
|NULL
|DEKOH_PHOTO_PHOTO_TABLE |
8520 |ROW |S |1 |(5,36)
|GRANT|T
|NULL
|DEKOH_PORTAL_COLLECTION_DIO_TABLE |
8519 |ROW |X |1 |(6,57)
|GRANT|T
|NULL
|DEKOH_PORTAL_COLLECTION_DIO_TABLE |
8519 |TABLE |IX |27 |Tablelock
|GRANT|T
|NULL
|DEKOH_PORTAL_DIO_TABLE |
8520 |TABLE |IS |4 |Tablelock
|GRANT|T
|NULL
|DEKOH_PORTAL_DIO_TABLE |
8519 |TABLE |IS |1 |Tablelock
|GRANT|T
|NULL
|DEKOH_PORTAL_DIO_TABLE |
8519 |ROW |X |1 |(6,59)
|GRANT|T
|NULL
|DEKOH_PORTAL_COLLECTION_DIO_TABLE |
8519 |ROW |X |1 |(6,58)
|GRANT|T
|NULL
|DEKOH_PORTAL_COLLECTION_DIO_TABLE |
8519 |ROW |X |1 |(6,61)
|GRANT|T
|NULL
|DEKOH_PORTAL_COLLECTION_DIO_TABLE |
8519 |ROW |X |1 |(6,60)
|GRANT|T
|NULL
|DEKOH_PORTAL_COLLECTION_DIO_TABLE |
8519 |ROW |X |1 |(8,68)
|GRANT|T
|NULL
|DEKOH_PHOTO_PHOTO_TABLE |
8519 |ROW |X |1 |(6,63)
|GRANT|T
|NULL
|DEKOH_PORTAL_COLLECTION_DIO_TABLE |
8519 |ROW |X |1 |(8,67)
|GRANT|T
|NULL
|DEKOH_PHOTO_PHOTO_TABLE |
8519 |ROW |X |1 |(6,62)
|GRANT|T
|NULL
|DEKOH_PORTAL_COLLECTION_DIO_TABLE |
8519 |ROW |X |1 |(8,66)
|GRANT|T
|NULL
|DEKOH_PHOTO_PHOTO_TABLE |
8519 |ROW |X |1 |(8,65)
|GRANT|T
|NULL
|DEKOH_PHOTO_PHOTO_TABLE |
8519 |ROW |X |1 |(8,64)
|GRANT|T
|NULL
|DEKOH_PHOTO_PHOTO_TABLE |
8520 |ROW |S |1 |(5,1)
|GRANT|T
|SQL070222113209030
|DEKOH_PORTAL_COLLECTION_DIO_TABLE |
8521 |ROW |X |0 |(5,1)
|WAIT |T
|SQL070222113209030
|DEKOH_PORTAL_COLLECTION_DIO_TABLE |
8519 |TABLE |IX |18 |Tablelock
|GRANT|T
|NULL
|DEKOH_PORTAL_COLLECTION_DIO_TABLE |
8520 |TABLE |IS |1 |Tablelock
|GRANT|T
|NULL
|DEKOH_PORTAL_COLLECTION_DIO_TABLE |
8520 |ROW |S |1 |(3,92)
|GRANT|T
|NULL
|DEKOH_PORTAL_DIO_TABLE |
8520 |ROW |S |2 |(3,91)
|GRANT|T
|NULL
|DEKOH_PORTAL_DIO_TABLE |
8519 |TABLE |IX |36 |Tablelock
|GRANT|T
|NULL
|DEKOH_PHOTO_PHOTO_TABLE |
8519 |ROW |X |1 |(6,65)
|GRANT|T
|NULL
|DEKOH_PORTAL_COLLECTION_DIO_TABLE |
8519 |ROW |X |1 |(6,64)
|GRANT|T
|NULL
|DEKOH_PORTAL_COLLECTION_DIO_TABLE |
8519 |ROW |X |1 |(3,264)
|GRANT|T
|NULL
|DEKOH_PORTAL_DIO_TABLE |
8519 |ROW |X |1 |(3,265)
|GRANT|T
|NULL
|DEKOH_PORTAL_DIO_TABLE |
8519 |ROW |X |1 |(3,266)
|GRANT|T
|NULL
|DEKOH_PORTAL_DIO_TABLE |
8519 |ROW |X |1 |(3,267)
|GRANT|T
|NULL
|DEKOH_PORTAL_DIO_TABLE |
8519 |ROW |S |1 |(3,267)
|GRANT|T
|NULL
|DEKOH_PORTAL_DIO_TABLE |
8519 |ROW |X |1 |(3,260)
|GRANT|T
|NULL
|DEKOH_PORTAL_DIO_TABLE |
8519 |ROW |X |1 |(3,261)
|GRANT|T
|NULL
|DEKOH_PORTAL_DIO_TABLE |
8519 |ROW |X |1 |(3,262)
|GRANT|T
|NULL
|DEKOH_PORTAL_DIO_TABLE |
8519 |ROW |X |1 |(3,263)
|GRANT|T
|NULL
|DEKOH_PORTAL_DIO_TABLE |
8519 |ROW |X |1 |(3,259)
|GRANT|T
|NULL
|DEKOH_PORTAL_DIO_TABLE |
8519 |ROW |S |1 |(5,1)
|GRANT|T
|SQL070222113207850
|DEKOH_PORTAL_DIO_TABLE |
8520 |ROW |S |3 |(4,1)
|GRANT|T
|SQL070222113207850
|DEKOH_PORTAL_DIO_TABLE |
8519 |ROW |X |1 |(8,63)
|GRANT|T
|NULL
|DEKOH_PHOTO_PHOTO_TABLE |
8519 |ROW |X |1 |(8,62)
|GRANT|T
|NULL
|DEKOH_PHOTO_PHOTO_TABLE |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
