scrnjakovic February 2016

EF6 Transaction rollback & concurrency issue

Once I rollback my transaction and try again, it fails with DbUpdateConcurrencyException.

After looking at SQL profiler, I see that first time INSERT query is sent, as expected, while second time it tries to update, even though first one was rolled back?!

First query:

exec sp_executesql N'INSERT [dbo].[FiscalReceipt]([PurchaseTime], [ReceiptNumber], [Cash], [Card], [Bank])
VALUES (@0, @1, @2, @3, @4)
SELECT [FiscalReceiptID]
FROM [dbo].[FiscalReceipt]
WHERE @@ROWCOUNT > 0 AND [FiscalReceiptID] = scope_identity()',N'@0 datetime2(7),@1 int,@2 decimal(18,2),@3 decimal(18,2),@4 decimal(18,2)',@0='2016-02-08 15:05:43.9145089',@1=666,@2=1.70,@3=0,@4=0

Second query:

exec sp_executesql N'UPDATE [dbo].[FiscalReceipt]
SET [PurchaseTime] = @0, [Cash] = @1
WHERE ([FiscalReceiptID] = @2)
',N'@0 datetime2(7),@1 decimal(18,2),@2 int',@0='2016-02-08 15:12:11.8101261',@1=555.00,@2=2042

P.S. Shouldn't there be TM:Rollback or TM:Commit in the EventClass column in SQL Profiler table when something is commited?

C# Code:

NOTE: OutOfPaperException is intended to be ignored and transaction inside that scope is being commited on purpose. Changes should be rolled back on any other exception.

var transaction = DatabaseContext.Database.BeginTransaction();

            try
            {
                // Commented for debugging
                //IFiscalPrinter fPrinter = DeviceManager.GetFiscalPrinter();
                //var lastReceiptNumber = fPrinter.GetLastReceiptNumber();

                // false data for debugging
                var lastReceiptNumber = 666;
                Receipt.ReceiptNumber = lastReceiptNumber++;
                Receipt.PurchaseTime = DateTime.Now;

                DatabaseContext.SaveChanges();

                //fPrinter.PrintFiscalReceipt(Receipt);

                // Exception for debugging        

Answers


Jonathan Magnan February 2016

For your EDIT2

When SaveChanges methods is called, if no error is found when saving (which is the case since you rollback after), the ObjectContext.AcceptAllChanges() methods is invoked which accept changes and populate primary keys, foreign keys and change entry state.

The rollback only rollback the transaction and not on the object context / change tracker.

It's to late at this point to call SaveChanges again since entities are already populated with database information even if you did a rollback.

Original Question

You throw an error (for debugging) after the SaveChanges has been successfully completed

if (Receipt.Cash < 500)
{
    throw new Exception();
}

So by following the previous logic, the AcceptAllChanges is already invoked.

EDIT

You can control the AcceptAllChanges by saving using the ObjectContext

var objectContext = ((IObjectContextAdapter) ctx).ObjectContext;
objectContext.SaveChanges(SaveOptions.DetectChangesBeforeSave);

transaction.Commmit();
objectContext.AcceptAllChanges();

Post Status

Asked in February 2016
Viewed 1,780 times
Voted 7
Answered 1 times

Search




Leave an answer