vipinsudhakar <[email protected]> writes: > Dear All, > > I have included the SYSCS_UTIL.SYSCS_COMPRESS_TABLE method call on a daily > basis to improve the performance of derby by means of compressing tables. > But i got an error while invoking this method on a particular table, which > was using by some other query execution which was continiuously running for > 3-4 days. So this method cant obtain the lock on the particular table. I got > the below exception. > "Execution of Compress Tables failed. Reason: The exception > 'java.sql.SQLException: A lock could not be obtained due to a deadlock, > cycle of locks and waiters is: Lock : ROW, SYSCONGLOMERATES, (7,10)" > But at the same time i cant stop the other activity as it was a continuously > running. > Please let me know how can i handle this situation?
You could maybe get around this particular deadlock by using SYSCS_INPLACE_COMPRESS_TABLE[1] instead, since that procedure shouldn't need to update SYSCONGLOMERATES, I think. It doesn't do everything SYSCS_COMPRESS_TABLE does, though. In general, if you have code that can run into lock conflicts and you cannot enforce an ordering that completely avoids the risk of deadlocks, the way to handle it would be to detect lock timeouts and deadlocks by checking the SQL state of the SQLException (lock timeout is 40XL1 and deadlock is 40001) and re-execute the transaction on such errors. Alternatively check if the SQLException is an instance of SQLTransactionRollbackException. [1] http://db.apache.org/derby/docs/10.7/ref/rrefproceduresinplacecompress.html -- Knut Anders
