DB Concurrency Control with .NET – Details

[To put this post into perspective see DB Concurrency Control with .NET – Overview]

The following patterns are commonly used in applications to control database concurrency:

  • Optimistic Control
    • in UI Scenarios
    • in Service Scenarios
  • Pessimistic Control
    • SQL Locking Hints
    • Check-out Locking
  • No Control

Remember from DB Concurrency Control with .NET-Overview that the above patterns are used on top of the concurrency model of the RDBMS (see Know your Database System!):

It is a common misconception that pessimistic control in applications does not scale and optimistic control is always better than pessimistic. Especially in high-concurrency update scenarios pessimistic locking my well scale better, because it avoids the rollbacks and retries of optimistic control and writers do not block readers, when using optimistic RDBMS concurrency mode. I recommend basing your design decisions on the specific application requirements and on scalability tests.

The following picture shows application sessions using pessimistic and optimistic control on top of a RDBMS in optimistic (versioning) mode:

Applications using pessimistic and optimistic control on top of versioning RDBMS.

Optimistic Control (first write wins)

Optimistic control patterns check for concurrency conflicts on update by testing if another session has modified the data originally read. To make optimistic concurrency work, all applications and ad-hoc changes vial SQL tools must implement the optimistic concurrency check!

Optimistic control is the ADO.NET (DbCommandBuilder) default.
To enable optimistic control in Entity Framework use ConcurrenyMode=Fixed.

Optimistic control is commonly used:

  • In scenarios with low concurrency. When conflicts are rare, transactions can complete without the expense of managing locks and without having transactions wait for other transactions’ locks to clear.
  • In UI scenarios. It is generally not acceptable to hold a DB lock on data for the long duration of a user is editing it (think time).
  • In service scenarios with long-running transactions.

Conflict Detection

Conflicts are detected by checking if columns values have changed since they were read.  Commonly uses patterns are

  • Compare all or some columns.
  • Compare modified columns only.
  • Compare hash code of the row.
  • Compare the row version.
    Use integers for row versions! I discourage using time based row versions, because they are not fine grained enough for machine-speed updates (the SQL Server TimeStamp is an integer incremented with each change!).

