Q: "SQL run using a SqlTransaction is part of a SQL Server Transaction and can be rolled back."
A) True
B) False
The answer is of course "A", right? That's what I thought too.
Basic SqlTransaction Usage
Most of us assume that we can write this:
using (var con = new SqlConnection(".."))
{
con.Open();
using (var tran = con.BeginTransaction())
{
var cmd = con.CreateCommand();
cmd.CommandText = "Update dbo.Customer ...";
cmd.Transaction = tran;
try
{
cmd.ExecuteNonQuery();
tran.Commit();
}
catch
{
tran.Rollback();
}
}
}
This is plain and simple SqlTransaction based logic. Exec the command. If it completes without error, call Commit(). If it has an issue, then call Rollback();
But ... What Does This Do?
I challenge you to predict the behavior of the following "Retry" scenario. The code may seem a bit odd… Why would you execute more SQL after an exception? But it shows what one might do if the code implemented Retry semantics inside a transaction.
For retries, one might want to open a transaction, run a set of statements, and then retry any statements that had failed. The SQL transaction should remain in force in spite of any errors thrown by SQL server. For example, a foreign key violation does not automatically abort the transaction. Rather, the code would catch the FK violation and queue it for subsequent execution after completing the 1st pass.
This does in fact work… most of the time. I say most of the time, because deadlock errors in SQL Server cause some interesting behavior.
Here's a snippet that should easily run in LinqPad without too much work. The only pre-requisite is to create a DB in SqlExpress named "Scratch".
Pay close attention to lines 144-145. How will they behave after the deadlock? What is their impact on the database?
Shocked, Stupefied, and Generally Stumped
If you run the above code and look at the data that ends up in [dbo].[customer2], you'll notice a very strange thing:
The transactions were rolled back, but one of the "C" statement was still applied!
WHY???
Short Answer…
The Deadlock aborts the underlying transaction on SQL Server, but SqlTransaction and SqlCommand happily continue to execute more commands outside of any transaction.
Hindsight is 20/20
After diagnosing and fixing this issue, it almost makes sense, but it's not at all intuitive up front. I asked a couple colleagues to guess the outcome of the code, and both guessed wrong. I suspect that most of you will also be surprised by the results. Hindsight is 20/20, but up front, this one is nonsense.
Deadlocks in SQL server mean that SQL will kill one of the transactions and roll it back. This frees the locks and allows the other transaction to proceed. In the scenario above, we force a deadlock, meaning that one of the transactions is ROLLED BACK on the server. It's not just "zombied", which would cause "SELECT XACT_STATE()" to return "-1". No, in this case, the transaction is done. There is no more transaction.
So, how does one of those "C" statements get applied? Basically, SqlCommand doesn't know that there is a problem with the transaction on the server, nor does it ask the SqlTransaction object to check. Instead, it just sends the statement to the server, assuming that the server still has a transaction open on the current connection. But there is no transaction. As a result, those statements are executed with no transaction at all. The subsequent Rollback() has no means to undo their work.
Is This a Bug?
Calling something a bug is rather severe. In this case, I suspect that the framework can do better.
The system does some checks to ensure proper & safe usage of the SqlTransaction, but not enough. After a deadlock, the client still has a reference to a SqlTransaction object. This object still thinks that it has a transaction on the server. It will even allow you to call Rollback() on this transaction object after the deadlock. But it won't let you call Commit(). It seems that SqlTransaction knows that the transaction can't commit, and throws an error back at you. Additionally, if you use a SqlConnection with a SqlCommand, but don't pass the associated SqlTransaction, then it will throw an error.
So the system does some internal checks, but it doesn't check for the deadlock scenario. Well, I think it should do better. Before executing a command, it should check whether the associated transaction is gone. If the server transaction is gone, it shouldn't even run the command. It should just error. Otherwise, SqlCommand will continue to run the statements outside of any transaction.
I don't know if this is possible, or if it has some sever side-effects, but the current behavior of SqlTransaction is a very dangerous and sharp edge on the API surface of the .NET framework. A transaction that doesn't actually roll back the associated commands is not good.
Solution: Catch Deadlock Errors and Halt
The solution seems to require catching the SqlException, inspecting the Number to see if it's a deadlock, and then taking an alternate course of action. In the case of retry logic, the processing should immediately stop and rollback to prevent any subsequent commands from executing outside a transaction.
To catch a deadlock, look for SqlException.Number == 1205. This indicates a deadlock occurred.
Happy (Transaction) Coding!
Cheers!