db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Bogdan Calmac" <bc4...@gmail.com>
Subject Re: lock escalation and deadlocks
Date Wed, 01 Aug 2007 11:33:08 GMT
Oh, and one more observation. The IX table lock for the insert thread
mentions LOCKCOUNT=476. I can only infer the meaning of the column (so
this might perfectly normal), but the number of row locks of that
connection is about 150 (it could not exceed 200, the number of
inserts I do per transaction).

On 8/1/07, Bogdan Calmac <bc4all@gmail.com> wrote:
> Hi Brian,
>
> OK, I see. What I said about escalation is wrong. I looked with new
> eyes at the trace (see a more relevant summary below) and here's what
> appears to happen:
>
>  - 216 (the select thread) holds a lock on 1,1 and waits for a lock on 1,7
>  - 183 (the insert thread) holds many locks, including 1,7
>  - 226 (maybe some db internal) waits for a lock on 1,1
>
> 226 must probably be forked from from 183 (the insert thread), that's
> how the deadlock would make sense.
>
> So the original question remains: Is it expected behaviour for insert
> and select on the same table to deadlock each other? Can you figure
> out the relationship between 183 and 226? As a comparison, the same
> test works fine on other databases with a similar locking approach.
>
> XID       |TYPE         |MODE|LOCKCOUNT|LOCKNAME  |STATE|TABLETYPE
> ------------------------------------------------------------------
> *** The following row is the victim ***
> 216       |ROW          |S   |0        |(1,7)     |WAIT |T
> *** The above row is the victim ***
> 216       |ROW          |S   |1        |(1,1)     |GRANT|T
> 226       |ROW          |X   |0        |(1,1)     |WAIT |T
> 183       |ROW          |X   |1        |(1,7)     |GRANT|T
> 183       |TABLE        |IX  |476      |Tablelock |GRANT|T
> 216       |TABLE        |IS  |2        |Tablelock |GRANT|T
> ... and many more like the row below
> 183       |ROW          |X   |1        |(1,65)    |GRANT|T
>
> Another observation that might be useful for the interpretation is
> that the trace is the same on every run. The record of contention is
> always (1,7)
>
> Thanks,
>
> Bogdan.
>

Mime
View raw message