You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by David Van Couvering <da...@vancouvering.com> on 2010/09/22 23:21:41 UTC
Deadlock XID with no SQL?
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.
Thanks,
David
--
David W. Van Couvering
http://www.linkedin.com/in/davidvc
http://davidvancouvering.blogspot.com
http://twitter.com/dcouvering
Re: Deadlock XID with no SQL?
Posted by Knut Anders Hatlen <kn...@oracle.com>.
David Van Couvering <da...@vancouvering.com> writes:
> 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?
That's the idea, at least. There's no guarantee that the retry will
succeed, but it shouldn't cause any harm. I usually do a rollback() on
the connection before I retry so that I know exactly how much work was
rolled back, since I'm not sure if all the SQLTransientExceptions have
the same severity level.
Here's a code example that makes two more attempts before it gives up if
a transaction fails with a transient error:
http://src.opensolaris.org/source/xref/opengrok/trunk/src/org/opensolaris/opengrok/history/JDBCHistoryCache.java#686
In case you need some inspiration... :)
--
Knut Anders
Re: Deadlock XID with no SQL?
Posted by David Van Couvering <da...@vancouvering.com>.
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
<kn...@oracle.com>wrote:
> David Van Couvering <da...@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
Re: Deadlock XID with no SQL?
Posted by Knut Anders Hatlen <kn...@oracle.com>.
David Van Couvering <da...@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