My default is “compare row version” because it does not increase network traffic or bloat the where clause. To ensure that all updates by whatever tool or app do increment the row version, I automatically increment it in the database:

  • SQL Server: Use a column with data type ROWVERSION (=TIMESTAMP).
    Gets automatically incremented by SQL Server.
  • Oracle: Use a column with data type NUMBER.
    Using ORA_ROWSCN seems to be not dependable for this usage!
    Increment it via an update trigger using:

    • RowVer=RowVer+1
      If your app only needs RowVer for optimistic control (does not need real version numbers) you may limit the number of digits (ex: NUMBER(3) and cycle through the values using MODULO.
    • or using a Sequence

I prefer using a trigger with Oracle here, even though there is Trouble with Triggers. I use triggers with all databases to maintain ChangeTime and ChangedByUser columns in all tables anyway.

Consider comparing the row hash when you want to avoid schema changes.

Using “compare row version” with ADO.NET manual update commands:

 Dim daa As New SqlDataAdapter("SELECT ProductId, QuantityInStock, RowVer FROM Inventories WHERE ProductType=@ProductType", ConfigurationManager.ConnectionStrings("ConcurrencyTest_SQLServer").ConnectionString)
 daa.SelectCommand.Parameters.AddWithValue("ProductType", "Tool")
 Dim inventories As New DataTable
 daa.MissingSchemaAction = MissingSchemaAction.AddWithKey
 daa.Fill(inventories)
 Dim product = inventories.Rows.Find("Hammer")
 product!QuantityInStock = product!QuantityInStock + 200 'using Bang notation
 Try
  Using cnx As New SqlConnection(ConfigurationManager.ConnectionStrings("ConcurrencyTest_SQLServer").ConnectionString)
  cnx.Open()
  Dim cmd As New SqlCommand("UPDATE Inventories SET QuantityInStock=@QuantityInStock WHERE ProductId='Hammer'" &
  " AND [RowVer]=@RowVerOriginal", cnx)
  cmd.Parameters.AddWithValue("ProductId", product!ProductId)
  cmd.Parameters.AddWithValue("QuantityInStock", product!QuantityInStock)
  cmd.Parameters.AddWithValue("RowverOriginal", product!RowVer)
  If 0 = cmd.ExecuteNonQuery() Then Throw New DBConcurrencyException
  End Using
 Catch ex As DBConcurrencyException
   '...
 End Try

Using “compare row version” with SQLCommandBuilder (it is way better than its reputation!):

    Try
        Dim daa As New SqlDataAdapter("SELECT * from Inventories", ConfigurationManager.ConnectionStrings("ConcurrencyTest_SQLServer").ConnectionString)
        Dim inventories As New DataTable
        daa.Fill(inventories)
        inventories.Rows(0)!QuantityInStock = 200 'using Bang notation
        inventories.Rows(1)!QuantityInStock = 300
        Dim cmb As New SqlCommandBuilder(daa)
        cmb.ConflictOption = ConflictOption.CompareRowVersion
        daa.Update(inventories)
    Catch ex As DBConcurrencyException
        '...
    End Try

Sadly the OracleCommanbuilder does not support ConflictOption. I submitted a feature request for the Oracle Data Provider for .NET.

SQL Server ROWVERSION values are cumbersome to read. They convert nicely to bigint for displaying readable output when debugging:

SELECT Id, Value, RowVer FROM t1

Id    Value    rowver
1    a    0x0000000000023A76
2    b    0x0000000000023A77
3    c    0x0000000000023A78

SELECT Id, Value,CONVERT(bigint,RowVer) as RowVer FROM t1

Id    Value    RowVer
1    a    146038
2    b    146039
rowVerInt = BitConverter.ToInt64(oldRowVer.Reverse().ToArray(), 0))

To use “compare row version” with Entity Framework set ConcurrencyMode=Fixed on the row version column (plus use self-tracking entities in multi-tier scenarios) to enable optimistic control via row version:

When using “compare column values” you must test for NULL values too (because NULL=NULL is never true in SQL), resulting in ugly, bloated WHERE clauses:

UPDATE Inventories SET ProductType=:ProductType, QuantityInStock=:QuantityInStock
 WHERE ProductId='Hammer'
 AND (ProductType=:ProductTypeOriginal OR (ProductType is NULL AND ProductTypeOriginal is NULL))
 AND (QuantityInStock=:QuantityInStockOriginal OR (QuantityInStock is NULL AND :QuantityInStockOriginal is NULL))

In Oracle you can use DECODE as a shorthand:

UPDATE Inventories SET ProductType=:ProductType, QuantityInStock=:QuantityInStock
 WHERE ProductId='Hammer'
 AND DECODE(ProductType,:ProductTypeOriginal,1) = 1
 AND DECODE(QuantityInStock,:QuantityInStockOriginal,1) = 1

Remember from Know your Database System! that  Oracle (with serializable isolation only!) and SQL Server (with Snapshot isolation only!) might throw update conflict exceptions which your application must handle too:

  • Oracle: ORA-08177 can’t serialize access for this transaction”.
  • SQL Server: Msg 3960, Snapshot isolation transaction aborted due to update conflict.

