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 Colin Rosenthal <cs...@statsbiblioteket.dk> on 2005/10/25 11:12:53 UTC
Locked row in table
Hi all,
We have an application which uses derby in auto-commit=false mode.
For some reason a row in one of our tables cannot be updated from
our system. Attempting to do so consistently produces a 40XL1 lock
timeout error. I have shut down the system and gone in with ij and
updated this row "by hand" without problem, but when I restart the
system the row is still not updateable.
Any ideas how to proceed?
--
Colin
Re: Locked row in table
Posted by Suresh Thalamati <su...@gmail.com>.
Colin Rosenthal wrote:
> On Tue, 2005-10-25 at 13:30, Knut Anders Hatlen wrote:
>
>>Colin Rosenthal <cs...@statsbiblioteket.dk> writes:
>>
>>
>>>Hi all,
>>>We have an application which uses derby in auto-commit=false mode.
>>>For some reason a row in one of our tables cannot be updated from
>>>our system. Attempting to do so consistently produces a 40XL1 lock
>>>timeout error. I have shut down the system and gone in with ij and
>>>updated this row "by hand" without problem, but when I restart the
>>>system the row is still not updateable.
>>>
>>>Any ideas how to proceed?
>>
>>The lock timeout error probably means there is a deadlock. This can
>>happen in cases where two transactions access the same resources in a
>>different order, like in
>>
>> T1 accesses X before Y
>> T2 accesses Y before X
>>
>>If T1 locks X and T2 locks Y, both transactions will wait for the
>>other transaction to release its lock.
>>
>>You basically have two options:
>>
>> 1. Make sure that the transactions which cause deadlocks access rows
>> in the same order.
>>
>> 2. Deal with deadlocks in your code, i.e. catch exceptions and
>> restart the transactions.
>
>
> What bothers me about that suggestion is that I would expect a fault in
> the program logic to affect all rows of the table, but it is one
> specific row that is affected.
> A colleague suggests that failing to
> disconnect cleanly when shutting down the application could cause a
> lock to persist when the database is restarted. My problem with that
> idea is that having accessed the database with ij, and exited cleanly
> from ij, I would expect the database to be cleaned up.
No locks are held once the database is shutdown, even if you did not
close the connections. There are some flags in the following FAQ ,
they may help you to figure out; if you are actually hitting a dead
lock / simple lock time out and which statement might be holding the
locks.
http://incubator.apache.org/derby/faq.html#debug_lock_timeout
hope that helps
-suresh
Re: Locked row in table
Posted by Colin Rosenthal <cs...@statsbiblioteket.dk>.
On Tue, 2005-10-25 at 13:30, Knut Anders Hatlen wrote:
> Colin Rosenthal <cs...@statsbiblioteket.dk> writes:
>
> > Hi all,
> > We have an application which uses derby in auto-commit=false mode.
> > For some reason a row in one of our tables cannot be updated from
> > our system. Attempting to do so consistently produces a 40XL1 lock
> > timeout error. I have shut down the system and gone in with ij and
> > updated this row "by hand" without problem, but when I restart the
> > system the row is still not updateable.
> >
> > Any ideas how to proceed?
>
> The lock timeout error probably means there is a deadlock. This can
> happen in cases where two transactions access the same resources in a
> different order, like in
>
> T1 accesses X before Y
> T2 accesses Y before X
>
> If T1 locks X and T2 locks Y, both transactions will wait for the
> other transaction to release its lock.
>
> You basically have two options:
>
> 1. Make sure that the transactions which cause deadlocks access rows
> in the same order.
>
> 2. Deal with deadlocks in your code, i.e. catch exceptions and
> restart the transactions.
What bothers me about that suggestion is that I would expect a fault in
the program logic to affect all rows of the table, but it is one
specific row that is affected. A colleague suggests that failing to
disconnect cleanly when shutting down the application could cause a
lock to persist when the database is restarted. My problem with that
idea is that having accessed the database with ij, and exited cleanly
from ij, I would expect the database to be cleaned up.
Colin
Re: Locked row in table
Posted by Knut Anders Hatlen <Kn...@Sun.COM>.
Colin Rosenthal <cs...@statsbiblioteket.dk> writes:
> Hi all,
> We have an application which uses derby in auto-commit=false mode.
> For some reason a row in one of our tables cannot be updated from
> our system. Attempting to do so consistently produces a 40XL1 lock
> timeout error. I have shut down the system and gone in with ij and
> updated this row "by hand" without problem, but when I restart the
> system the row is still not updateable.
>
> Any ideas how to proceed?
The lock timeout error probably means there is a deadlock. This can
happen in cases where two transactions access the same resources in a
different order, like in
T1 accesses X before Y
T2 accesses Y before X
If T1 locks X and T2 locks Y, both transactions will wait for the
other transaction to release its lock.
You basically have two options:
1. Make sure that the transactions which cause deadlocks access rows
in the same order.
2. Deal with deadlocks in your code, i.e. catch exceptions and
restart the transactions.
--
Knut Anders