c#数据网格阻止对行的并发访问

Question

我需要编写一个简单的datagrid表单，允许用户编辑单元格-问题是不同的人可以在同一时间编辑单元格，因此并发性成为一个问题

我正在使用数据库支持的数据

我似乎有两个选择：

1）在允许编辑之前，不断轮询数据库并刷新datagrid中的数据-这意味着数据库更新需要在编辑之后进行，而不是以允许编辑的首选方式进行，然后让用户查看并提交

2）允许脏编辑，然后阻止用户向数据库提交他们想要的更改

有人能描述一种允许用户同时编辑行的机制吗？这将使实现变得简单

编辑：我的问题是如何在C#中实现这一点-我添加了锁列，但这仍然不够-如果usera尝试编辑行1并提交更改，userB尝试编辑行1的旧版本，这将不会被捕获，这是一个大问题

Answer 1

您可以选择但不限于：

Optimistic Concurrency - assume that while there may be concurrency conflicts every now and then, the vast majority of the time such conflicts won't arise; therefore, if a conflict does arise, simply inform the user that their changes can't be saved because another user has modified the same data

Pessimistic Concurrency - assume that concurrency conflicts are commonplace and that users won't tolerate being told their changes weren't saved due to another user's concurrent activity; therefore, when one user starts updating a record, lock it, thereby preventing any other users from editing or deleting that record until the user commits their modifications

有关参考和详细信息，请参见：

• Implementing Optimistic Concurrency

---

乐观并发

PeopleDataContext people = new PeopleDataContext();     
Person p = people.People.Single(person => person.ID == 1);     
p.IDRole = 2;

try
{ people.SubmitChanges(ConflictMode.ContinueOnConflict); }

catch (ChangeConflictException cce)
{ people.ChangeConflicts.ResolveAll(RefreshMode.KeepChanges); }

悲观并发

PeopleDataContext people = new PeopleDataContext();

using (TransactionScope t = new TransactionScope())
{
   Person p = people.People.Single(person => person.ID == 1);

   p.LastName = "Pessimistic";
   p.FirstName = "Concurrency";    

   people.SubmitChanges();     
   t.Complete();
}

参考：

• LINQ to ADO.NET—Optimistic Concurrency Database Transactions

---

Answer 2

实现乐观锁定的一种简单方法是向表中添加行版本列。（如果您的数据库服务器支持它，则为SQL Server 2005 and 2008 do。）

当用户加载记录进行编辑时，获取记录的行版本

当用户试图保存其更新时，再次获取记录的行版本，并将其与用户编辑的记录的版本进行比较。如果它们不匹配，则使更改失败，并向用户解释发生了什么

您可以在多个位置执行第二步—在业务层、触发保存的事件处理程序或用于更新记录的存储过程中

（如果您的数据模型在结构上与您提供给用户的数据模型不同，那么这会变得更加复杂，但这并不是一个不可逾越的障碍。）

悲观锁定从来没有这么简单。引用another stack overflow question的一个极好的答案：

[Pessimistic locking] requires you to be careful with your application design to avoid Deadlocks. To use pessimistic locking you need either a direct connection to the database (as would typically be the case in a two tier client server application) or an externally available transaction ID that can be used independently of the connection.

您还需要为悲观锁实现超时机制，以便疏忽的用户不能无限期地锁定记录