Best Practise on Concurrency Issues in .Net Application

Question

Hi,

We are planning to upgrade an existing VB6 application to C# ( VS2008 ). ADO.Net follows more a disconnected strategy when accessing Data. I'm thinking about giving the Entity Framework a shot since using an ORM-tool is higly recommended if you want to speed up the development. And EF uses optimistic locking.

How do you solve the ancient problem when 2 users open the same 'customer' for example. ADO.net suggests to check whether the record has changed when you save it using the conflict exceptions.

In the old VB6 app we used to have 2 systems : - Keeping a bit called 'Locked' and a text-field called 'LockedBy' on the record This is very easy to implement and use but when the application crashes or there are problems with the network-connection, this record will still be locked. - Since we only use MSSQL as database, we have been using the rowlevel-locking feature of MSSQL for those customers that have an SQL 2000 or higher.

How is this handled in modern .Net application, how do you solve this ancient problem in .Net applications?

Any idea's,remarks or info are welcome ...

Regards, Sven Peeters

Answer 1

The most common implementation of Optimistic Locking is to have a Version column (typically a TIMESTAMP) in each table in addition to the Primary Key.

When you load data from the table, you must load the Version in memory and never change it (normally it's just going to be an opaque byte array anyway).

When you save the updated table, you start a very short-lived transaction and first read in the current value of the Version column and compare it to your in-memory value.

• If they are equal it means that no-one else modified the data and you can safely save it.
• If they are different it means that someone else modified the data since it was read, and you will have to resolve that conflict.

AFAIR, the Entity Framwork supports this type of Optimistic Locking out of the box.

Answer 2

Hi,

The locking issue still isn't clear for me. I find the .Net standard solution not user-friendly ( conflict management just before saving ). In many situations, the user will loose the changes he applied to the record.

We are now playing with NHibernate and manual locking of the record. By creating a global temp table called TableName.PrimaryKeyValue. After the save-operation, the table is dropped,if you close the connection, the table is dropped, if the application halts unexpectetly SQL Server will clean up after me. We are integrating this manual locking into the objects itself and in the object repository.

Regards, Sven Peeters