Database Concurrency and Row Level Versioning in SQL Server 2005

By Kalen Delaney, Fernando Guerrero

On This Page

Database Concurrency Overview
Conclusion

Database Concurrency Overview

Modern relational database systems potentially allow hundreds, if not thousands, of simultaneous connections to the data. The architecture of the database system itself determines the various ways that concurrent access to the same data can be managed with as little interference between users or connections as possible. Most database systems provide features that an application developer can use to exert some control over how concurrent access is managed, allowing the developer to find a balance between concurrency and data consistency.

Microsoft® SQL Server™ 2005 includes a new technology called Row Level Versioning (RLV) that allows concurrent access to be handled in new ways. Many features of SQL Server 2005 are designed around RLV and no additional application control is necessary in order to take advantage of this new capability. For other features, such as new isolation levels, a Database Administrator must specifically allow RLV on a database by database basis. This allows backward compatibility to be maintained for those applications that depend on the locking behavior in previous SQL Server versions.

This white paper focuses on concurrency enhancements in SQL Server 2005. On the server side, it covers all the features of SQL Server that leverage RLV technology. These include the new features: Snapshot Isolation, Multiple Active Result Sets (MARS) and Online Index Rebuild. RLV is also used in SQL Server 2005 to support database triggers, so the differences in trigger behavior between SQL Server 2000 and 2005 are also discussed. On the client side, concurrency enhancements covered include concurrency in CLR objects, transaction control from the new SQL Native Client, Windows Enterprise Services and queued components, and concurrency using Service Broker enabled applications.

One of the main benefits of RLV and the client-side enhancements is that SQL Server can now provide higher levels of database concurrency with equivalent or better data consistency. This paper will describe the pre-existing concurrency features only in order to compare the new features with the existing ones.

Database Concurrency Definition

Concurrency can be defined as the ability for multiple processes to access or change shared data at the same time. The greater the number of concurrent user processes that can execute without blocking each other, the greater the concurrency of the database system.

Concurrency is impacted when a process that is changing data prevents other processes from reading the data being changed or when a process that is reading data prevents other processes from changing that data. Concurrency is also impacted when multiples processes are attempting to change the same data concurrently and they cannot all succeed without sacrificing data consistency.

How a database system addresses situations that decrease concurrency is partly determined by whether the system is using optimistic or pessimistic concurrency control. Pessimistic concurrency control works on the assumption that there are enough data modification operations in the system to make it likely that any given read operation will be affected impacted by a data modification made by another user. In other words, the system is being pessimistic and assuming a conflict will occur. The default behavior when using pessimistic concurrency control is to use locks to block access to data that another process is using. Optimistic concurrency control works on the assumption that there are few enough data modification operations to make it unlikely (although possible) that any process will modify data that another process is reading. The default behavior when using optimistic concurrency control is to use row versioning to allow data readers to see the state of the data before the modification took place.

Historical Behavior

Historically, the concurrency control model in SQL Server at the server level has been pessimistic and based on locking. While locking is still the best concurrency control choice for most applications, it can introduce significant blocking problems for a small set of applications.

The biggest problem arises when locking causes the writer-block-reader or reader-block-writer problem. If a transaction changes a row, it holds exclusive locks on the changed data. The default behavior in SQL Server is that no other transactions can read the row until the writer commits. Alternatively, SQL Server supports ‘read uncommitted isolation’, which can be requested by the application either by setting the isolation level for the connection or by specifying the NOLOCK table hint. This nonlocking scan should always be carefully considered prior to use, because it is not guaranteed to return transactional consistent results.

Prior to SQL Server 2005, the tradeoff in concurrency solutions has been that we can avoid having writers block readers if we are willing to risk inconsistent data. If our results must always be based on committed data, we needed to be willing to wait for changes to be committed.

Overview of Row Level Versioning

Even if the application requires that results must always be based on committed data, there are still two possibilities. If the reader absolutely must have the latest committed value of the data it is correct for readers to wait (on a lock) for writers to complete their transactions and commit their changes. In other situations, it might be sufficient just to have committed data values, even if they are not the most recent versions. In this case, a reader might be fine if SQL Server could provide it with a previously committed value of the row, that is, an older version.

SQL Server 2005 introduces a new isolation level called ‘snapshot isolation’ (SI) and a new non-locking flavor of read-committed isolation, call ‘read committed snapshot isolation’ (RCSI). These row-versioning based Isolations levels allow the reader to get to a previously committed value of the row without blocking, so concurrency is increased in the system. For this to work, SQL Server must keep old versions of a row when it is updated. Because multiple older versions of the same row may need to be maintained, this new behavior is also called multi-version concurrency control or row level versioning.

To support storing multiple older versions of rows, additional disk space is used from the tempdb database. The disk space for version store needs to be monitored and managed appropriately.

Versioning works by making any transaction that changes data keep the old versions of the data around so that a ‘snapshot’ of the database (or a part of the database) can be constructed from these old versions.

When a record in a table or index is updated, the new record is stamped with the transaction sequence_number of the transaction that is doing the update. The previous version of the record is stored in the version store, and the new record contains a pointer to the old record in the version store. Old records in the version store may contain pointers to even older versions. All the old versions of a particular record are chained in a linked list, and SQL Server may need to follow several pointers in a list to reach the right version. Version records need to be kept in the version store only as long as there are there are operations that might require them.

In the following figure, the current version of the record is generated by transaction T3, and it is stored in the normal data page. The previous versions of the record, generated by transaction T2 and transaction Tx are stored in pages in the version store (in tempdb).

Figure 1: Versions of a Record

Figure 1: versions of a record

