Howto perform 2-phase commit (global transaction or savepoint)?

[bairog]

Hello.
In our software we are working with two physical databases (there are two connections).
When we need to create transaction we open two transactions.
When we need to commit transaction we commit two transactions one after another.

And there is a problem: it may happen that first transaction committed successfully, but second one failed.
So we need to rollback both of them (including first one which is already committed).

Is there some kind of savepoint which we can create for the first database before committing both
transactions - so we could rollback to this savepoint when second commit fails?
Or maybe some nested transactions mechanism?

[Shalex]

Try using a distributed transaction:

Code: Select all

    using (TransactionScope transScope = new TransactionScope()) {

        using (SQLiteConnection connection1 = new SQLiteConnection(connectString1)) {
            // Opening connection1 automatically enlists it in the 
            // TransactionScope as a distributed transaction.
            connection1.Open();

            // Do work in the first connection.
        }
        using (SQLiteConnection connection2 = new SQLiteConnection(connectString2)) {
            // Open the second connection, which enlists the 
            // second connection to a full distributed transaction. 
            connection2.Open();

            // Do work in the second connection.
        }
        //  The Complete method commits the transaction.
        transScope.Complete();

        // The result of transaction will be available at the databases after
        // disposing TransactionScope
    }

Is that what you need?

[bairog]

TransactionScope in general is what we need. But it's usage limitations nullifies all the advantages.
In terms of our software client works with our DAL.dll (all EF logic here) something like that:

Code: Select all

DAL.BeginTransaction();
var userTypes = DAL.GetUserTypes();
//
//some client work here
//
user = DAL.GetUsersForUserType(userTypes[i]);
//
//some client work here
//
DAL.UpdateUser(user);
DAL.CommitTransaction();

For that purpose DAL.dll has a static MyDBContext field to work with it's transaction

Code: Select all

((IObjectContextAdapter)MyDBContext).ObjectContext.Connection.BeginTransaction()
((IObjectContextAdapter)MyDBContext).ObjectContext.Connection.CommitTransaction()
and ((IObjectContextAdapter)MyDBContext).ObjectContext.Connection.RollbackTransaction()

But now GetUserTypes() and DAL.GetUsersForUserType() are working with two different databases - so I need TransactionScope.
But as far as I understanded from your example (and Devart help - paragraph "Distributed Transactions") TransactionScope can be only used with using block.
So there is no way TransactionScope as a static field? Or some other way to make it work as I described above?
Thank you.

[Shalex]

But as far as I understanded from your example (and Devart help - paragraph "Distributed Transactions") TransactionScope can be only used with using block.

You can avoid usage of the using block by calling transactionScope.Dispose() manually.

So there is no way TransactionScope as a static field?

You can pass the TransactionScope instance via a static field.

[bairog]

You can pass the TransactionScope instance via a static field.

You can avoid usage of the using block by calling transactionScope.Dispose() manually

So my code will look like that, right

Code: Select all

public static class DAL
{
private static TransactionScope transaction;
....
public static BeginTransaction()
{
if (transaction != null)
   transaction = new TransactionScope();
}
...
public static CommitTransaction()
{
if (transaction != null)
   try
   {
   transaction.Complete();
   }
   catch (Exception ex)
   {
   //WHAT IS HERE???
   }
   finally
   {
   transaction.Dispose();
   }
}
...
public static RollbackTransaction()
{
//IS THAT CORRECT???
if (transaction != null)
   transaction.Dispose();
}

1) Do I need to implicitly enlist EF connections into that transaction? Or everything will work automatically after I call BeginTransaction()?
2) What should I do with that transaction inside a catch block? Some kind of a rollback or nothing?
3) Is RollbackTransaction() correct (calling transaction.Dispose() without calling transaction.Complete() before)?

[Shalex]

1) Do I need to implicitly enlist EF connections into that transaction?

Yes, you do. Refer to https://www.devart.com/dotconnect/sqlit ... ction.html.

2) What should I do with that transaction inside a catch block? Some kind of a rollback or nothing?

Just transaction.Dispose().

3) Is RollbackTransaction() correct (calling transaction.Dispose() without calling transaction.Complete() before)?

Yes, it is.

[bairog]

Yes, you do. Refer to https://www.devart.com/dotconnect/sqlit ... ction.html

Since I don't use using for my TransactionScope static field (I call Dispose() manually as I described above) I tried to use the following code from documentation

Code: Select all

((IObjectContextAdapter)context).ObjectContext.Connection.EnlistTransaction(Transaction.Current);

But I failed:
1) Transaction.Current equals null at runtime
2) Calling context.SaveChanges() pushes all data directly to database
so I conclude that my TransactionScope static field does not work.
What is my mistake?

I've uploaded my test application here (click buttons from top to bottom to create transaction-add entities-read entities-update entities-commit transaction)

[Shalex]

We have sent a modified test project to the email specified in your forum profile.

[bairog]

I've tried your test project and faced some problems:
1) First of all - you are enlisting only one connection (for context with Users) into transaction inside BeginTransaction(). The result is that added UserTypes are pushed to database directly after SaveChanges().
2) You are using sb.Enlist = false for the enother connection (for context with UserTypes) - for what purpose?
3) You are calling conn.Dispose() inside CommitTransaction(), but missing it in RollbackTransaction(). Mistake?

So I've tried to modify your project to enlist both connections into transaction, but enlisting second one fails with the following exception

EntityException: The underlying provider failed on EnlistTransaction:
Cannot enlist local transaction, because current global transaction already contains distributed transactions.

I've uploaded updated project here

