TransactionScope yields System.Transactions.TransactionException

Question

I have a database class with methods as shown below. Both foo and bar are all-or-nothing operations. Hence the need for transactions. Note that I'm using MySQL 5.5.21 together with MySQL .NET Connector 6.6.4.

public void foo()
{
    using (var transaction = new TransactionScope())
    {
        // This call yields a 'System.Transactions.TransactionException'
        bar();

        insertStuff();

        transaction.Complete();
    }
}

public void bar()
{
    using (var transaction = new TransactionScope())
    {
        insertStuff();
        insertStuff();

        transaction.Complete();
    }
}

private void insertStuff()
{
    using (var connection = CreateConnection()) // Using the same connection string!
    {
        connection.ConnectionString = ConnectionString;
        connection.Open();
    }
}

I did try specifying TransactionScopeOption.RequiresNew in the constructor, but it didn't help. I also tried explicitly open a connection before each transaction scope, but still no success.

What I want is this:

If I call bar alone, there should be one transaction.
If I call foo (which in turn calls bar), there should be one transaction.

Q: Am I facing a limitation of MySQL .NET Connector or am I doing something wrong?

EDIT: The exact error is this: The operation is not valid for the state of the transaction. So, as soon as I call Open() on the connection the transaction bails out...

What is the inner exception of TransactionException? Is there any more information (stack trace)? — Davin Tryon, Nov 12 '12 at 09:04
@eggyal transaction-scope, however, explicitly **can** be layered. There are defined rules for what happens in each combination of complete/abort. — Marc Gravell, Nov 12 '12 at 09:06
Not sure how to fetch the exception as it only shows in the output window: `A first chance exception of type 'System.Transactions.TransactionException' occurred in System.Transactions.dll` — l33t, Nov 12 '12 at 09:13

score 0 · Answer 1 · answered Nov 12 '12 at 09:07

0

Most likely, you are opening 2 or more connections to the database and, being in a transaction scope, MSDTC is called into action. Usually, if you wanted to have one transaction, you would create the connection and then pass it to each of the operations. This would ensure that you were always making changes on the same connection. If you have 2 connections, then the changes on each have to be coordinated.

However, if you provide more of the exception message, it might be more clear of the cause.

answered Nov 12 '12 at 09:07

Davin Tryon

66,517
15
143
132

As far as I understand, the whole point of TransactionScope is to avoid passing around the connection. No? There is no exception message. Only `A first chance exception of type 'System.Transactions.TransactionException' occurred in System.Transactions.dll` in the output window and a no-op transaction as result. – l33t Nov 12 '12 at 09:12

score 0 · Accepted Answer · answered Nov 12 '12 at 09:22

0

Use bar as shown:

public void bar()
{
        insertStuff();
        insertStuff();
}

Is it work?

For best practice you can create transaction using IDbConnection.BeginTransaction() because the transaction is connection scoped.

public void foo()
{
    using(IDbConnection connection = new MySqlConnection(/*connection string*/))
    {
        connection.Open();
        using(IDbTransction transaction = connection.BeginTransaction())
        {
            bar(connection);
            insertStuff(connection);

            transaction.Commit();
        }

    }
}

public void bar(IDbConnection connection)
{
        insertStuff(connection);
        insertStuff(connection);
}

private void insertStuff(IDbConnection connection)
{
     // do stuff
}

answered Nov 12 '12 at 09:22

Hamlet Hakobyan

32,965
6
52
68

Sure it works without a transaction. I tried your solution, but it seems my database operations are never rolled back even though the `Commit` is never reached. If I call `Rollback` explicitly, my operations are still commited! Not sure how to interpret this. – l33t Nov 12 '12 at 09:38
@l33t Your transaction will Rolled back if the exception will occurs in InsertStuff (see using and Dispose of Transaction). – Hamlet Hakobyan Nov 12 '12 at 09:41
Yes, this works. But I would really like to use TransactionScope... :( – l33t Nov 15 '12 at 10:11
using (var connection = CreateConnection()) // Using the same connection string! This is not same as using same connection. For creating transaction around different connection you must use distributed transaction coordinator or such one. About use TransactionScope, you can create scope then connection and attach transction to connection, then pass this connection to to for or bar. – Hamlet Hakobyan Nov 15 '12 at 11:36
Even though it is not guaranteed, using the same connection string will most likely use the same connection. At least that is true for SQL Server. When you say "attach", do you mean this: http://msdn.microsoft.com/en-us/library/system.data.common.dbconnection.enlisttransaction.aspx – l33t Nov 15 '12 at 12:52

TransactionScope yields System.Transactions.TransactionException

2 Answers2