Derby is used in my application in the in-memory only mode. For a long time Derby’s lock logic caused no worries, but recently some use cases failed with lock timeouts. Thus I’m looking for guidance on best practices for handling locks in Derby. A use-case which may cause timeouts to obtain a lock: one thread is executing an SQL statement which accesses two (or more) in-memory tables. Those two tables are being modified by other threads at random times. So, situations in which the SQL is executed for a long time and the other threads are frequently updating the tables may cause lock timeouts.
Besides best practices to avoid timeouts and deadlocks, I would like to ask the following questions:
1) What’s the default length of lock timeouts?
2) Does my app need another layer of synchronization mechanism/locks to avoid attempts to update in-memory tables or execute SQLs against them?
3) Can my application utilize Derby’s locks through some API – to query their state or to use them in making a decision of whether to batch updates or to execute them, to wait or execute the SQLs?
Your help would be greatly appreciated,