Row level versioning gives SQL Server an optimistic concurrency model to work with when the needs of an application require it or when the concurrency reduction of using the default pessimistic model is unacceptable. To switch to the row-versioning based isolation levels, the tradeoffs of using this new concurrency model need to be carefully considered. In addition to extra management requirements to monitor the increased usage of tempdb for the version store, using versioning will slow the performance of update operations, due to the extra work involved in maintaining old versions. Update operations will bear this cost, even if there are no current readers of the data. If there are readers using row level versioning, they will have the extra cost of traversing the link pointers to find the appropriate version of the requested row.

In addition, because the optimistic concurrency model of snapshot isolation assumes (optimistically) that there will not be many update conflicts occurring, you should not choose the SI isolation level if you are expecting contention for updating the same data concurrently. Snapshot isolation works well to enable readers not to be blocked by writers, but simultaneous writers are still not allowed. In the default pessimistic model, the first writer will block all subsequent writers, but using SI, subsequent writers could actually receive error messages and the application would need to resubmit the original request. Note that these update conflicts will only occur with SI, and not with the enhanced read-committed isolation level, RCSI. In addition, there are guidelines you can follow to reduce update conflicts when using Snapshot Isolation.

For more details about using Row Level Versioning to support Snapshot Isolation, please see Kimberly Tripp’s white paper: SQL Server 2005 Snapshot Isolation, https://msdn2.microsoft.com/en-us/library/ms345124.aspx

Additional SQL Server 2005 Features utilizing Row Level Versioning

Although the motivation behind adding row level versioning to SQL Server 2005 was to maintain a version store for optimistic concurrency, and to support row-versioning based isolation levels to solve the problem of data writers blocking all readers, there are other SQL Server 2005 features that take advantage of this new data management mechanism. Two of these features, multiple active result sets (MARS) and online index rebuilds, are new to the product; the third is a new way of managing triggers, which are an existing feature. This section provides an overview description of the use of row level versioning for these SQL Server features.

Triggers and Row Level Versioning

Triggers have been a part of SQL Server since the earliest version and they were the only feature of the product prior to SQL Server 2005 that gave us any type of historical (or versioned) data. One of the special features of triggers is the ability to access a pseudo-table called ‘deleted’. If the trigger is a DELETE trigger, the ‘deleted’ table contains all the rows which were deleted by the operation that caused the trigger to fire. If the trigger is an UPDATE trigger, the ‘deleted’ table contains the old version of the data in all the rows changed by the update statement that caused the trigger to fire, in other words, the data before the update took place. Previous versions of SQL Server would populate the deleted table by scanning the transaction log looking for all the log records in the current transaction that changed the table to which the trigger was tied. Scanning log records can be very expensive, because the transaction log is optimized for writing, not reading. For a high volume OLTP system in which log records for the current transaction may have already been written to disk, this could incur actual physical I/O operations. The new mechanism can then help the performance of your existing triggers.

In SQL Server 2005, the deleted table is materialized using row level versioning. When updates or deletes are performed on a table that has a relevant trigger defined, the changes to the table are versioned, regardless of whether or not row-versioning based isolation levels have been enabled. When the trigger needs to access the ‘deleted’ table, it retrieves the data from the version store. New data, whether from an update or an insert, is accessible through the ‘inserted’ table. When a SQL Server 2005 trigger scans inserted, it looks for the most recent versions of the rows.

Because of the fact that tempdb is used for the version store, applications that make heavy use of triggers in SQL Server 2000 need to be aware that there may be increased demands on tempdb after upgrading to SQL Server 2005.

Online Index Creation and Row Level Versioning

Index creation and rebuilding are obviously not new features of SQL Server, but in SQL Server 2005, we can now build, or rebuild, an index without taking the table or index offline. In previous versions, building or rebuilding a clustered index would exclusively lock the entire table so that all the data was completely inaccessible. Building or rebuilding a nonclustered index would place a shared lock on the table, so that data could be read but not modified. In addition, while rebuilding a nonclustered index, the index itself was completely unusable and queries that might have used the index would exhibit degraded performance.

With Row Level Versioning, SQL Server 2005 allows indexes to be built or rebuilt completely online. As the index is being built, SQL Server scans the existing table for the version of the data at the time the index building began. Any modifications to the table will be versioned, regardless of whether snapshot isolation has been enabled. Requests to read data from the table will access the versioned data.

Multiple Active Result Sets and Row Level Versioning

Although Multiple Active Result Sets (MARS) is a client-side feature of SQL Server 2005, its implementation relies on the version store which is very much a server-side feature. For this reason, discussion of MARS is included along with other row level versioning features, and not in the section on client-side concurrency considerations.

Microsoft SQL Server 2005 extends the support for MARS in applications accessing the database engine. In earlier versions of SQL Server, database applications could not maintain multiple active statements on a connection when using default result sets. The application had to process or cancel all result sets from one batch before it could execute any other batch on that connection. SQL Server 2005 introduces new attributes that allow applications to have more than one pending request per connection; in particular, to have more than one active default result set per connection.

Only SELECT and BULK INSERT statements are allowed to execute non-atomically and interleave with other statements executing on other batches under a MARS enabled connection. DDL and data modification statements all execute atomically. If there are any other statements waiting to execute, they will block until atomic statements have completed execution.

If two batches are submitted under a MARS connection, one of them containing a SELECT statement and the other containing an UPDATE statement, the UPDATE can begin execution before the SELECT has processed its entire result set. However, the UPDATE statement must run to completion before the SELECT statement can make progress and all changes made by the UPDATE will be versioned. If both statements are running under the same transaction, any changes made by the UPDATE statement after the SELECT statement has started execution are not visible to the SELECT, because the SELECT will access the older version of the required data rows.

Client-side Issues Involving Concurrency

The Storage Engine in SQL Server 2005 provides concurrency control, managing transactions and locks. However, users access SQL Server through client applications, components, and services, and all these programming components are affected by the way SQL Server manages concurrency. It is also important to understand how connection settings affect concurrency, and how to manage transactions effectively from client applications.

