Lars Clausen wrote:
Hi!
I was under the impression that by default Derby does not take locks
when reading, so deadlocks should only occur when two updates collide.
However, I had the exception below this morning:
Caused by: SQL Exception: A lock could not be obtained due to a deadlock, cycle
of locks and waiters is:
Lock : ROW, DOMAINS, (346,20)
Waiting XID : {3556682, S} , APP, SELECT domains.name, configurations.name
FROM domains, configurations, harvest_configs WHERE harvest_id = ? AND
configurations.config_id = harvest_configs.config_id AND
configurations.domain_id = domains.domain_id
Granted XID : {3556738, X}
Lock : ROW, CONFIGURATIONS, (410,270)
Waiting XID : {3556738, X} , APP, UPDATE configurations SET comments = ?,
template_id = ( SELECT template_id FROM ordertemplates WHERE name = ? ),
maxobjects = ?, maxrate = ?, overridelimits = ?WHERE name = ? AND domain_id = ?
Granted XID : {3556682, S}
. The selected victim is XID : 3556682.
<
<snip>
The first lock shown (SELECT domains.name, ...) is inside an entirely
read-only function, so it is strange that it should deadlock. We are
using default settings for Derby. Is there any way this could actually
be caused by that read, or should I really go around looking for an
unfinished transaction somewhere before that select?
Read acquire shared locks except for read uncommitted. From the manual
http://db.apache.org/derby/docs/10.1/devguide/cdevconcepts842304.html
"When a statement reads data without making any modifications, its
transaction obtains a /shared lock/ on the data. Another transaction
that tries to read the same data is permitted to read, but a transaction
that tries to update the data will be prevented from doing so until the
shared lock is released. How long this shared lock is held depends on
the isolation level of the transaction holding the lock. Transactions
using the TRANSACTION_READ_COMMITTED isolation level release the lock
when the transaction steps through to the next row. Transactions using
the TRANSACTION_SERIALIZABLE or TRANSACTION_REPEATABLE_READ isolation
level hold the lock until the transaction is committed, so even a SELECT
can prevent updates if a commit is never issued. Transactions using the
TRANSACTION_READ_UNCOMMITTED isolation level do not request any locks."
My guess is -it seems like there is a conflict between the locks
necessary on configuration table.
To help debug this, please check out the faq -
http://db.apache.org/derby/faq.html#debug_lock_timeout . This will
print the lock table info in derby.log and you can see what locks are
held by which transaction.
Hope this helps,
Sunitha.
