0

I've got a nested transaction, where after an exception data ends up being in the database without it being committed by the outer transaction.

There are three insert statements, every time followed by an select statement. The second select statement throws the exception. And for some reason I currently can not explain the third statement ends up in the database. If however the exception does not occur (either by removing the select statement or by removing the 0 in the input array) nothing is committed in the database which is the expected behavior.

First of all the small working example:

using (var transactionScope = new TransactionScope())
{
    var input = new[] {1, 0, 2 };

    foreach (var i in input)
    {
        using (SqlConnection cnn = new SqlConnection(connetionString))
        {
            cnn.Open();
            using (var tran = cnn.BeginTransaction())
            {
                var sql = "INSERT INTO [Test].[dbo].[Test] ([Timestamp] ,[Message]) VALUES ('" + DateTime.Now + "', 1 / " + i + ");";
                using (SqlCommand cmd = new SqlCommand(sql, cnn, tran))
                {
                    try
                    {
                        cmd.ExecuteNonQuery();
                        tran.Commit();
                    }
                    catch (Exception e)
                    {
                        tran.Rollback();
                    }
                }
            }
        }
    }
}

What am I missing?

I'm using SQL Server 2016 & C# 7

Vulcano
  • 415
  • 10
  • 25
  • You are using a different transaction for each `insert / select` pair. So when you have an exception in your second transaction, that insert statement is rolled back, but the next `insert / select` pair of statements does not belong to that transaction, they belong to a new transaction that doesn't rollback since it has no exceptions. – Zohar Peled Feb 12 '18 at 07:35
  • But when it is a different transaction, why is it rolled back if there was no exception? And if I remove the outer transaction scope 1 & 2 end up in the database (which also makes sense to me). So the outer and inner transactions musst be tangled somehow. – Vulcano Feb 12 '18 at 07:43
  • I'm not really sure. I rarely use inline sql, I work almost exclusively with stored procedures, so naturally I use transactions inside the stored procedures if I need to. That's why I didn't attempt to post an answer, just a comment. – Zohar Peled Feb 12 '18 at 07:49
  • Even though this code is suspect anyway. I'm wondering if the transaction name has something to do with it. Does this give different results `cnn.BeginTransaction($"foo{count++}")` i.e changing the transaction name? – TheGeneral Feb 12 '18 at 07:54
  • You might want to read [this SO post](https://stackoverflow.com/questions/4497910/will-an-inner-transaction-scope-roll-back-if-the-outer-transaction-scope-doesnt) as well. – Zohar Peled Feb 12 '18 at 08:09
  • 1
    Possibly it doesnt fail because you have a select in there but execute a non query so it doesnt try return results. – BugFinder Feb 12 '18 at 08:09
  • I think @BugFinder hit the nail on the head. If you want to select, don't use `ExecuteNonQuery`. In fact, if you want to execute multiple statements of any type, it is cleaner to execute them separately. – HoneyBadger Feb 12 '18 at 08:41
  • Combining `TransactionScope` with explicit transactions is a bad idea (as is naming your transactions; names and nesting are for the most part a lie in SQL Server, which only maintains a simple count). I know exactly how transactions work on the server level, yet I have trouble reasoning about what this code does. If I had to tell, I'd break out a profiler to see exactly what statements are issued in terms of `BEGIN TRANSACTION`/`COMMIT`/`ROLLBACK`. – Jeroen Mostert Feb 12 '18 at 08:46
  • Is that some kind of programming puzzle? Because to me that code makes little sense (I mean I have no idea what you are trying to achieve with it). – Evk Feb 12 '18 at 08:57
  • This is only a small example which produces the same (erroneous?) result I'm currently facing. I updated the example without the select and without a transaction name. Still one entry. @JeroenMostert Why is combining TransactionScope and an explicit transaction a bad idea? Do you have any sources that state this should not be used? From my understanding in transactions either two entries or no entry should end in the database, but I can't explain why only one entry is in the db. – Vulcano Feb 12 '18 at 10:43
  • It's a bad idea because `TransactionScope` introduces implicit enrollment in the ambient transaction, while explicit transactions are, well, explicit enrollment that don't use any ambient transaction. As a result, reasoning about which statements and connections will participate in what transaction gets more complicated. This has nothing to do with "it should not be used", I have no reason to assume it's not allowed -- I just don't know *how* it works, from the source alone. Hence, I'd need to break out a profiler and see for myself if I wanted to understand it. – Jeroen Mostert Feb 12 '18 at 10:46
  • After playing around a bit, it seems that the ambient TransactionScope is rolling back all changes prior to the exception. After that it somehow "looses" its scope and let every operation through. My solution is now to remove the explicit transaction as it is not needed there (some legacy cleanup). I still feel though that this is not the intended behavior. – Vulcano Feb 13 '18 at 10:45

0 Answers0