Reg Compress Tables Locking issue

[vipinsudhakar <su...@lntinfotech.com>]

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?

Thanks in advance.
-- 
View this message in context: http://old.nabble.com/Reg-Compress-Tables-Locking-issue-tp30890556p30890556.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.

Re: Reg Compress Tables Locking issue

[Knut Anders Hatlen <kn...@oracle.com>]

vipinsudhakar <su...@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