问题描述
我有一个在两台服务器上重复运行的程序。
我需要选择和更新程序中的DB记录，所以我需要一个表锁或从EF的事务，否则每个程序的副本可以选择和修改相同的记录。当第一个副本使DB更改时，另一个不应该运行相同的代码部分。
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.
我在EF中找到了TransactionScope，但它不能正常工作，因为在第一个副本运行时，我可以对SQL Server MGM中的表进行许多选择和更新工作室。
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 )
{
}
}
推荐答案
技术上您要求的是长时间运行的事务，读提交（默认级别）。没有足够的信息让我知道，如果你想要RepeatableRead或Serialzable（避免幻影插入）。
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
}
这是您实际想要的。可序列化事务可能会使大部分数据库被锁定。这意味着什么？下面是Microsoft如何描述可序列化的事务：
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 :
- 语句无法读取已被其他事务修改但尚未提交的数据。
- 没有其他事务可以修改当前事务读取的数据，
- 其他事务不能插入新行，其中键值将落在当前事务中的任何语句读取的键范围内，直到当前事务完成。
范围锁定位于与事务中执行的每个语句的搜索条件匹配的键值范围内。这会阻止其他事务更新或插入任何符合当前
事务执行的任何语句的行。
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.
正如@Bertie指出的，实体框架是围绕一个乐观并发模型构建的。使用乐观并发（OC）和大量模式来处理不可避免的冲突有很多原因。 OC让你更高更有趣。使用serializalbe交易的一切将离开你像布鲁斯威利斯在12只猴子 - 塞满了Thorazine的gobs在一个直杰克流口水在你的填充房间的地板。你不想要，现在是吗？
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 ?
这篇关于实体框架事务的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！