DataGrid - Preventing Concurrent Access To Rows

Question

I need to write a simple datagrid form which allows users to edit cells - The problem is that different people can edit this at EXACTLY the same time so concurrency becoames an issue

I am using data backed by a database

I seem to have two options here:

1) Continually poll the database and refresh data in the datagrid before allowing editing - This means that updates to the database need to take place just after editing rather then the preffered way of allowing edits, then letting users review and then commiting.

2) Allow dirty edits and then prevent users from commiting to the database the changes they want

Can anyone describe a mechansim which allows users to edit rows concurrently which would make the implementation easy??

EDIT : My question is how to implement this in C# - I have added lock columns but this is still not sufficent - If usera tries to edit row1 and commits changes userB tries to edit a stale version of row1 this will not get caught which is a BIG problem

Answer 1

you have these option but not limited to:

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

For reference and details see:

• Implementing Optimistic Concurrency

---

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); }

Pessimistic Concurrency

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();
}

Reference:

• LINQ to ADO.NET—Optimistic Concurrency Database Transactions

---

Answer 2

A simple way to implement optimistic locking is to add row version columns to your tables. (If your database server supports it, as SQL Server 2005 and 2008 do.)

When a user loads a record to edit it, get the record's row version.

When a user attempts to save their updates, get the record's row version again and compare it to version of the record the user edited. If they don't match, fail the changes and explain to the user what happened.

You can do the second step in several places - in your business layer, in the event handler that triggers the save, or in the stored procedure you use to update the record(s).

(This gets a bit more complicated if your data model is structurally different than the data model you present to users, but it isn't an insurmountable obstacle.)

Pessimistic locking is never as simple. To quote an excellent answer from 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.

You also need to implement a timeout mechanism for pessimistic locks so that negligent users can't lock records indefinitely.