Assumptions about SPIDs from where deadlock events will be produced

This is related to a discussion I’ve had with some colleagues during an XE session couple of weeks ago.

These are the assumptions you can made:

Lock:Deadlock Chain event is produced in the context of the lock monitor’s session. It tends to be the same SPID in a given system, and is typically SPID 6, but race conditions can affect the order in which SQL creates sessions for initial system tasks (RESOURCE MONITOR, XE TIMER, XE DISPATCHER, LAZY WRITER, LOG WRITER, SIGNAL HANDLER, LOCK MONITOR, TRACE QUEUE TASK, BRKR TASK, TASK MANAGER, CHECKPOINT, etc ). So, it can vary.

Lock:Deadlock event is produced by the session that was chosen as a victim of the deadlock situation. So, the SPID in the event is that of the victim’s session. So, this one naturally will vary.

Deadlock graph event is produced in the context of whatever task manager system session picking up the task enqueued by lock monitor to produce the graph event. So, this one also changes.

Bottom line is: don’t make any assumptions. 🙂



Comments (0)