How can an INSERT from SELECT deadlock with itself (same SPID)

Mark Freeman 2015-11-16 17:45:29

I have an Agent Job that runs a single stored procedure from a signle scheudle, one time per day. That procedure TRUNCATEs a table and then repopulates in using an INSERT FROM SELECT. The SELECT has a UNION and both parts use some of the same tables.

How can this procedure deadlock with itself?

Aaron Bertrand 2015-11-16 18:55:54
Looks like intra-query parallelism. My first suggestion would be to try changing the INSERT/SELECT to use OPTION (MAXDOP 1).
Mark Freeman 2015-11-17 04:00:44
I accept that OPTION (MAXDOP 1) will probably fix it, but I'm curious as to how the engine would allow such a situation to occur. Can you point me to any relevant write-ups on this phenomenon?
Aaron Bertrand 2015-11-17 04:04:39
Usually bugs that are too risky to fix. Jonathan Kehayias touches on it briefly here and Bart Duncan goes into more detail here.
Mark Freeman 2015-11-17 14:41:15
Aaron, thanks for the answer and the pointers to more detailed information.