Catching concurrency exceptions when using Oracle Serializable or SQL Server Snaphot isolation:

  ...
  Catch (ex) as Exception
     If IsConcurrencyConflict(ex) then
        ...
     End If
    Private Shared Function IsConcurrenyConflict(ex As Exception) As Boolean
        If TypeOf (ex) Is OptimisticConcurrencyException Then Return True
        If TypeOf (ex) Is DBConcurrencyException Then Return True
        If TypeOf (ex) Is SqlException Then Return IsConcurrenyConflict(CType(ex, SqlException))
        If TypeOf (ex) Is Oracle.DataAccess.Client.OracleException Then Return (IsConcurrenyConflict(CType(ex, OracleException)))
        If TypeOf (ex) Is System.Data.UpdateException Then
            Dim sqlException = TryCast(ex.InnerException, SqlException)
            If sqlException IsNot Nothing Then Return IsConcurrenyConflict(sqlException)
            Dim oracleException = TryCast(ex.InnerException, OracleException)
            If sqlException IsNot Nothing Then Return IsConcurrenyConflict(oracleException)
        End If
        Return False
    End Function

    Private Shared Function IsConcurrenyConflict(ex As SqlException) As Boolean
        'TODO Verify and complete exception numbers

        Select Case ex.Number
            Case 3960 'Msg 3960: Snapshot isolation transaction aborted due to update conflict.
                Return True
            Case 1205 'deadlock victim
                Return True
            Case -2, 1222 'lock timeout
                Return True
            Case Else
                Return False
        End Select
    End Function

    Private Shared Function IsConcurrenyConflict(ex As OracleException) As Boolean
        Select Case ex.Number
            Case 8177 'ORA-08177 can't serialize access for this transaction
                Return True
            Case 60 'ORA-00060: deadlock detected while waiting for resource
                Return False 'FALSE! because a deadlock in Oracle usually is an app bug
            Case Else
                Return False
        End Select
    End Function

Conflict Resolution

Commonly uses patterns for conflict resolution are:

  • Give up.
  • Display the problem and let the user decide.
  • Retry.
  • Merge the changes.
    Merging the changes if conflicting transactions changed different columns should only be used when then column values are independent from an application logic point of view. Ex: A user might change the “ShippingMethod” based on what he sees in “ProductWeight”, while another user concurrently changes the “ProductWeight”.
  • Log the problem so someone can decide later.
  • Ignore the collision and overwrite.
    Rarely useful – better user “No Control”.

My general approach to conflict resolution is:

In UI scenarios ask the user to resolve update conflicts by looking at the new DB values and deciding which changes he wants to reapply:

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

This is how LightSwitch asks the user to resolve conflicts:

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)

DB.DoWithRetryOnConcurrencyConflict(unitOfWork as Action) encapsulates the retry logic and uses a random exponential back-off to avoid excessive retries and potential livelocks. The implementation was inspired by [Retry]. TransactionScopeFactory encapsulates the otherwise distracting lengthy isolation level specification:

Class DB
    Shared Sub DoWithRetryOnConcurrencyConflict(ByVal unitOfWork As Action)
        Const maxRetries As Integer = 3
        Dim tryCount = 0
        Do
            Try
                unitOfWork()
                Exit Do
            Catch ex As Exception
                If tryCount = maxRetries Then Throw
                If Not IsConcurrenyConflict(ex) Then Throw
                tryCount += 1
                Dim backoffTime = ComputeBackoffTime(tryCount)
                'Debug.Print("{0}. Will do retry no {1} in {2} ", ex.GetType, tryCount, backoffTime)
                Console.WriteLine("{0}. Will do retry no {1} in {2} ", ex.GetType, tryCount, backoffTime)
                'RaiseEvent PerformingRetry(Me, New RetryEventArgs With{.TryCount=tryCount, .BackoffTimeSpan=backoffTimeSpan)
                System.Threading.Thread.Sleep(backoffTime)
            End Try
        Loop
    End Sub

   Private Shared Function ComputeBackoffTime(ByVal tryCount) As TimeSpan
        Dim fastFirstRetry = True
        Dim minBackoff = TimeSpan.FromMilliseconds(100), maxBackoff = TimeSpan.FromSeconds(5), deltaBackoff = TimeSpan.FromMilliseconds(100)

        If fastFirstRetry And tryCount = 1 Then Return TimeSpan.Zero
        'Compute random exponential retry back-off time
        Dim random = New Random(Guid.NewGuid().GetHashCode())
        Dim backoffTime As Integer = (Math.Pow(2.0, tryCount) - 1.0) * random.Next(deltaBackoff.TotalMilliseconds * 0.8, deltaBackoff.TotalMilliseconds * 1.2)
        Return TimeSpan.FromMilliseconds(Math.Min(backoffTime + minBackoff.TotalMilliseconds, maxBackoff.TotalMilliseconds))
    End Function
