I am troubleshooting a derby deadlock and need help. (setup - -Derby 10.7.1 + in-memory DB + Hibernate 3.6.9 + spring 3.0.3)
Inside a transaction, I execute delete on a specific instance of an entity that has a 1-* relationship with another entity. This results in an update SQL that tries to break that relationship. So basically, something like this… Update A.col1=null, A.col2=null from A where A.col3 = ? Even, though col3 has a non-unique index on it, this results in an escalated table lock (verified it by querying the SYSCS_DIAG.LOCK_TABLE). Why a table lock? I verified this behavior on an oracle system and it just creates a row lock. I tried tuning different parameters such as the "derby.locks.escalationThreshold", but it did not help. Further down the logic, I have to start a new nested transaction (for complicated reasons….I will leave out the details) that tries to read a different entity from the same table and it gets blocked. We have also tried upgrading to the latest 10.8 version as well as switched from in-memory to file based DB but it didn't help. Any insight is appreciated… Jasmeet Bhatia
