db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From David Van Couvering <da...@vancouvering.com>
Subject Re: Deadlock XID with no SQL?
Date Thu, 23 Sep 2010 15:59:36 GMT
I suspected as much, but the "blank" XID threw me.

I think I may have found the cause, but now I can't reproduce now that I've
restarted the server.  But I'll put the change in, and also add the backup
logic of retrying a deadlock.

Can I reliably catch and retry on any SQLException that is a
SQLTransientException?

Thanks,

David

On Thu, Sep 23, 2010 at 4:22 AM, Knut Anders Hatlen
<knut.hatlen@oracle.com>wrote:

> David Van Couvering <david@vancouvering.com> writes:
>
> > I am getting the following deadlock?  One of the locks does not have
> > a user or SQL associated with it.  What should I be looking for when
> > a lock is described in this way?
> >
> > Lock : ROW, FILTERS_FOR_DELIVERY, (3,1960)
> >   Waiting XID : {271243, X} , VONTU, DELETE FROM FILTERS_FOR_DELIVERY
> > WHERE ITEMSET_ID = ? AND FILTER_INDEX = ? AND SERVER_ID = ?
> >   Granted XID : {271250, X}
> > Lock : ROW, FILTERS_FOR_DELIVERY, (3,1990)
> >   Waiting XID : {271250, X} , VONTU, DELETE FROM FILTERS_FOR_DELIVERY
> > WHERE ITEMSET_ID = ? AND FILTER_INDEX = ? AND SERVER_ID = ?
> >   Granted XID : {271243, X}
> > . The selected victim is XID : 271243.
>
> Hi David,
>
> The human-readable version of the above is something like this:
>
> There are two transactions involved in the deadlock, both of which are
> attempting to execute the following statement:
>
>    DELETE FROM FILTERS_FOR_DELIVERY
>           WHERE ITEMSET_ID = ? AND FILTER_INDEX = ? AND SERVER_ID = ?
>
> Transaction 271243 is waiting for an exclusive lock on row (3,1960) in
> table FILTERS_FOR_DELIVERY. That row is already locked exclusively by
> transaction 271250.
>
> Transaction 271250 is waiting for an exclusive lock on row (3,1990) in
> table FILTERS_FOR_DELIVERY. That row is already locked exclusively by
> transaction 271243.
>
> I would have tried to find out if the transactions in question were
> performing other INSERT/SELECT/DELETE operations on FILTERS_FOR_DELIVERY
> before the DELETE that deadlocked. If code inspection doesn't help, the
> property derby.language.logStatementText may come handy. When you know
> where the locks come from, you have (at least) three options:
>
> 1) Ensure that the transactions update the rows in the same order to
> avoid the deadlock
>
> 2) Commit more frequently to prevent transactions from holding on to the
> exclusive locks (if the application logic allows the transaction to be
> split up, that is)
>
> 3) Add a try/catch around the transaction, and roll back and retry the
> entire transaction if a deadlock is encountered
>
> --
> Knut Anders
>



-- 
David W. Van Couvering

http://www.linkedin.com/in/davidvc
http://davidvancouvering.blogspot.com
http://twitter.com/dcouvering

Mime
View raw message