Question

I have to do queries on uncommitted changes and I tried to use transactions, but I found that it do not work if there are exceptions.

I made a simple example to reproduce the problem. I have a database with only one table called "Tabella" and the table has two fields: "ID" is a autogenerated integer, and "Valore" is an integer with a Unique constraint. Then I try to run this code:

using (TransactionScope scope = new TransactionScope())
{
    Db1Container db1 = new Db1Container();

    try
    {
        db1.AddToTabella(new Tabella()
        {
            Valore = 1
        });
        db1.SaveChanges();
    }
    catch { }

    try
    {
        db1.AddToTabella(new Tabella()
        {
            Valore = 1
        });
        db1.SaveChanges(); //Unique constraint is violated here and an exception is thrown
    }
    catch { }

    try
    {
        db1.AddToTabella(new Tabella()
        {
            Valore = 2
        });
        db1.SaveChanges();
    }
    catch { }

    //scope.Complete(); //NEVER called
}   //here everything should be rolled back

Now if I look into the database it should contain no records because the transaction should rollback, instead I find two records!!!! One with Valore=1 and one with Valore=2. I am missing something? It looks like the second call to SaveChanges method rollback its own changes and "deletes" the transaction, then the third call to SaveChanges commits the changes of the first and the third insert (at this point it is like the transaction not exists).

I also tried to use SaveChanges(false) method (even without calling AcceptAllChanges method), but with no success: I have the same behaviour.

I do not want the transaction to be rolled back automatically by SaveChanges, because I want to correct the errors (for example by user interaction in the catch statement) and make a retry.

Can someone help me with this? It seems like a "bug", and it is giving me a really big headache...

Answer 1

Are you sure that scope.Complete() is never called? If you catch{} all exceptions, the code will continue running and the Complete statement will be reached. Instead, you should for example let the exception to propagate and catch it at a higher level.

Answer 2

This is a bit late for the original question but perhaps it will help someone.

This code should work with .net/ef 4 and sql server 2008 R2.

using (TransactionScope scope = new TransactionScope())
{
    Db1Container db1 = new Db1Container();

    try
    {
        db1.AddToTabella(new Tabella()
        {
            Valore = 1
        });
        db1.SaveChanges();

        db1.AddToTabella(new Tabella()
        {
            Valore = 1
        });
        db1.SaveChanges(); //Unique constraint is violated here and an exception is thrown

        //if we get here then there were no errors thrown on previous SaveChanges() 
        //calls and we can complete the transaction with no rollback
        scope.Complete();

    }
    catch (Exception)
    {
        //do nothing
    }

}

友情链接