Lars Clausen wrote:
On Wed, 2005-11-02 at 13:34, Knut Anders Hatlen wrote:
Lars Clausen <[EMAIL PROTECTED]> writes:
On Tue, 2005-11-01 at 15:36, Knut Anders Hatlen wrote:
Lars Clausen <[EMAIL PROTECTED]> writes:
Hi!
Delving further into our deadlock/lock timeout problems, I now have a
dump of the locks in question. I think I understand what they're
saying, but my interpretation doesn't make as much sense as I'd like it
to. The lockTable dump is as follows:
ERROR 40XL2: A lock could not be obtained within the time requested. The lockTable dump is:
2005-11-01 12:14:33.516 GMT
XID |TYPE |MODE|LOCKCOUNT|LOCKNAME |STATE|TABLETYPE / LOCKOBJ
|INDEXNAME / CONTAINER_ID / (MODE for LATCH only) |TABLENAME / CONGLOM_ID |
-------------------------------------------------------------------------------------------------------------------------------------------------------------
*** The following row is the victim ***
20965 |ROW |X |0 |(4,105) |WAIT |T
|NULL |SEEDLISTS |
*** The above row is the victim ***
20963 |ROW |S |9 |(4,105) |GRANT|T
|NULL |SEEDLISTS |
20965 |ROW |S |1 |(4,105) |GRANT|T
|NULL |SEEDLISTS |
20965 |ROW |S |1 |(5,1) |GRANT|T
|SEEDLISTDOMAIN |SEEDLISTS |
[...]
-------------------------------------------------------------------------------------------------------------------------------------------------------------
The interesting lock of course is on the SEEDLISTS table. It appears to
me that the 20963 XID has a shared table lock while the 20965 XID has an
exclusive table lock. That's the first thing that doesn't make sense --
doesn't seem like a very exclusive lock that. Also, I would expect the
20965 XID to have no problems getting an additional lock on a SEEDLISTS
table row, since it already has a lock on the entire table. Could
somebody explain these oddities to me?
You have misinterpreted IS and IX locks. These are intentional locks,
which means that the transaction intends to lock rows in that
table. For example, if a transaction wants an exclusive lock on row R1
in table T1, it will lock table T1 with intent exclusive (IX) and row
R1 in exclusive mode (X). This prevents others from locking the entire
table or that particular row, but not from locking other rows in the
table.
Thank you, that made it a lot clearer. So for wanting to lock a row,
the intentional table locks are irrelevant. That trims the relevant
entries to the above. Now I got a suspicion that I want to run by you:
I've been running the entire system with autocommit off and committing
explicitly after all updates. Does that mean that shared locks are kept
until the next commit, even if the statements causing the locks are
close()d? It appears likely, as switching to autocommit on for all but
the few places that requires transactions seems to cure the problem[1],
but it is not the behaviour I expected from reading the java.sql
documentation.
This depends on the isolation level:
1) In read uncommitted mode, both shared and exclusive locks are
released after you have finished the operation on a row.
2) In read committed mode (default in Derby), shared locks are
released after you have finished reading the row, but exclusive
locks are kept until the transaction is committed or rolled
back.
3) In repeatable read mode, both shared and exclusive locks are
kept until the transaction has finished.
4) Serializable mode is like repeatable read, but additionally
prevents so-called phantom reads.
If you are using the default isolation level, shared locks should not
be kept until the next commit. The lock table indicates that you are
running with repeatable read or serializable.
I am not sure that is what the lock table is saying, as I don't
understand exactly what the application is doing. During read
committed read locks will exist and one will be able to view them
in the lock table.
As another posting said, this is a lock timeout not a deadlock. The
message will say deadlock if it is a deadlock.
The lock table is saying there are 2 transactions (20965 and 20963),
which should correspond to 2 connections in your program.
transaction 20965 has a S row lock on (4, 105) (page 4, row id 105)
transaction 20965 is waiting on X lock on (4, 105)
transaction 20963 has a S row lock on (4, 105)
20963 has locks outstanding on 4 tables, is that a single join or
4 concurrent open result sets? If it is 4 concurrent result sets
then autocommit is almost always a bad idea.
So for some reason 20963 is holding the lock on the order of a minute
causing timeout of 20965. The question is why. Some possible
explanations:
1) 20963 is some sort of gui sitting waiting for input before moving
off the row.
2) 20963 did not close the statement so derby does not know it really
wanted to "move off the row".
3) as has been suggested you are somehow not running in read committed
mode, I don't know of any situation where Derby will change this
if you don't ask it to. You can use the available properties to
get query plans for each statement to be printed to the derby.log -
these query plans will include the isolation level. See the
documentation.
4) there is some sort of undetected deadlock in your program that
involves derby locking and some other wait mechanism. The 2 most common
mistakes are:
1) use 2 connections but only one thread. Connection 1 obtains
locks, Connection 2 waits inside Derby on the lock for Connection 1 to
give up it's lock but the code to execute another jdbc call for
Connection 1 can never run as it needs the call from Connection 2 to
come back.
2) 2 threads, 1 connection in both threads.
Connection 1 hold lock A.
Connection 2 gets java synchronization on object A or routine A
Connection 2 calls Derby and waits on lock A
Connection 1 continues, and wants to do something that will
release lock on A, but never can because if first needs java
synchonization on object A or routine A.
Curious. I am not setting the isolation level programmatically, and
have no derby.properties file setting it either, so it should be read
committed. How do you tell from the table what level we're at? Is
there some description of the fields somewhere? It wouldn't be that
Connection.setAutocommit() can change the isolation level, would it?
-Lars
