DB Concurrency Control with .NET – Overview

This (long) posting provides guidelines for using common patterns to control concurrency when using relational databases (RDBMS) via ADO.NET or the .NET Entity Framework (EF). MS SQL Server and Oracle are used as examples to sensitize you for the implications of RDBMS specific concurrency model implementations.

When multiple sessions (users, processes) attempt to concurrently modify database data, modifications by one session can adversely affect modifications by simultaneous sessions or cause inconsistencies in the database(s). Some of the most common problems are:

  • Lost updates
  • Inconsistent queries
  • Bad response times
  • Insufficient scalability
  • Deadlocks
  • Timeouts

To meet the specific quality requirements consistency, concurrency, performance and scalability of an application the architect must balance the:

  • Application concurrency model (none, pessimistic, optimistic)
  • Transaction scoping and isolation levels
  • RDBMS concurrency model

Concurrency control design decisions are very difficult to change once you are well into development (or even in production) because of the deep and complete design decisions and tests necessary.

Table of Contents:

  • Concurrency control has many aspects
  • My general Approach
  • Part 1: Overview
    • RDBMS Concurrency Models
    • Transactions & Isolation Levels
    • Application Concurrency Control
      • Optimistic Control
      • Pessimistic Control
      • Using SQL Operations
  • Part 2: Know your Database System!
    • What to Know and Why
    • Locks, Blocking and Deadlocks
    • RDBMS Concurrency Models
      • Pessimistic Concurrency Mode
      • Optimistic Concurrency Mode
      • Choosing pessimistic (locking) or optimistic (versioning) mode
    • Transaction Isolation Levels
    • RDBMS Optimistic Update Conflicts
    • Stored Procedures
  • Part 3: DB Concurrency Control with .NET – Details
    • Intro
    • Optimistic Control (first write wins)
      • Conflict Detection
      • Conflict Resolution
    • Pessimistic Control (serialize updates)
      • Disconnected scenarios
      • Check-out Locking
      • Pessimistic Offline Lock
      • DB Lock with NOWAIT
    • No Control (last write wins)
      • Conflict Detection and Resolution
  • Books & Articles
  • Products & Versions used

Related Content:

Concurrency control has many aspects

With this post I want to sum up my experience, thoughts and research about handling concurrency issues using the current .NET technologies. In case you disagree with my findings, thoughts or recommendations: your comments are welcome!

The following mind map shows many aspects of DB concurrency control with .NET using Oracle and SQL Server:

DB Concurrency Aspects (Click to zoom)

My general Approach

Let me first give an overview of my general approach. Later I will explain the background of my recommendations and give detailed code examples for ADO.NET and EF.

My general approach for concurrency control is:

  1. Consciously select the the RDBMS concurrency model and
    transaction isolation level.
  2. Use optimistic locking via RowVersion in your application
    to prevent lost updates.
  3. Consider using pessimisic locking
    to improve performance in high-concurrency scenarios.
  4. Consider using SQL atomic operations, set-based operations and
    relative updates.

Part1: Overview

RDBMS Concurrency Models

Databases implement different concurrency models (see Know your Database System!):

Transactions & Isolation Levels

For most applications the isolation level Read Committed offers a good balance between data consistency and concurrency. For SQL Server consider using Read Committed Snapshot by enabling its optimistic row-versioning mode:

-- Enable SQL Server optimistic DB concurrency mode.
ALTER DATABASE ConcurrencyTest SET ALLOW_SNAPSHOT_ISOLATION ON
ALTER DATABASE ConcurrencyTest SET READ_COMMITTED_SNAPSHOT ON

The following picture shows how the different database concurrency modes maintain statement-level read-consistency under Read Committed isolation:

DB concurrency modes and read-consistency

We can use different approaches to use Transactions:

  • Use TransactionScope as a business layer construct
    to scope a set of business operations.
  • Use TransactionScope in the data access layer only
    to scope a set of database updates.
  • Use explicit transactions in the data access layer.
  • Use declarative transactions via Enterprise Services.
    Via the <Transaction> attribute.
    I do not use Enterprise Service for new applications.
  • Use WCF transactions.

I favor using TransactionScope in the business layer. It provides an elegant and convenient way to enclose a set of DB operations in a transaction:

Beware! The default isolation level of TransactionScope is Serializable, while the default in Oracle and SQL Server tools and via ADO.NET or EF is Read Committed.

For using WS-Atomic transactions with services on different platforms like Java see Truly Understanding .NET Transactions and WCF Implementation.

.NET does not support software transactional memory yet.

The Entity Framework implements “Requires Transaction” for SaveChanges: it automatically creates a new Read Committed transaction around SaveChanges() if not executing in the scope of a transaction.

Application Concurrency Control

