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 <[EMAIL PROTECTED]> 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. >