End Class

In addition to concurrency scenarios, retries may be needed for other transient conditions too:

The retry logic is silent by design. To enable monitoring the application for concurrency hotspots, retries should be traced. I addition it might be helpful for the retry class to expose an OnRetry event (with CancelEventArgs ?) to enable the caller to implement custom actions on retries.

In high conflict scenarios, the cost of repeatedly rolling back and retrying operations may hurt performance and scalability significantly, so using pessimistic control might be a better solution.

If retries do significantly lengthen the response time experienced by users, always consider informing  users and offering a cancel, instead of silent automatic retries, thus giving them a feeling of control. Users develop an expectation for typical response times and become impatient when deviations happen. Remember how you feel, when Windows Explorer hangs on trying to navigate to a non-existent network folder, while you can see that you simply mistyped the folder name and there is nothing you can do about it but kill Explorer or even reboot.

Pessimistic Control (serialize updates)

Pessimistic control patterns lock resources via DB locks or check-out logic to serialize operations.

Pessimistic control is commonly used

  • In scenarios with high concurrency where the cost of managing locks and transactions waiting for other transactions’ locks is less than the cost of rolling back and retrying transactions if concurrency conflicts occur.
  • In UI scenarios where optimistic control is not suitable because users might lose too much work when losing a concurrency conflict.
  • Not in disconnected scenarios (stateless client/server, web application, connection pooling).
    Because DB locks are not held across connections to use pessimistic control in disconnected scenarios one must either keep the connection open for the duration of the SELECT…UPATE or encapsulate it in a transaction.

Disconnected scenarios require special attention with pessimistic concurrency :   Because DB locks are not held across connections, we must either keep  the connection open for the complete set of operations (Ex:   SELECT…UPDATE) or encapsulate it in a transaction. In disconnected scenarios like Client/(stateless)Server apps, web applications or with operation sets spanning user think-time this generally not feasible and optimistic locking should be used instead.

Pessimistic control can be implemented via

  • SQL locking hints
    • Oracle: SELECT … FOR UPDATE
    • SQL Server: SELECT …WITH (UPDLOCK)
  • Check-out Locking
  • Application defined locks (~ coarse-grained lock)
    • Oracle: DBMS_LOCK.
      Mainly used to serialize access to external resources (ex: writing to files).
    • SQL Server: sp_getapplock.
      Can be used to simplify locking or to reduce lock resources.

Pessimistic control using SQL hints:

Using cnx As New OracleConnection(My.Settings.OracleConnectionString)
  cnx.Open()
  Dim selectCmd As New OracleCommand("SELECT Price FROM Inventories" &
                                     " WHERE Product_ID=:ProductID FOR UPDATE", cnx)
  selectCmd.Parameters.Add("ProductID", ProductID)
  Price = CLng(selectCmd.ExecuteScalar)
  NewPrice = ComputeNewPrice '...some business logic
  Dim updateCmd As New OracleCommand("UPDATE Inventories" &
                                     " SET Price=:Price" &
                                     " WHERE Product_ID=:ProductID", cnx)
  updateCmd.Parameters.Add("Price", NewPrice)
  updateCmd.Parameters.Add("ProductID", ProductName)
  updateCmd.ExecuteNonQuery()
End Using

The Entity Framework (in contrast to NHibernate) does not offer pessimistic locking – except by executing raw SQL via Context.ExecuteStoreCommand(). Mixing EF access with raw SQL is ugly.