While transactions do guarantee that a set of operations is either completely committed or rolled back and provide isolation between overlapping transactions, using transactions alone does not fully meet the specific data consistency requirements of most applications:

  • Depending on the RDBMS concurrency mode and the isolation level chosen, transactions alone often do not meet the data consistency requirements.
    Ex: lost updates can occur under read committed, when transactions base updates on previously retrieved values (~ SELECT…UPDATE constructs).
  • Even if transactions alone would meet all requirements,  there are scenarios in most architectures where it is not feasible to use transactions for protecting a complete set of operations  (Ex: web apps, Client/(stateless)Server apps, operation sets spanning user think-time).

Thus we must implement concurrency control logic in our applications (on top of the concurrency model of the database management system):

Application Concurrency Control on top of RDBMS Concurrency Model

Optimistic Control

My default implementation for optimistic control uses a row version column (automatically incremented by the database) and throws a DBConcurrencyException if it has changed since reading the row. For optimistic control to work, all applications (and interactive changes via SQL tools!) must check the row version on update.

SELECT ..., RowVer as RowVerOriginal FROM Inventories WHERE ProductID=42
'... some business logic
RowsAffected = UPDATE Inventories SET ...
               WHERE ProductID=42 AND RowVer=RowVerOriginal
IF RowsAffected = 0 then Throw New DBConcurrencyException

With the Entity Framework set ConcurrencyMode=Fixed on the row version column  to enable optimistic control via row version (and use self-tracking entities in multi-tier scenarios):

In UI scenarios ask the user to resolve update conflicts:

Try
  SaveShoppingCart()
Catch ex As OptimisticConcurrencyException
  MessageBox.Show("Data was modified by another user or a background process." & vbCrLf &
                  "Click 'Refresh' to show the current values and reapply your changes.",
                  "Concurrent update conflict", MessageBoxButton.OK)
End Try

In service scenarios, where you simply want to serialize units of work, or where there is no user to ask, automatically retry the complete unit of work (read+modify+save) if a conflict occurs:

DB.DoWithRetryOnConcurrencyConflict(
  Sub()
      Using tra = TransactionScopeFactory.CreateTransactionScope(
                                         TransactionScopeOption.RequiresNew,
                                         IsolationLevel=ReadCommitted)
         AssembleOrder()
         EnqueueOrder()
         tra.Complete()
      End Using
   End Sub)

My DB.RetryOnConflict(unitOfWork as Action) procedure encapsulates the retry logic and uses a random exponential back-off to avoid excessive retries and potential livelocks. For details see see DB Concurrency Control with .NET -Details.

Pessimistic Control

Consider using pessimistic control, serializing SELECT…UPDATE constructs by locking, to avoid the overhead of rollbacks and retries needed with optimistic locking.

In ADO.NET use:

  • Oracle: SELECT … FOR UPDATE
  • SQL Server: SELECT …WITH (UPDLOCK)

The Entity Framework (in contrast to NHibernate) does not offer pessimistic locking – except by executing raw SQL.

Consider using Check-out locking (~pessimistic offline lock) if neither holding DB Locks is a viable solution (because of user think time or a disconnected scenario) nor optimistic control (because losing all editing work is not acceptably for the victim of a conflict).

Using SQL Operations

Consider using SQL atomic operations, set-based operations and relative updates.

Use atomic SQL statements instead of SELECT…(IF)…UPDATE constructs.

‘Only send an Email if we succeed in setting leave approval request state to ‘Reminded’.
 If 1 = ctx.ExecuteStoreCommand("UPDATE LeaveApprovalRequests" &
                           " SET State = 'Reminded'" &
                           " WHERE ID = @ID AND State <>'Reminded'",
                           New SqlParameter("ID", 42))
         Then SendReminderEmail()

Solving the problem above in EF without using raw SQL is extremely cumbersome.

Use relational set operations instead of procedural loops. Use relative updates (SET Price * 0.8).

  ‘Change prices using a relative update.
  ctx.ExecuteStoreCommand("UPDATE Products" &
                                    " SET Price = Price * @Rise" &
                                    " WHERE Type = @Type",
                                    New SqlParameter("Rise", 0.8),
                                    New SqlParameter("Type", "Tires"))

Part 2: Know your Database System!

Part 3: DB Concurrency Control with .NET – Details

About these ads

About Peter Meinl

IT Consultant
This entry was posted in Computers and Internet and tagged , , , , , , , , , . Bookmark the permalink.

3 Responses to DB Concurrency Control with .NET – Overview

  1. Pingback: The Morning Brew - Chris Alcock » The Morning Brew #818

  2. Pingback: DB Concurrency Control with .NET – Details « DuongTienLam's Blog

  3. Pingback: DB Concurrency Control with .NET – Overview « DuongTienLam's Blog

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s