ASP.Net MVC 5 and SQL Transaction Isolation Levels

Question

Having an issue with the creation of a front end for a database. I began with a EF6 Code First from database. I created the controller and went to run a test on the create page, but ran into a big issue. It gave me the following error:

 System.Data.SqlClient.SqlException: Transaction failed in database 'DB' because the statement was run under snapshot isolation but the transaction did not start in snapshot isolation. You cannot change the isolation level of the transaction to snapshot after the transaction has started unless the transaction was originally started under snapshot isolation level.

The other thing that showed up was the code from the controller:

if (ModelState.IsValid)
{
    db.Characters.Add(character);
    await db.SaveChangesAsync();
    return RedirectToAction("Index");
}

It highlighted the await db.SaveChangesAsync(); as the error line. So now I am confused as to where to look to change the transaction level in the MVC code. Will it be the Web.config, the model that was created for the database, or the controller? I know that I would have to use the TransactionScope to change it.
Easier would be to change the default transaction scope to what the database is set up for. Just not sure where to make that change?

where is `db` instantiated, you should be making use of a `using` statement when using the `db` context. — 3dd, Aug 17 '15 at 03:11
So I should be putting it in the controllers where I have: private DBModel db = new DBModel(); ? And that should have the using in the new DBModel( using TransactionScopeOption.IsolationLevel.Snapshot ) ? — Mike Mastro, Aug 17 '15 at 12:00
Instead of making the `DBModel` private, see the `Lifetime` section in the following article https://msdn.microsoft.com/en-us/data/jj729737.aspx — 3dd, Aug 18 '15 at 03:12

3dd · Answer 1 · 2015-08-17T05:30:54.680

0

In the constructor of your EF context you can call the following passing in the IsolationLevel you want

_ContextTransaction = Database.BeginTransaction(isolationLevel)

Then by overriding the SaveChangesAsync in the context, you can commit the transaction

public override Task<int> SaveChangesAsync() {
    var result = base.SaveChangesAsync();
    _ContextTransaction.Commit();

}

Note that you would need to Commit the transaction for other Saving methods too, and you might also consider testing if the context is part of another transaction before starting a new one with BeginTransaction(isolationLevel)

To check for current transactions you can use System.Transactions.Transaction.Current

edited Aug 17 '15 at 05:30

answered Aug 17 '15 at 05:23

3dd

2,520
13
20

I just want to clarify that the constructor of my EF context would be the: public partial class DBModel : DbContext { public DBModel() : base("name=DBModel") { } – Mike Mastro Aug 18 '15 at 02:04
Well I added _ContextTransaction = Database.BeginTransaction(isolationLevel); to the braces right after base("name=DBModel"), but it does not like _ContextTransaction and isolationLevel, it wants me to add some version of what they are in the model. – Mike Mastro Aug 18 '15 at 10:43
You need to define a private variable `_ContextTransaction` i.e. `private DbContextTransaction _ContextTransaction;` inside the class. `isolationLevel` should be the option from 'IsolationLevel' enum you want to use – 3dd Aug 18 '15 at 10:54
Ok well having the DBAs remove the Snapshot on the database was no help. They have put it back since. A private DBContextTransaction _ContextTransaction put in the DBModel.cs and then adding the _ContextTransaction = Database.BeginTransaction(isolationLevel Snapshot) led to the = sign being a problem along with .BeginTransaction say it is a method being used like a field. Taking the async code out of the controller led to the same transaction issue. We are out of ideas. – Mike Mastro Aug 19 '15 at 03:05

ASP.Net MVC 5 and SQL Transaction Isolation Levels

1 Answers1