db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Knut Anders Hatlen <knut.hat...@oracle.com>
Subject Re: Reg Compress Tables Locking issue
Date Fri, 11 Feb 2011 12:05:26 GMT
vipinsudhakar <sumitha.arjunan@lntinfotech.com> 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

Mime
View raw message