Best practice to lock a record for editing while using entity framework

Question

Not quite sure how to word this question but here it goes. I am working on a project where multiple client applications are accessing one data source through a WCF service. It may not be relevant but the WCF service is utilizing entity framework to access this data source. Whenever a client has queried a record for editing, I would like to prevent that same record from being edited by the rest of the clients till the first client has completed their update.

Correct me if I am wrong but I believe this is also known as synchronous and asynchronous data access.

My question is, what is the industry best practice to implement this functionality. Is there a way to control this from the database side (Using SQL) or must it be done through the client?

I have considered including a boolean 'EditMode' column for each table and simply setting it to true when it is being edited and check if that is set to true before allowing another client to access that record.

Answer 1

Best practice is to use RowVersion and Optimistic locking.

Optimistic Concurrency Patterns explained.

If using Code first, then include a field in your POCO.

public virtual byte[] RowVersion { get; set; }

EF will add a Timestamp/RowVersion property to your table. It will be checked during Update. And automatically updated by the DB when changed.

EDIT: to better explain.

What EF is looking for is properties that are concurrency fields so you can actually control the concurrency with one or more fields.

entity.Property(p => p.RowVersion).IsConcurrencyToken()

when performing an update or delete you catch the defined exception

catch (DbUpdateConcurrencyException ex)

EF treats the RowVersion as a concurrency token. This is the Generally used approach. Since SQLServer will automatically update this field type for you. So very fast and easy. But you can tell EF a property is a concurrency token explicitly and have more than one.

So EF should add properties to the where clause for updates and deletes to make sure the record didn't change since accessed.