Reputation: 31
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
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
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
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 :
Hope that helps - let me know if there's anything specific you need clarifying!
Happy Coding,
Cheers,
Chris.
Upvotes: 0