I encountered a really strange problem a few weeks ago that I had not seen before that could really pose a debug pain to any dev that has never seen it before (I know it did me).  I knew what was going on, I just could not figure out any way to fix it.  I was working on a web app that was using System.Transactions for transactional code inside the business layer that wrapped a bunch of data manipulation processes.  In the middle of the transaction I would get an error from an insert that would bomb.   I thought all was well, I assumed the transaction rolled back as my debugger dropped, but when I went to check on some other data with the table that was being written to I would notice it was locked.   I pulled out a  'with nolock' and was able to access everything and did a who to see if I could figure out what was holding up the table.  At first I had thought my error (which I didn't read in full before I stopped the debugger) could have been caused by the lock, but I did not remember the debugger sitting for 10s of seconds before it happened for a timeout.   So I tried to look to see if I could find the process to kill to release the lock.  I couldn't find anything.  The container I was working on was really only being worked on by me (no other reason for anyone else to utilize it), so when I could not find the lock I was really  perplexed.  I asked someone about it and he started to explain to me "ghost locks".  If there was ever really an error that sounded like a shit-your-pants tale to tell around a campfire this might be it.  They are locks that lock tables that you cannot see by mortal normal means.   The scaryness goes away after you realize it is just a negative spiid.

The kicker is looking for that negative req_spid.  So why so scary you ask?  It just happened to be hidden in a ninja-like fashion, but we all know ninjas can still be killed.   Although I can't see the ninja, I know it is behind the curtain so it is just a matter of pointing my gun at him and pulling the trigger right?

 

not going to work, the kill sproc will only take out positive IDs

So what do we do?

Each transaction is going to attached itsef ith a session or Unit Of Work ID that is a guid key for that specific transaction.  Kill will also work with this key, so we just need to find the key:

 

blast out the one req_transactionUOW that isn't a zero'd guid (the blocking transaction) and you should be good.

As I understand it, this generally happens when specific transaction under system.transactions touches 2 Databases.   The Microsoft Distributed Transaction Manager does not usually kick into play when there is only one transactional request to one database, but when we come outside of that one database (and in the instance I found this I was using 2 databases) then the transaction will elevate to the MSDTC's control.  When it does this, if the second part of the transaction to server b (after the connection to server a) is severed then it will leave the transaction portion to server a in an orphaned state.   This is why the server assigns it an spid of -2.   It is the flag for saying this transaction was orphaned in this manner.  Unfortunately it continues to leave a lock on whichever tables it was operating on until killed, and with it's silent ability to merge into its surroundings makes it exceptionally hard to find unless you have encountered its ninja ways before.