While pessimistic control prevents update conflicts by serializing SELECT…UPDATE constructs via locking, with Oracle Serializable or SQL Server Snaphot isolation the RDBMS might still throw update conflict exceptions (see Know your Database System!) and you must use the same conflict detection and resolution logic as with optimistic control.

Check-out Locking

Check-out patterns should be considered when neither holding DB Locks is a viable solution (ex: because of user think time or a disconnected scenario) nor optimistic control (ex: because losing all update work is not acceptably for the victim):

Check-out locking can be implemented via

I often use pessimistic offline locks.

Pessimistic Offline Lock

A pessimistic offline lock serializes operations via a CheckedOutBy column managed by custom application logic. In addition to the conflict detection logic a timeout for the check-out and an admin function to clear orphaned check-outs should be implemented:

Try
  If 0 = ctx.ExecuteStoreCommand("UPDATE CadDocuments" &
      " SET set CheckedOutBy = @UserId, CheckedOutExpirationTime = @CheckedOutExpirationTime" &
      " WHERE ProductID = @ProductID" &
      " AND (CheckedOutBy is null OR CheckedOutBy = @UserId OR CheckedOutExpirationTime < @CheckedOutExpirationTime",
      New SqlParameter("ProductID", ProductID),
      New SqlParameter("UserId", CurrentUser.UserID),
      New SqlParameter("CheckedOutExpirationTime", DateTime.Now.AddDays(4))
   ) Then Throw New DBConcurrencyException
   '...
Catch ex As DBConcurrencyException
  MessageBox.Show("Data is already checked out for editing by another user!" & Environment.NewLine &
                  "Please try again later.")
End Try

DB Lock with NOWAIT

Locking with Nowait prevents blocking by using SQL hints:

  • Oracle: SELECT…FOR UPDATE NOWAIT
    and handle exception:
    ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
  • SQL Server: SELECT…WITH (UPDLOCK, NOWAIT)
    and handle exception:
    Msg 1222 : Lock request time out period exceeded

The application can catch the “resource busy” exception and retry later. Be aware that in contrast to pessimistic offline locks you are holding locks in the DB.

No Control (last write wins)

Nothing is implemented to detect or control concurrency conflicts. The last write simply wins. Lost updates can happen silently.

No control is the Entity Framework default (optimistic control must be enabled manually  per entity).

No control should only be used if concurrency conflicts never occur or the  cost of inconsistencies is acceptable. “Concurrent updates happen very  rarely ” is not a valid argument here! The cost of a single lost update can be extremely high (ex: cost a life or store a wrong  balance).

Conflict Detection and Resolution

While the application itself implements no logic to detect conflicts, with Oracle Serializable or SQL Server Snaphot isolation the RDBMS might still throw update conflict exceptions (see Know your Database System!) and you must use the same conflict detection and resolution logic as with optimistic control.

Books & Articles

[ExpOra]
Expert Oracle Database Architecture: 9i and 10g Programming Techniques and Solutions,
Thomas Kyte

[SQLInt]
Microsoft SQL Server 2008 Internals (Pro – Developer),
Kalen Delaney

[Retry]
Retry patterns:

Products & Versions

This post is based on the following products and versions:

  • Visual Studio 2010 SP1
  • .NET Framework 4
    • Entity Framework 4
    • System.Data.SQLClient
    • System.Data.OracleClient
      Is marked as deprecated.
  • Oracle.DataAccess.Client
    Oracle Data Provider for .NET 4 11.2.0.2.30  Beta
  • Oracle EF provider
    ODAC 11.2.0.2.30 Beta for Entity Framework and LINQ to Entities
  • MS SQL Server 2008 R2
  • Oracle Database 11g Release 2

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 – Details

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

  2. Pingback: DB Concurrency Control with .NET – Overview – Know your Database System « DuongTienLam's Blog

  3. Pingback: DB Concurrency Control with .NET – Details « 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