In previous versions of SQL Server, any command actually executed by SQL Server had to be a Transact-SQL command, regardless of which application layer started a particular transaction.

With the new SQL CLR capabilities in SQL Server 2005, the new server side programming infrastructure, including such features as Service Broker, and the new data access provider, concurrency and transaction management becomes far more powerful. However, with this added power comes added complexity.

  1. Concurrency control in SQL CLR objects

    Actions performed from inside a SQL CLR procedure do not need to start a DTC-managed transaction, and operations that update, insert, or delete SQL Server data follow the same transactional principles as standard Transact-SQL procedures.

    SQL CLR objects can use SQL Server data by using the built-in Data Access Provider, through the SqlContext object, and these SQL CLR objects can send results to the calling connection using the Pipe property of the SqlContext object, as in the following example:

    <SQLProcedure()> _
    Public Shared Sub GetServerVersion  ()
    Dim cmdGetVersion as SqlCommand = SqlContext.CreateCommand()
    
    cmdGetVersion.Commandtext = “SELECT @@VERSION”
    cmdGetVersion.CommandType = CommandType.Text
    
    SqlContext.Pipe.Send(cmdGetVersion.ExecuteScalar().ToString())
    End Sub
    

    It looks very simple, but what is actually happening behind the scenes? Setting up a trace to watch what actually happens when the above command is executed, we see the following events:

    SQL:BatchStartingEXEC cbo.GetServerVersion
    SQL:StmtStartingEXEC cbo.GetServerVersion
    SP:StartingEXEC cbo.GetServerVersion
    SP:StmtStarting SELECT @@VERSION
    

    Note that these are exactly the same events we would have seen if we had defined and executed the following Transact-SQL stored procedure:

    CREATE PROCEDURE dbo.GetServerVersion
    AS
    SELECT @@VERSION
    GO
    

    Now let’s create a CLR stored procedure which updates data in Production.Product table in the AdventureWorks database:

    <SqlProcedure()> _
    Public Shared Sub UpdateListPriceByProductID(ByVal ProductID As SqlInt32)
    Try
    Dim cmdUpdate As SqlCommand = SqlContext.CreateCommand()
    Dim parProductID As SqlParameter = _
    cmdUpdate.Parameters.Add("@ProductID", SqlDbType.Int)
    
    parProductID.Direction = ParameterDirection.Input
    cmdUpdate.CommandText = "UPDATE Production.Product " _
    + "SET ListPrice = ListPrice * 1.1 " _
    + "WHERE ProductID = @ProductID"
    
    parProductID.Value = ProductID
    
    cmdUpdate.ExecuteNonQuery()
    
    Catch e As Exception
    SqlContext.Pipe.Send(e.Message)
    End Try
    End Sub
    

    Again, we can use a trace to see what activity actually takes place in SQL Server when we execute this stored procedure. The following table lists the events that took place on the server, and includes an event number so that we can refer to the specific events in the following discussion.

    1

    SQL:BatchStarting

    EXECUTE dbo.UpdateListPriceByProductID 444;
    
       

    The batch we execute in Management Studio begins execution.

    2

    SQL:StmtStarting

    EXECUTE dbo.UpdateListPriceByProductID 444;
    
       

    The only statement in this batch begins execution.

    3

    SP:Starting

    EXECUTE dbo.UpdateListPriceByProductID 444;
    
       

    This statement calls the dbo.UpdateListPriceByProductID CLR stored procedure, which begins its execution.

    4

    SP:StmtStarting

    UPDATE Production.Product
    SET ListPrice = ListPrice * 1.1
    WHERE ProductID = @ProductID
    
       

    This is the only statement in this stored procedure that executes any action on the database.

    Note that there is no connection event, because this procedure uses the SQLContext object to get a reference to the current connection context.

    5

    SQLTransaction

    75691 UPDATE 0 – Begin
    
       

    Because the statement to be executed is a data modification operation, SQL Server starts an implicit transaction automatically.

    6

    SP:Starting

    UPDATE Production.Product
    SET ListPrice = ListPrice * 1.1
    WHERE ProductID = @ProductID
    
       

    There is an AFTER UPDATE trigger defined on the Production.Product table called uProduct. This trigger is managed internally exactly like a stored procedure, so we see the SP:Starting event.

    The execution of this trigger still takes place under the control of the transaction number 75691. This is the code that creates this trigger:

    CREATE TRIGGER [Production].[uProduct] 
    ON [Production].[Product] 
    AFTER UPDATE NOT FOR REPLICATION AS 
    BEGIN
    SET NOCOUNT ON;
    UPDATE [Production].[Product]
    SET [Production].[Product].[ModifiedDate] 
    = GETDATE()
    FROM inserted
    WHERE inserted.[ProductID] = 
    [Production].[Product].[ProductID];
    END;
    

    7

    SP:StmtStarting

    SET NOCOUNT ON;
    
       

    The first statement inside the trigger begins.

    8

    SP:StmtCompleted

    SET NOCOUNT ON;
    
       

    The first statement inside the trigger completes.

    9

    SP:StmtStarting

    UPDATE [Production].[Product] 
    SET [Production].[Product].[ModifiedDate] 
    = GETDATE() 
    FROM inserted 
    WHERE inserted.[ProductID] 
    = [Production].[Product].[ProductID];
    
       

    The second statement inside the trigger begins.

    10

    SP:StmtCompleted

    UPDATE [Production].[Product] 
    SET [Production].[Product].[ModifiedDate] 
    = GETDATE()
    FROM inserted 
    WHERE inserted.[ProductID] 
    = [Production].[Product].[ProductID];
    
       

    The second statement inside the trigger completes.

    11

    SP:Completed

    UPDATE Production.Product
    SET ListPrice = ListPrice * 1.1
    WHERE ProductID = @ProductID
    
       

    The uProduct trigger completes execution.

    12

    SP:StmtCompleted

    UPDATE Production.Product
    SET ListPrice = ListPrice * 1.1
    WHERE ProductID = @ProductID
    
       

    The original UPDATE statement started on step 4 completes its execution.

    13

    SQLTransaction

    75691 UPDATE 1 – Commit
    
       

    Transaction 75691 commits automatically when the statement completes, because this transaction has been opened automatically as this statement required writing to the database.

    14

    SP:Completed

    EXECUTE [dbo].[UpdateListPriceByProductID] 444;
    
       

    The stored procedure started on step 3 completes.

    15

    15 SQL:StmtCompleted

    EXECUTE [dbo].[UpdateListPriceByProductID] 444;
    
       

    The statement started on step 2 completes.

    16

    SQL:BatchCompleted

    EXECUTE [dbo].[UpdateListPriceByProductID] 444;
    
       

    The batch started on step 1 completes.

    The trace events give no clue that what was actually executed was CLR code. The fact that a CLR stored procedure forced the execution of a Transact-SQL trigger should not cause any concern from a transactional point of view.

    The above trace information shows us that Execution of code sent from inside a SQL CLR stored procedure runs under the same SPID as the connection which calls this procedure, as long as the SqlContext object is used.

    When SQL CLR objects access any external database system using any of the .NET Data Providers, they will behave as if they had connected from a standard .NET application. There will be no differences from a transactional point of view. We cover concurrency management in ADO.NET 2.0 in the next section.

    SQL CLR objects should always connect to the current instance of SQL Server through the in-process data provider.

  2. Concurrency management from ADO.NET 2.0

    ADO.NET broke the former client data access paradigm by providing two different programming models to create database applications and components:

    • The disconnected model based on optimistic concurrency built around the DataSet and DataAdapter types, and

    • The Connected model based on pessimistic concurrency, built around the SQLCommand and SQLDataReader types

    Using the disconnected model, the application uses a SqlDataAdapter or a TableAdapter to read the requested data from the database, using enough shared locks during this operation to make sure that it can read consistently data rows, and then disconnects from the database, releasing any locks that this reading operation might had. In this sense, transaction behavior is almost the same as in previous releases of ADO.NET, and you can refer to the comprehensive public information available on this topic in MSDN.

    ADO.NET 2.0 provides an enhanced SQL Server .NET Data Provider that exposes the new functionality available in the new edition of SQL Server. Some of the new features exposed by this new provider have been covered previously in this paper, such as MARS and the new Snapshot Isolation level.

    Another ADO.NET enhancement that might change transaction behavior is the possibility of executing commands asynchronously, which uses the new MARS feature of SQL Server behind the scenes. However, there isn’t anything special on this issue related to ADO.NET and the behavior is exactly the same as it has been described in earlier sections of this paper.

    The .NET Framework Version 2.0 provides two new Transaction Managers: the Lightweight Transaction Manager (LTM) and the OleTx Transaction Manager. Access to these two transactions managers is encapsulated through the System.Transactions namespace.

    You can read a complete description of System.Transaction in the white paper “Introducing System.Transactions” from Juval Lowy (https://www.microsoft.com/downloads/details.aspx?FamilyId=AAC3D722-444C-4E27-8B2E-C6157ED16B15&displaylang=en) ADO.NET 2.0 has been redesigned to take advantage of System.Transactions automatically, which means that any code using Enterprise Services in ADO.NET 2.0 will use LTM or OleTx behind the scenes when necessary. In this way, the application would be using this transaction model in a declarative way.

    To illustrate this new technique, we are going to execute the same sample application with and without using System.Transactions.

    This first example executes a SQL statement through a SQLCommand object, without explicit transaction control:

    Private Sub TestSystemTransactions()
        Dim conAW As New SqlConnection(sConnString)
        Dim sQuery1 As String, count1 As Integer
        Dim cmd1 As SqlCommand = conAW.CreateCommand()
    
        sQuery1 = "UPDATE Production.Product " _
            + "SET ListPrice = ListPrice * 1.1 " _
            + "WHERE ProductNumber LIKE 'EC%'"
    
        cmd1.CommandText = sQuery1
    
            Try
            conAW.Open()
            Dim result1 As IAsyncResult = cmd1.BeginExecuteNonQuery()
    
            While result1.IsCompleted = False
                Console.WriteLine("Waiting ({0})", count1)
                ' Wait for 1/10 second, so the counter
                ' doesn't consume all available resources 
                ' on the main thread.
                Threading.Thread.Sleep(100)
                If result1.IsCompleted = False Then count1 += 1
            End While
    
            Console.WriteLine("Command complete. Affected {0} rows.", _
                cmd1.EndExecuteNonQuery(result1))
    
            Catch ex As SqlException
            Console.WriteLine("Error ({0}): {1}", ex.Number, ex.Message)
            Catch ex As InvalidOperationException
            Console.WriteLine("Error: {0}", ex.Message)
            Catch ex As Exception
            Console.WriteLine("Error: {0}", ex.Message)
            Finally
            conAW.Close()
            Console.ReadLine()
            End Try
    End Sub
    

    Again, we can use a trace to see what activity actually takes place in SQL Server when we execute this stored procedure. The following table lists the events that took place on the server, and includes an event number so that we can refer to the specific events in the following discussion.

    1

    SQL:BatchStarting

    UPDATE Production.Product 
    SET ListPrice = ListPrice * 1.1 
    WHERE ProductNumber LIKE 'EC%'
    
       

    The batch we execute from our VB console application, through the cmd1 SqlCommand object.

    2

    SQL:StmtStarting

    UPDATE Production.Product 
    SET ListPrice = ListPrice * 1.1 
    WHERE ProductNumber LIKE 'EC%'
    
       

    Same as in step 1.

    3

    SQLTransaction

    162198 UPDATE 0 – Begin
    
       

    Because the statement to be executed is a data modification operation, SQL Server starts an implicit transaction automatically.

    4

    SP:Starting

    UPDATE Production.Product 
    SET ListPrice = ListPrice * 1.1 
    WHERE ProductNumber LIKE 'EC%'
    
       

    There is an AFTER UPDATE trigger defined on the Production.Product table called uProduct. This trigger is managed internally exactly like a stored procedure, so we see the SP:Starting event.

    The execution of this trigger still takes place under the control of the transaction number 162198. It is the same trigger we commented earlier in this document.

    5

    SP:StmtStarting

    SET NOCOUNT ON;
    
       

    The first statement inside the trigger begins.

    6

    SP:StmtCompleted

    SET NOCOUNT ON;
    
       

    The first statement inside the trigger completes.

    7

    SP:StmtStarting

    UPDATE [Production].[Product] 
    SET [Production].[Product].[ModifiedDate] 
    = GETDATE()
    FROM inserted 
    WHERE inserted.[ProductID] = 
    [Production].[Product].[ProductID];
    
       

    The second statement inside the trigger begins.

    8

    SP:StmtCompleted

    UPDATE [Production].[Product] 
    SET [Production].[Product].[ModifiedDate] 
    = GETDATE()
    FROM inserted 
    WHERE inserted.[ProductID] = 
    [Production].[Product].[ProductID];
    
       

    The second statement inside the trigger completes.

    9

    SP:Completed

    UPDATE Production.Product 
    SET ListPrice = ListPrice * 1.1 
    WHERE ProductID = @ProductID
    
       

    The uProduct trigger completes execution.

    10

    SP:StmtCompleted

    UPDATE Production.Product 
    SET ListPrice = ListPrice * 1.1 
    WHERE ProductNumber LIKE 'EC%'
    
       

    The original UPDATE statement started on step 4 completes its execution

    11

    SQLTransaction

    162198 UPDATE 1 – Commit
    
       

    Transaction 162198 commits automatically when the statement completes, because this transaction has been opened automatically as that this statement required writing to the database.

    12

    SQL:BatchCompleted

    UPDATE Production.Product 
    SET ListPrice = ListPrice * 1.1 
    WHERE ProductNumber LIKE 'EC%'
    
       

    The batch started on step 1 completes.

    Now, this second example executes the same SQL statement through the same SQLCommand object, with explicit transaction control (new lines of code are shown in Bold):

    Private Sub TestSystemTransactions()
        Dim conAW As New SqlConnection(sConnString)
        Dim sQuery1 As String, count1 As Integer
        Dim cmd1 As SqlCommand = conAW.CreateCommand()
    
        sQuery1 = "UPDATE Production.Product " _
            + "SET ListPrice = ListPrice * 1.1 " _
            + "WHERE ProductNumber LIKE 'EC%'"
    
        cmd1.CommandText = sQuery1
    
        Dim myTSScope As New TransactionScope
    
        Using myTSScope
            Try
            conAW.Open()
            Dim result1 As IAsyncResult = cmd1.BeginExecuteNonQuery()
    
            While result1.IsCompleted = False
                Console.WriteLine("Waiting ({0})", count1)
                ' Wait for 1/10 second, so the counter
                ' doesn't consume all available resources 
                ' on the main thread.
                Threading.Thread.Sleep(100)
                If result1.IsCompleted = False Then count1 += 1
            End While
    
            myTSScope.Complete()
    
            Console.WriteLine("Command complete. Affected {0} rows.", _
                cmd1.EndExecuteNonQuery(result1))
    
            Catch ex As SqlException
            Console.WriteLine("Error ({0}): {1}", ex.Number, ex.Message)
            Catch ex As InvalidOperationException
            Console.WriteLine("Error: {0}", ex.Message)
            Catch ex As Exception
            Console.WriteLine("Error: {0}", ex.Message)
            Finally
            conAW.Close()
            Console.ReadLine()
            End Try
        End Using
    End Sub
    

    We can run Profiler to examine the sequence of operations that this code produces in SQL Server, where new operations are in Bold:

    1

    TM: Begin Tran starting

    BEGIN TRANSACTION
    
       

    As soon as the connection is established, the transaction manager attempts to create a transaction, because the code is using a TransactionScope object.

    2

    SQLTransaction

    164587 user_transaction 0 – Begin
    
       

    his is a transaction started by the transaction manager, not triggered by any UPDATE statement as in the previous sample, because no UPDATE statement has been executed yet. In fact, this SQLTransaction has been produced by the BEGIN TRANSACTION statement executed in step 1.

    3

    TM: Begin Tran completed

    BEGIN TRANSACTION
    
       

    The creation of a user transaction has been successful in this connection, and it obtains a XactSequence number for this transaction. The XactSequence number is 236223201281 in this case and identifies all operations to be managed by Transaction Manager under this transactions.

    4

    SQL:BatchStarting

    UPDATE Production.Product 
    SET ListPrice = ListPrice * 1.1 
    WHERE ProductNumber LIKE 'EC%'
    
       

    The batch we execute from our VB console application, through the cmd1 SqlCommand object.

    5

    SQL:StmtStarting

    UPDATE Production.Product 
    SET ListPrice = ListPrice * 1.1 
    WHERE ProductNumber LIKE 'EC%'
    
       

    Same as in step 1.

    6

    SP:Starting

    UPDATE Production.Product 
    SET ListPrice = ListPrice * 1.1 
    WHERE ProductNumber LIKE 'EC%'
    
       

    There is an AFTER UPDATE trigger defined on the Production.Product table called uProduct. This trigger is managed internally exactly like a stored procedure, so we see the SP:Starting event.

    The execution of this trigger still takes place under the control of the XactSequence number 236223201281. It is the same trigger we commented earlier in this document.

    7

    SP:StmtStarting

    SET NOCOUNT ON;
    
       

    The first statement inside the trigger begins.

    8

    SP:StmtCompleted

    SET NOCOUNT ON;
    
       

    The first statement inside the trigger completes.

    9

    SP:StmtStarting

    UPDATE [Production].[Product] 
    SET [Production].[Product].[ModifiedDate] 
    = GETDATE()
    FROM inserted 
    WHERE inserted.[ProductID] = 
    [Production].[Product].[ProductID];
    
       

    The second statement inside the trigger begins.

    10

    SP:StmtCompleted

    UPDATE [Production].[Product] 
    SET [Production].[Product].[ModifiedDate] 
    = GETDATE()
    FROM inserted 
    WHERE inserted.[ProductID] = 
    [Production].[Product].[ProductID];
    
       

    The second statement inside the trigger completes.

    11

    SP:Completed

    UPDATE Production.Product 
    SET ListPrice = ListPrice * 1.1 
    WHERE ProductID = @ProductID
    
       

    The uProduct trigger completes execution.

    12

    SP:StmtCompleted

    UPDATE Production.Product 
    SET ListPrice = ListPrice * 1.1 
    WHERE ProductNumber LIKE 'EC%'
    
       

    The original UPDATE statement started on step 4 completes its execution.

    13

    SQL:BatchCompleted

    UPDATE Production.Product 
    SET ListPrice = ListPrice * 1.1 
    WHERE ProductNumber LIKE 'EC%'
    
       

    The batch started on step 4 completes.

    14

    TM: Commit Tran starting

    COMMIT TRANSACTION
    
       

    The Complete method of the myTSScope object requests Transaction Manager to commit transaction defined by XactSequence number 236223201281, identified as the SQL Transaction 164587.

    15

    SQLTransaction

    164587 user_transaction 1 – Commit
    
       

    This is the user transaction started by the transaction manager on step 2, and it is committed in this step.

    16

    TM: Commit Tran completed

    COMMIT TRANSACTION
    
       

    The user transaction has been successful committed in this connection.

    Profiler shows clearly that the transaction is created in a different way than in the previous sample and is held until the TransactionScope object requests explicitly that the transaction be committed. This gives a more granular control to the developers and they can include more operations as part of the same transaction.

    It is important to realize that in this case System.Transactions based all transactional operations in standard SQL Transactions, because only one data storage was required and distributed transactions were unnecessary. If the application required to update data in two different servers, System.Transactions would start a DTC Transaction instead.

  3. Concurrency and transaction control from the new SQL Native Client

    The new SQL Native Client for SQL Server 2005 has been designed to replace MDAC, combining the SQL OLE DB Provider and the SQL ODBC Driver into one native dynamic Link Library (DLL) and expanding the capabilities of these two interfaces. It is compatible with ActiveX Data Objects (ADO), providing a migration path to ADO-based database applications with added performance and functionality.

    The main purpose of the SQL Native Client is to provide support for SQL Server 2005 specific features to C++ developers currently using ODBC or OLEDB natively. These developers may be avoiding ADO or ADO.NET because these “developer-friendly” programming models include extra layers of application processing which degrade the application’s performance.

    For example, the SQL Native Client supports MARS and asynchronous operations natively, without requiring ADO.NET to take advantage of these options.

    3.1 SQLOLEDB Transaction processing considerations using the SQL Native Client

    By default, SQLOLEDB uses an automatic session-based transaction control, for which each unit of execution is considered as a single transaction, without requiring any manual control from the client application.

    However, multiple units of work can participate in the same transaction. A SQLOLEDB consumer can get more precise control over a SQL Server Transaction through the ITransactionLocal interface, which exposes the StartTransaction, Commit, and Abort methods.

    To gain even broader transaction control, a SQL Server 2005 SQLOLEDB transaction can join an existing distributed transaction, managed by MS-DTC. SQLOLEDB consumers can use the ITransactionJoin::JoinTransaction method to participate in a distributed transaction.

    Client applications using the SQLOLEDB programming model can control the transaction isolation level in different ways:

    • DBPROPSET_SESSION property DBPROP_SESS_AUTOCOMMITISOLEVELS for SQLOLEDB default autocommit mode.

    • The isoLevel parameter of the ITransactionLocal::StartTransaction method for local manual-commit transactions.

    • The isoLevel parameter of the ITransactionDispenser::BeginTransaction method for MS DTC-coordinated distributed transactions.

    Selecting an appropriate transaction isolation level is crucial for most database applications. On one hand, business requirements might require that the isolation level be more restrictive, to make sure that data access is performed in a way that is compatible with the business requirements. On the other hand, high transaction isolation levels reduce concurrency. The default isolation level for SQLOLEDB applications is DBPROPVAL_TI_READCOMMITTED, which is equivalent to the SQL Server default transaction Isolation level READ COMMITTED.

    3.2 ODBC Transaction processing considerations using the SQL Native Client

    As opposed to SQLOLEDB, database applications using the ODBC programming model through the SQL Native Client can manage transactions at connection level. The ODBC model exposes two different transaction models:

    • The autocommit mode, which natively uses the default per-statement autocommit mode of SQL Server. The database application does not need to perform any action to commit or rollback the transactions created since the connection was opened, because these actions are automatically managed by SQL Server on a statement by statement basis.

    • The manual-commit mode, which is based on the IMPLICIT_TRANSACTION functionality of SQL Server, considers all actions performed since the connection was established (or since the previous SqlEndTran) as part of the same transaction. This transaction will end only if the application executes the SqlEndTran method. If the application closes the connection without committing its work, all work will be rolled back automatically.

    An ODBC application can switch from autocommit to manual mode at any time by calling the SQLSetConnectAttr method to set autocommit mode off.

    The database application will need to call the SqlEndTran method with the SQL_COMMIT or SQL_ROLLBACK options to terminate transactions in manual mode.

    For distributed transactions, ODBC applications should run without using autocommit mode, but there is no need to call SQLEndTran because this will be managed directly by MS-DTC. However, the process required to participate in a distributed transaction is a bit more complex than with the SQLOLEDB model:

    • The client application calls the ITRansactionDispenser::BeginTransaction method of the MS-DTC OLE interface to get a reference to a transaction context

    • The client application calls the SQLSetConnectAttr method to set the SQL_COPT_SS_ENLIST_IN_DTC attribute to the DTC object obtained in the previous step

    • Now this connection would be enlisted as part of the distributed transaction.

    Note that the ODBC programming model implements the Transaction Isolation level Repeatable Read as Serializable. You can set the transaction isolation level using the SQL_ATR_TXN_ISOLATION to any of the following values: SQL_TXN_READ_UNCOMMITTED, SQL_TXN_READ_COMMITTED, SQL_TXN_REPEATABLE_READ, SQL_TXN_SS_SNAPSHOT, or SQL_TXN_SERIALIZABLE.

    The ODBC model supports MARS, which is enabled by default. The application can control this behavior by calling the SQLSetConnectAttr method to set the SQL_COPT_SS_MARS_ENABLED to SQL_MARS_ENABLED_YES or SQL_MARS_ENABLED_NO.

  4. Transaction management from COM+ Services managed components

    COM+ provides several services to applications, some of them designed to manage transactions in a distributed environment. These transaction related services are:

    • Automatic Transaction Processing, which applies declarative transaction-processing features.

    • BYOT (Bring Your Own Transaction), which allows a form of transaction inheritance, using the BYOT feature to access the COM+ Distributed Transaction Coordinator (DTC).

    • Compensating Resource Manager (CRM), which applies atomicity and durability properties to non-transactional resources.

    • Queued Components, which provide asynchronous message queuing.

    4.1 Automatic Transaction Processing

    This service provides the ability to configure the transactional behavior of a class in a declarative way at design time, and this will dictate the way objects created from this class will participate in transactions at run time, without any further code to control the transaction starting and ending points.

    This technique simplifies transaction management in your application code, but it comes at a cost. It is important to understand these implications to make sure that it doesn’t introduce performance degradation.

    The usual best practices for transaction processing typically advise not opening a connection until it is absolutely required to be open, and closing the connection as soon as possible. Along the same vein, you should consider not initiating a transaction until it is absolutely necessary, and then completing the transaction when it is no longer required.

    One of the typical problems with serviced components is that they create a new transaction (or enlist the current connection with an existing transaction) as soon as the application opens the connection. This means that whatever you do in this connection will be part of a transaction, and will include all statements executed since the connection first opened. This reduces concurrency as transactions will include the execution of statements that might not need to be part of a connection-wide transaction, and might perform better under the default auto-commit behavior of SQL Server.

    Another issue that not many developers are unaware of is the fact that COM+ transactions are created by default using the SERIALIZABLE transaction isolation level. This means that any shared lock acquired by any statement executed in this connection will not be released until the transaction terminates. It is easy to change the transaction isolation level for a COM+ transaction, but a developer needs to be aware that a change of isolation level might be required. To make things more complicated, it is not always obvious whether the object being designed is to be the root object in a distributed transaction. Therefore, it might not always be possible to change the transaction isolation level from the current component, and the developer might not be able to control the concurrency in the required way.

    The fact is that if a database application uses data from only one SQL Server instance, and doesn’t require any other services from COM+ than automatic transactions, this application should be re-written based on local transactions instead of COM+ based distributed transactions. COM+ simplifies development, but makes transaction debugging more difficult. COM+ also imposes a performance penalty (often between 20% and 50%) on each transaction, which could be avoided if there is no need for distributed transactions.

    4.2 BYOT (Bring Your Own Transaction).

    The BYOT feature allows a form of transaction inheritance, where a COM+ component can enlist the connection it uses in pre-existing Distributed Transaction Protocol (DTC) or Transaction Internet Protocol (TIP) transactions. This technique can be difficult to manage in some complex cases, although it provides more granular control than automatic transactions.

    4.3 Compensating Resource Manager (CRM)

    COM+ provides a Compensating Resource Manager for including non-transactional objects in DTC transactions. CRM is a simplified resource manager that provides at least transactional atomicity (all or nothing behavior) and durability through the recovery log.

    It is important to understand the implications of adding non-transactional objects to a DTC transaction, as this adds extra complexity and, typically, additional execution time to a DTC transaction. Furthermore, these non-transactional objects typically expand the number of possible error conditions which must be considered, resulting in a greater likelihood that the transaction will be rolled back and producing clear performance degradation from the user point of view.

    This doesn’t mean that this service should not be used, but it is important to evaluate the need for this service before using it. If there is a clear business needs for this service, and it cannot easily be solved by other means, CRM may be the best solution. However, the application should be monitored to make sure that it serves specific business needs in an acceptable way for the users. Discussing the CRM is outside the scope of this paper, but you can read a full description of this service at:https://msdn2.microsoft.com/library/ms680326.aspx

    4.4 Queued Components

    Queued components are COM+ services that expose Microsoft Message Queue (MSMQ) functionality to components, providing asynchronous message queuing. These messages can be transactional, ensuring that messages are delivered in order, are delivered only once, and are successfully retrieved from the destination queue.

    Sending a set of messages inside a transaction involves grouping messages together to form a single transaction. The Queuing technology differentiates between internal and external transactions. Internal transactions are directly related to the message management and are not part of the scope of this paper.

    External queuing transactions involve external resources, such as databases. These transactions rely on an external transaction coordinator that is not part of the Message Queuing system, typically MS-DTC. An external transaction coordinator has a more complex programming model than that used for internal transactions.

    In principle, if the only reason for using Queued Components is to extend scalability of a database application involving one instance of SQL Server, it would be better to reconsider this architecture in favor of one based on Service Broker.

  5. Concurrency and asynchronous Service Broker enabled applications

    Service Broker is a component of the SQL Server 2005 Storage Engine that provides infrastructure for queue-based messaging framework to create reliable and scaleable database applications.A typical custom-made queued database application relies on the heavy use of queue tables, where client applications and queuing services are constantly inserting, selecting and deleting small numbers of rows, leading to frequent concurrency problems. These concurrency problems occur primarily because the SQL Server lock manager doesn’t consider these queue tables as anything other than standard tables, and it applies all the usual locking mechanisms when the applications are accessing the data in the queue tables. It is very hard for developers to design custom queuing mechanisms that perform well, because the normal locking mechanisms used with the heavily accessed queue tables result in frequent blocking and a high rate of deadlocks.

    Service Broker provides an alternative to the custom queuing application, and manages the entire queuing and messaging infrastructure. Using Service Broker, developers can concentrate on writing applications that use the framework provided without dealing with the inner details of the technology.

    Applications using Service Broker are based on messages sent to queues as part of conversations, which are grouped in conversation groups. The applications send messages to a conversation as part of a transaction, which guarantees that all messages from each conversation are registered in Service Broker or that none of them are.

    The application defines specific Service Broker Services, which may be implemented as stored procedures, or as external Windows or console applications. In some cases, the application may implement the Service Broker Service as an external Windows service, to process the messages one at a time in the order received. If the queue is defined with automatic internal activation, Service Broker will execute as many instances as necessary of each of these services, up to the limit defined in the MAX_QUEUE_READERS option. Having multiple instances of these services allows Service Broker to process all pending conversations in this queue.

    Transactions in Service Broker are handled in two separate steps:

    1. The application creates a transaction to guarantee the consistency of the message or group of messages sent as part of a single business transaction. The transaction includes all required actions to be taken by Service Broker to add these messages to the queue, and to make sure that all of them are grouped logically together. If this transaction needs to be rolled back, the integrity of the conversation is still preserved, because only committed transactions will be part of the Service Broker conversation.

    2. A Service broker service should process each conversation group in its own transaction, which will include not only the database actions requested by messages included in this conversation, but also all changes to the infrastructure tables, to reflect the status of this conversation. The MSDN article “A First Look at SQL Server 2005 Service Broker” describes this process in detail.

    The first process is unlikely to result in concurrency problems, due to row-level locking in SQL Server. Each conversation group represents a unit of work, which will be inserted into a queue in a single transaction and will be processed as a single transaction. Service Broker guarantees that each message is processed only once, therefore the case of two processes competing for the same data at the same time is very unlikely to happen, and this event will be managed by Service Broker transparently.

    Conversation group locks are handled automatically by Service Broker and there are no mechanisms, such as hints, to manage them manually. Only one queue reader service can process messages from a given conversation group at any time.

    The following statements acquire conversation group locks:

    • BEGIN DIALOG CONVERSATION (Transact-SQL)

    • BEGIN CONVERSATION TIMER (Transact-SQL)

    • END CONVERSATION (Transact-SQL)

    • MOVE CONVERSATION (Transact-SQL)

    • RECEIVE (Transact-SQL)

    • SEND (Transact-SQL)

    However, as any database application, and more importantly for applications based in a disconnected model, it is crucial to design applications in such a way that updates to the data don’t depend on a particular state of the data they want to update. Applications should be designed to perform atomic data updates whenever possible, without relying on data values previously read because, unless both operations could be part of serializable transaction, the values read previously are not guaranteed to be the same by the time the update operation will take place. This is true as well for Service Broker applications, where not properly designed transactions could result in lost updates in cases where one message requests specific changes to SQL Server data and another message requests different changes to the same data. One of the updates would most likely be lost. This is not a Service broker issue, but an application design issue.

    This is a typical case where using optimistic concurrency techniques could be helpful, as this avoids the problem caused when one application requests some database modifications based on the current state of the data, but by the time these modifications need to be applied to the database, the underlying data might have been changed by another process. In order to implement optimistic concurrency, each conversation should include enough information about the current and previous state of the data, to make sure that the service in charge of processing this message has enough information to determine whether to allow these new changes or to rollback this transaction.

    In other words, when the application reads data from SQL Server, the application should keep this data as original values (this is the way DataSets work automatically). When the application send requests for changes to these data values, the message should contain both the actual and original values. The UPDATE statement to be generated will use the actual values in the SET clause, and the original values in the WHERE clause. In this way, if the data is still the same it was read originally, the WHERE clause will find this same row again, and the SET clause will determine how to update these values.

    However, if the data has been changed by other processes since the data was originally read, the WHERE clause won’t match any existing row, and no rows will be changed by this UPDATE operation. This case doesn’t produce any SQL Server errors as such, but the stored procedure implementing this particular Service broker Service should send a notification of this event, which the client application should consider as a concurrency violation.

    A message that cannot be successfully processed becomes a Poison Message, and the application should be designed to manage poison message gracefully. In particular, if the reason for this poison message is a concurrency problem, the client application should be informed about this event. The client application will need to resolve this situation and could treat it as would any lost data in an optimistic concurrency environment. A typical solution would be to present the user the new underlying values available in the database, offering the user the possibility of updating them again, or discarding the proposed changes.

Conclusion

SQL Server 2005 has improved concurrent access to data with new features and changes to existing features. Many of the features are built upon the new Row Level Versioning (RLV) technology in SQL Server. Some of the features based upon RLV technology, such as triggers, require no application changes and are barely noticeable from a developer perspective. Others, such as the two levels of snapshot isolation, require enabling a new database option and may require minor code changes. Taking full advantage of snapshot isolation definitely involves a re-evaluation of the currency versus consistency tradeoff.

SQL Server 2005 also provides concurrency enhancements for the clients communicating with SQL Server. The more you know about how SQL Server manages concurrency internally, the better able you will be to determine the optimal concurrency control values in your client applications.