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
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
- 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
- My Advice for .NET Application Architecture and Design
- Use the .NET TransactionScope
- Stored Procedures Pros & Cons
- Avoid unwanted Escalation to Distributed Transactions
- The SQLCommandBuilder is way Better than its Reputation
- .NET Database Access Options
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:
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:
- Consciously select the the RDBMS concurrency model and
transaction isolation level.
- Use optimistic locking via RowVersion in your application
to prevent lost updates.
- Consider using pessimisic locking
to improve performance in high-concurrency scenarios.
- Consider using SQL atomic operations, set-based operations and
RDBMS Concurrency Models
Databases implement different concurrency models (see Know your Database System!):
- Oracle has always used its optimistic multi-versioning.
- SQL Server defaults to pessimistic locking and
offers optimistic row-versioning.
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:
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:
- Lightweight Transactions (not using the DTC) against single databases
Works with SQL Server 2008 and Oracle 11g R 1.
See Avoid unwanted escalation to distributed (DTC) transactions.
- Distributed Transactions (using the DTC)
- spanning multiple databases or WCF service calls.
- spanning different resource manager types
(DB, MSMQ, File System, Registry).
See Using the .NET TransactionScope
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):
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.
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"))