And one more thing:
4) Opening connection in BeginTransaction() and closing in CommitTransaction()/RollbackTransaction() is not suitable for me: client may call some method (for example AddUserTypeAndUsers()) separately, without using transactions. So I need to open/close connection directly into methods and directly into methods I need to determine is connection already enlisted into transaction - if not I need to enlist it (if transaction != null)

[Shalex]

1) First of all - you are enlisting only one connection (for context with Users) into transaction inside BeginTransaction(). The result is that added UserTypes are pushed to database directly after SaveChanges().
2) You are using sb.Enlist = false for the enother connection (for context with UserTypes) - for what purpose?
[...]
So I've tried to modify your project to enlist both connections into transaction, but enlisting second one fails with the following exception

EntityException: The underlying provider failed on EnlistTransaction:
Cannot enlist local transaction, because current global transaction already contains distributed transactions.

The SQLite engine doesn't support two-phase commit. That's why dotConnect for SQLite emulates the TransactionScope support, the limitation is only one connection can be enlisted in the distributed transaction.

3) You are calling conn.Dispose() inside CommitTransaction(), but missing it in RollbackTransaction(). Mistake?

Your code calls RollbackTransaction() in the catch block after invoking CommitTransaction() which includes conn.Dispose() in the finally block.

[bairog]

The SQLite engine doesn't support two-phase commit. That's why dotConnect for SQLite emulates the TransactionScope support, the limitation is only one connection can be enlisted in the distributed transaction.

So the question from first post remains open - how could I enlist two different connections (two different databases) into one transaction (without using directive for TransactionScope) - so that two SaveChanges() would work like a single transaction?

[Shalex]

There is no way to enlist two different connections (two different databases) into one transaction.

Consider the following workarounds:

1. One connection (one local transaction) which works with two existing databases: the 1st database is specified via the Data Source connection string parameter, the 2nd one is attached to the 1st database connection via the Attach connection string option.
The SQLite documentation says:

Transactions involving multiple attached databases are atomic, [...]

discussion 1

2. Two connections (two local transactions) which work with two databases: you should implement a mechanism for storing 1st database file before commiting each transaction so that if the 2nd transaction fails you can replace "commited" 1st with "before commit" 1st. Don't forget to clear pool (or turn it off) before removing database file.
discussion 2

[bairog]

Happy new year

I've chose second approach (two connections and two local transactions).
For database storing mechanism I've tried to create a savepoint

Code: Select all

contextUserTypes.Database.ExecuteSqlCommand("SAVEPOINT test")

But when I try to release it (in the finally block after commiting changes)

Code: Select all

contextUserTypes.Database.ExecuteSqlCommand("RELEASE test")

I get an error

SQLte error: no such savepoint: test

I've uploaded my code here. Сlick buttons from top to bottom to create transaction - add entities - read entities - update entities - commit transaction (error is on this stage)

[Shalex]

Happy New Year!

https://www.sqlite.org/lang_savepoint.html says:

The COMMIT command may be used to release all savepoints and commit the transaction even if the transaction was originally started by a SAVEPOINT command instead of a BEGIN command.
[...]
The COMMIT command commits all outstanding transactions and leaves the transaction stack empty.

Code: Select all

contextUserTypes.Database.ExecuteSqlCommand("SAVEPOINT test")

But when I try to release it (in the finally block after commiting changes)

Code: Select all

contextUserTypes.Database.ExecuteSqlCommand("RELEASE test")

Your commit released the test savepoint, so there is no test savepoint in transaction stack when you execute "RELEASE test".

[bairog]

1) Looks like I misunderstood howto use savepoints.
To emulate nested transactions I should use savepoint inside a transaction, right?
I've made some sample code - is it correct now?

Code: Select all

public static void BeginTransaction()
        {            
                connectionUserTypes.Open();
                transactionUserTypes = connectionUserTypes.BeginTransaction();
                //Create a savepoint to emulate nested transaction
                contextUserTypes.Database.ExecuteSqlCommand("SAVEPOINT test");

                connectionUsers.Open();
                transactionUsers = connectionUsers.BeginTransaction();
            }
        }

Code: Select all

public static void CommitTransaction()
        {          
                try
                {
                    //commiting a savepoint (emulating commiting of a nested transaction)
                    //this makes me able to rollback upper-level transaction 
                    //if "transactionUsers.Commit()" fails
                    try
                    {
                        contextUserTypes.Database.ExecuteSqlCommand("RELEASE test");
                    }
                    catch (Exception ex)
                    {
                        //rollback a savepoint (nested transaction) 
                        contextUserTypes.Database.ExecuteSqlCommand("ROLLBACK TO test");

                        //rollback upper-level transaction 
                        transactionUserTypes.Rollback();

                        transactionUsers.Rollback();     

                        return;                
                    }

                    transactionUsers.Commit();                    

                    //the following line will never fail since I've already 
                    //successfully committed a savepoint above
                    transactionUserTypes.Commit();
                }
                //looks like "transactionUsers.Commit()" failed 
                catch (Exception ex)
                {        
                     //rollback upper-level transaction 
                     transactionUserTypes.Rollback();

                     transactionUsers.Rollback();                     
                }
                finally
                {
                    connectionUsers.Close();                    
                    connectionUserTypes.Close();
                }
        }

2) Regarding the first approach with one connection and one local transaction ("data source" database + "attached" database):
Since I'm using EF Code-First - I will have one DbContext with all my Entities (from both databases), right?
But I didn't catch how can I make my DbContext know that some of it's DbSet's should be saved in first database and others should be saved in second one. Some special attributes in DbContext initialisation?