How can a blocking_session_id be minus 2?

Question

I have a case where a query I'm running is taking a long time. When I checked sys.dm_exec_requests the blocking_session_id is a negative value, -2 to be specific. The wait is a LCK_M_X and the blocked command is a DELETE statement. To the best of my knowledge nothing else is touching that table at the moment. In fact the only active requests are the blocked connection and the query checking sys.dm_exec_requests. When I look at sys.dm_tran_session_transactions the blocked session is the only one showing up.

Answer 1

Take a look at this blog post by Bob Dorr: How It Works: Orphan DTC Transaction (Session/SPID = -2)

As Bob says, it's a bit of a misnomer that it's an orphaned transaction by DTC, but that is usually how it represents itself. It's due to (as per Bob's words) "NO ENLISTED SESSIONS on the SQL Server but the transaction is active yet". You should take a look at that link in it's entirety.

Oftentimes what happens with DTC transactions is that there is no commit/rollback and it does indeed pose a problem, as MSDTC may not reconnect to clean it up, release locks, etc.

See the BOL reference on sys.dm_tran_locks:

Distributed transactions that are not associated with a session ID value are orphaned transactions and are assigned the session ID value of -2. For more information, see KILL (Transact-SQL).

Yes, BOL uses the terminology "orphaned transaction", as in my experience that is what it usually ends up being (although you can't make that blanket statement without proper investigation that a distributed transaction coordinator hasn't indeed forgotten about the transaction holding everything up). In that case, after a proper orphaned determination, it is common to then KILL it based off of the unit of work (UOW).