Why doesn't WebAPI2 with Entity Framework automatically create a transaction for me?

Question

I have a WebAPI2 Restful services API and I am using SQL Server database with Entity Framework. I have PUT methods like this

    /* 
     * This changes the Study Status.
     */
    [HttpPut, Route("ResponseSetStatus/{id:int}")]
    public IHttpActionResult UpdateResponseSetStatus(int id, [FromUri] string status = null)
    {
        var db = new MyContext(MyContext.EntityContextString);
        var responseSet = db.ResponseSets.FirstOrDefault(x => x.ResponseSetId == id);

        if (responseSet == null)
        {
            return NotFound();
        }

        // ADD ONE SECOND DELAY HERE FOR TESTING
        Thread.Sleep(1000);
        responseSet.Status = status;
        db.SaveChanges();
        return Ok();
    }

I thought this would work! But it fails. One of the columns in the database is a rowVersion (to prevent lost updates). When I call this function from multiple clients I get exception...

An exception of type 'System.Data.Entity.Infrastructure.DbUpdateConcurrencyException' occurred in EntityFramework.dll but was not handled in user code

because of rowVersion mismatch. Do I really need an explicit transaction for all my update apis? I thought the framework is supposed to do that for me.

hmm, just noticed that the error contains a link http://go.microsoft.com/fwlink/?LinkId=472540 telling me to catch the exception and try again -- in a loop! OMG — John Henckel, Apr 22 '16 at 21:16
This is not sql-server related question, please use tags properly. — FLICKER, Apr 22 '16 at 23:14
I don't think it has anything to do with transactions. EF does create a transaction for you when you SaveChanges(). You pull the data from the database in the meantime someone updates database and then you try to save your data on top of someone else changes. Because you are using rowVersion an exception is thrown so that you don't corrupt data. EF can't create a single transaction for your reads and writes since it would prevent everyone from using the db after you start reading and you may never have a write so you would block the database until the connection is closed. Reads don't need trx — Pawel, Apr 22 '16 at 23:18
Throwing `DbUpdateConcurrencyException` is the expected behavior if you choose an Optimistic Concurrency approach. There is nothing wrong in your code. More info here: http://www.asp.net/mvc/overview/getting-started/getting-started-with-ef-using-mvc/handling-concurrency-with-the-entity-framework-in-an-asp-net-mvc-application — Federico Dipuma, Apr 23 '16 at 09:30
@Pawel, actually it is a good idea to wrap read and write in a single transaction. That is what transactions were made for. A relational database uses https://en.wikipedia.org/wiki/Optimistic_concurrency_control which means that a transaction does not "block the database". If it does, you need to upgrade your database software. — John Henckel, Apr 24 '16 at 16:33

score 0 · Accepted Answer · edited May 23 '17 at 12:16

Since no one has answered, I will. Yes, WebAPI2 does not wrap the call in a transaction. That would be silly, if you think about it. Also the code

using (var db = new MyContext()) {
    // do stuff
}

does not implicitly create a transaction. Therefore, when you implement a RESTFUL PUT method to update your database, you have three options: (1) call db.SaveChanges() one time only and hope for the best, as the OP code, or (2) you can add a rowVersion column, and call db.SaveChanges() with try-catch in a loop, or (3) you can create an explicit transaction.

In my opinion, option 1 is evil, and option 2 is a terrible hack that was invented because transactions did not exist prior to EF6.

The correct way to implement Update:

[HttpPut, Route("ResponseSetStatus/{id:int}")]
public IHttpActionResult UpdateResponseSetStatus(int id, [FromUri] string status = null)
{
    using (var db = new MyContext(MyContext.EntityContextString))
    {
        using (var tran = db.Database.BeginTransaction()) 
        { 
            var responseSet = db.ResponseSets.FirstOrDefault(x => x.ResponseSetId == id);

            if (responseSet == null)
            {
                return NotFound();
            }

            // ADD ONE SECOND DELAY HERE FOR TESTING
            Thread.Sleep(1000);
            responseSet.Status = status;
            tran.Commit();
        }
    }
    return Ok();
}

Please note that try-catch is not necessary. If anything fails, the using tran will automatically rollback, and the WebAPI2 will send a nice 500 response to the client.

p.s. i put the db = new MyContext also in using, because it's the right way to do it.

Why doesn't WebAPI2 with Entity Framework automatically create a transaction for me?

1 Answers1