Locking with EF Code First

Question

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.

Answer 1

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.