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: Deadlock XID with no SQL?
Date Thu, 23 Sep 2010 11:22:01 GMT
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

Mime
View raw message