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.
