domozi
domozi

Reputation: 31

Entity Framework transaction

I have a program which is running in duplicate on two servers. I need to select and update DB records in the program, so I need a table lock or transaction from EF, otherwise each copy of program can select and modify same records. While the first copy make DB changes the other one shouldn't run the same code section.

I found TransactionScope in EF, but it isn't working properly, because while the first copy is running I can make many select and update on that tables in SQL Server MGM studio.

I have a short code snippet, please validate it:

using (TransactionScope transaction = new TransactionScope())
{
    //select some records which aren't locked by the other copy of the program
    //condition: Locked==null
    recipientsList = (from c in context.Recipients
                      where
                          c.SentToPlatform == false && c.PopupID != null &&
                          c.Message.MessageStatus == 2 && c.Locked == null
                      select c).Take(piecePerMinute).ToList();

    foreach (var recipient in recipientsList)
    {
        //i need make some changes on the record, prevent it from the other copy of program
        //I need to change locked column to true
        recipient.Locked = true;
        recipient.LockBy = ipAddress;
        Console.Write("I");
        Thread.Sleep(1000);
    }

    //close transaction
    try
    {
        context.SaveChanges();
        transaction.Complete();
    } catch (Exception ex )
    {


    }
}

Upvotes: 2

Views: 6814

Answers (3)

EBarr
EBarr

Reputation: 12026

Technically what you're asking for is a long(er) running transaction with a higher isolation level than Read Committed (the default level). There isn't a enough informaiton for me to know if you want RepeatableRead or Serialzable (to avoid phantom inserts).

You can accomplish what you're asking by doing something like this :

var opt = new TransactionOptions();
    opt.IsolationLevel = IsolationLevel.Serializable;

using (var scope = new TransactionScope(TransactionScopeOption.Required, opt) ){

   //read table code

   //write table code

   //save context & complete scope

}

With that said, I highly doubt this is what you actually want. Serializable transactions can leave large parts of your database locked. What does that mean? Here is how Microsoft describes the serializable transaction :

SERIALIZABLE specifies the following:

  • Statements cannot read data that has been modified but not yet committed by other transactions.
  • No other transactions can modify data that has been read by the current transaction until the current transaction completes.
  • Other transactions cannot insert new rows with key values that would fall in the range of keys read by any statements in the current transaction until the current transaction completes.

Range locks are placed in the range of key values that match the search conditions of each statement executed in a transaction.This blocks other transactions from updating or inserting any rows that would qualify for any of the statements executed by the current transaction.

....

Because concurrency is lower, use this option only when necessary.

As @Bertie points out, entity framework is built around an optimistic concurrency model. There are lots of reasons for using optimistic concurrency (OC) and tons of patterns for dealing with the inevitable collisions. OC makes you taller and funnier. Using serializalbe transactions for everything will leave you like Bruce Willis in 12-monkeys --- stuffed with gobs of Thorazine in a straight jacked drooling all over the floor of your padded room. You don't want that, now do you ?

Upvotes: 2

stylefish
stylefish

Reputation: 571

you need to manually lock the table via SQL. i found this post, i think its what you need. but its not that satisfying..

Locking a table with a select in Entity Framework

EDIT:

        using (var ts = new TransactionScope())
        {
            var db = new Db();

            var asd = from x in db.MyTable
                      where x.Id == 1
                      select x;

            asd.First().Name = "test2";
            db.SaveChanges();    // if you're here with the debugger the table is locked
        } 
        // and now its unlocked and you can do a select again

internal class Db : DbContext
{
    public Db()
    {
        Database.DefaultConnectionFactory = new SqlConnectionFactory();
        //Database.SetInitializer(new DropCreateDatabaseIfModelChanges<Db>());
        Database.Connection.ConnectionString =
            "yourconnectionstring;";
    }
    public DbSet<MyTable> MyTable { get; set; }
}

internal class MyTable
{
    public int Id { get; set; }
    public string Name { get; set; }
    public DateTime MyTime { get; set; }
}

EDIT2:

i played around with EF and TransactionScope a little here are my results:

using (var ts = new TransactionScope())
{
    var db = new Db();

    var asd = from x in db.MyTable
                where x.Id == 1
                select x;

    db.SaveChanges(); // you still can fire selects in the studio

    asd.First().Name = "test2"; // now a change is made but not written to the transaction
    db.SaveChanges(); // after this call you can't fire selects in the management studio, the table is locked

    var asd2 = from x in db.MyTable
                where x.Id == 1
                select x;

    asd2.First().Name = "test3";
    db.SaveChanges(); // the table still is locked
}
// now you can do selects again, the table is unlocked

Upvotes: 1

Bertie
Bertie

Reputation: 733

What this boils down to is Optimistic Concurrency. You need a way of ensuring each application knows when the data has changed underneath it and then creating a strategy of dealing with it. I highly suggest reading this :

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

And then these if they help :

http://blogs.msdn.com/b/cesardelatorre/archive/2008/09/04/updating-data-using-entity-framework-in-n-tier-and-n-layer-applications-short-lived-ef-contexts.aspx

http://blogs.msdn.com/b/cesardelatorre/archive/2008/09/05/optimistic-concurrency-updates-using-entity-framework-in-n-tier-and-n-layer-applications-part-2.aspx

Hope that helps - let me know if there's anything specific you need clarifying!

Happy Coding,
Cheers,
Chris.

Upvotes: 0

Related Questions