CODEWITHSUNDEEP
sql-server-2008entity-frameworkconcurrencyef-code-firstlocking
vtortola
vtortola

Reputation: 35905

Locking with EF Code First

I have a table that holds information about cars (let's call it tbl_incoming_car). That table has a non-unique column named 'customer_number', that shows the number of cars that have get into the system so far. The same car can get in and out lot of times, but this is registered only once.

So, when a new car gets in, I need to get the number of the last one, increment it, and then save it as the 'customer_number' of the new car.

I know that the easiest way would be to have a separated table for cars, have the 'customer_number' there, and register the ins and outs in other table, but this is just a silly example to expose the situation. So there is no point in discuss that the approach is wrong, I know that already :)

As I said, every time a new car enters into the system, I have to get the latest added row, get the 'customer_number', increment it and save it as an atomic operation. Other application instances could try to do the same, and the DB must hold requests for the last added row during the "creation task".

I thought that I would be able of doing it by setting the isolation level to serializable, but I think it won't prevent from reading the last row, but from inserting the new one. So it looks like locking is the solution. I have tried using a static object as Monitor in the code, and it works fine, but of course it is limited to the same application domain, I need something at the DB level.

I don't think there is anything in EF to set a lock on the DB, so which would be the best way of set up a lock on a table and release it later?

Thanks.

Upvotes: 3

Views: 3844

Answers (6)

galets
galets

Reputation: 18472

Solution previously mentioned on this thread may not be universally applicable, because EF runs sp_resetconnection before any SQL operation, so when you run anything invoiving SQL on the DbContext, it essentially releases the lock you were supposed to be holding.

What I came up with is: you need to clone SqlConnection before obtaining the lock, and hold on to this connection until you are ready to release:

public class SqlAppLock {
    DbConnection connection;
    public SqlAppLock(DbContext context, string resourceName)
    {
        ... (test arguments for null, etc)
        connection = (DbConnection)(context.Database.Connection as ICloneable).Clone();
        connection.Open();

        var cmd = connection.CreateCommand();
        cmd.CommandText = "sp_getapplock";
        ... (set up parameters)
        cmd.ExecuteNonQuery();

        int result = (int)cmd.Parameters["@result"].Value;
        if (result < 0)
        {
            throw new ApplicationException("Could not acquire lock on resource");
        }
    }

and then to release, could release the lock using sp_releaseapplock, or simply Dispose() the connection

Upvotes: 1

Goran Obradovic
Goran Obradovic

Reputation: 9051

EF works with C# TransactionScope.

In the past, I have been solving similar problem like this:

 using (var ts = new TransactionScope())
        {
            using (var db = new DbContext())
            {
                db.Set<Car>().Add(newCar);
                db.SaveChanges();
                // newCar.Id has value here, but record is locked for read until ts.Complete()

                newCar.CustomerNumber = db.Set<Car>().Max(car => car.CustomerNumber) + 1;
                db.SaveChanges();
            }
            ts.Complete();
        }

The line db.Set<Car>().Max(car => car.CustomerNumber) in any concurrent tasks will have to wait, because it needs to have access to all records, you can check this by adding breakpoint before ts.Complete() and trying to execute Select max(CustomerNumber) from dbname.dbo.Cars while code is paused on that line. Query will complete when you resume code and ts completes.

Of course, this only works if your example is good enough description for the real scenario you have, but it should be easy to adapt to your needs.

See transactions in ef on MSDN for more info.

Upvotes: 1

Bruno Matuk
Bruno Matuk

Reputation: 742

Using REPEATABLE READ isolation level can aquire lock at the moment you execute the SELECT command. How I don´t know then SELECT command to retrieve the last line, maybe it won´t work. The way you execute the SELECT change the way SQL Locks Lines/Indexes/Tables.

The best aprroach would be to execute a stored procedure. EF makes some roundtrips between the database and application that will increase the lock time and impact your application performance.

Maybe you could use a trigger to make the update after the insert.

Upvotes: 1

usr
usr

Reputation: 171178

Serializable actually does solve this. Serializable means that transactions behave as if they all took a global database X-lock. As if only one transaction executed at the same time.

This is also true for inserts. The locks will be taken in such as way as to prevent inserts at the wrong places.

You might not achieve deadlock-freedom though. Still worth a try.

Upvotes: 1

vtortola
vtortola

Reputation: 35905

So far, this is the best way I have came up with:

    public void SetTransactionLock(String resourceName)
    {
        Ensure.IsNotNull(resourceName, "resourceName");

        String command = String.Format(
        @"declare @result int;
          EXEC @result = sp_getapplock '{0}', 'Exclusive', 'Transaction', 10000 
          IF @result < 0
            RAISERROR('ERROR: cannot get the lock [{0}] in less than 10 seconds.', 16, 1);",resourceName);

        base.Database.ExecuteSqlCommand(command);
    }

    public void ReleaseTransactionLock(String resourceName)
    {
        Ensure.IsNotNull(resourceName, "resourceName");
        String command = String.Format("EXEC sp_releaseapplock '{0}';",resourceName);
        base.Database.ExecuteSqlCommand(command);
    }

Since there is no built-in way in EF, I added these two methods to my data layer, and I use them to declare that "critical section" where only one concurrent operation is allowed.

You can use it in a try finally block.

Upvotes: 3

phil soady
phil soady

Reputation: 11328

EF supports the SQL timeStamp also known as rowversion dataType.

This allows you to perform updates using optimistic locking. The framework does that for you if the fields are declared properly. Essentially :

UPDATE where ID=ID SET Customer_number to X+1 becomes Update where ID=ID and Rowversion = rowversion Set Customer_number =X+1

So if the row has changed since it was loaded in thread X, you get an error. You can reload and try again or take what ever action is appropriate in your scenario.

form more info see, http://msdn.microsoft.com/en-us/data/jj592904

and

http://msdn.microsoft.com/en-us/library/aa0416cz.aspx

and this SO post

What is a good method to determine when an entities data has been changed on the database?

Upvotes: 0

Related Questions