Transactions (OLTP)---a Technical Reference Guide for Designing Mission-Critical OLTP Solutions
Want more guides like this one? Go to Technical Reference Guides for Designing Mission-Critical Solutions.
Enterprise applications rely on transactions to interact with a database system. A transaction is a sequence of operations performed as a single logical unit of work. Database management systems must support what are known as ACID (Atomic, Consistent, Isolated, and Durable) properties when supporting transactions. A critical aspect of the support for durability of transactions in SQL Server is provided by the transaction log.
This section provides some best practice guidance and resources for more information. (Note that the full URLs for the hyperlinked text are provided in the Appendix at the end of this document.)
Overview of Transactions
Proper transaction management minimizes locking and contention and provides data integrity. An application can use different transaction types in different contexts; therefore you should consider the specific characteristics of every transaction type the application will use. Details of transaction support in SQL Server can be found in the article Controlling Transactions (Database Engine).1
Manual transactions in client-side libraries enable you to span a transaction across multiple calls to a single data store. Both the SQL Server .NET Data Provider and the OLE DB .NET Data Provider implement the IDbTransaction interface and expose BeginTransaction on their connection objects. This lets you begin a transaction, run multiple SQL calls using that connection instance, and control the transaction outcome from the data access code. Controlling transactions from .NET Framework languages is described in the article Transactions and Concurrency (ADO.NET).2
By default, transactions are managed at the connection level. When a transaction is started on a connection, all Transact-SQL statements executed on that connection are part of the transaction until the transaction ends. However, under a multiple active result set (MARS) session, a Transact-SQL explicit or implicit transaction becomes a batch-scoped transaction that is managed at the batch level. When the batch completes, if the batch-scoped transaction is not committed or rolled back, it is automatically rolled back by SQL Server. This is described in the article Using Multiple Active Result Sets (MARS).3
Best practices and guidance from a SQL Server perspective can be found in the Coding Efficient Transactions4 section of SQL Server 2008 R2 Books Online, and in the "Design Considerations" section of the Chapter 12: Improving .NET Application Performance and Scalability5 in the "Microsoft patterns & practices" guide. Optimize queries that are part of transactions to make sure that as few rows as possible are read during transactions. Always separate the data reads that do not need to be involved in the transaction from data modification language (DML) statements. If you have to use SELECT and UPDATE in the same stored procedure, include only the UPDATE within a transaction wherever possible.
Transactions should be as short as possible and should not overlap any period of user input. When modifications need to be made, start a transaction, execute the modification statements, and then immediately commit or roll back. Do not open the transaction before it is required.
The use of Table-valued Parameters can help to eliminate the need for multiple round trips between clients and servers. Excessive round trips introduce network delays into transaction duration and need to be avoided. The use of table-valued parameters is discussed in the article Table-Valued Parameters (Database Engine).6
When errors occur, corrective action (COMMIT or ROLLBACK) should be included in application code. One effective tool for handling errors, including those in transactions, is the structured exception handling construct that is described in the article Using TRY…CATCH in Transact-SQL.7 Once a branch into an error handler has been made, it is important to take action based upon the current state of the transaction, which may or may not be committable. The state of the transaction can be determined by querying the XACT_STATE system function as described in the article XACT_STATE(Transact-SQL).8
Controlling ROLLBACK Behavior
By default if a statement-terminating error occurs within a Transact-SQL statement in a transaction, execution continues with the next Transact-SQL statement. For many users, this is unexpected behavior. Statement-terminating errors can be automatically promoted to batch-terminating errors (which will roll back any surrounding transaction) by the use of the XACT_ABORT session option that is described in the article SET XACT_ABORT (Transact-SQL).9
While transactions can be nested, a ROLLBACK TRANSACTION statement at any level will roll back all enclosing transactions as well as the transaction at the level that was rolled back. A user can set a savepoint, or marker, within a transaction. Savepoints are useful in situations where errors are unlikely to occur. The use of a savepoint to roll back part of a transaction in the case of an infrequent error can be more efficient than having each transaction test to see if an update is valid before making the update. Updates and rollbacks are expensive operations, so savepoints are effective only if the probability of encountering the error is low and the cost of checking the validity of an update beforehand is relatively high.
If a client's network connection to an instance of the Database Engine is broken, any outstanding transactions for the connection are rolled back when the network notifies the instance of the connection break.
Transaction Isolation Levels and Concurrency
To reduce blocking, consider using a row versioning-based isolation level for read-only queries. Make intelligent use of lower transaction isolation levels. Many applications can be readily coded to use a read-committed transaction isolation level. Substantial concurrency issues can be caused by requiring serializable transaction isolation level when it is not needed. Many applications (including Component Services and BizTalk SQL Server adapters) default to serializable transaction isolation level but this level can often be reduced. When the database is enabled for READ_COMMITTED_SNAPSHOT, all queries running under the read committed isolation level use row versioning, which means that read operations do not block update operations. For more information, see Using Row Versioning-based Isolation Levels.10
On the page Advanced Transaction Topics,11 you can find specific guidance to deal with contention and performance issues in transactional systems with many concurrent users.
Case Studies and References
Every scalable and well-designed Online Transaction Processing (OLTP) deployed solution makes use of database transactions. The following case studies provide examples:
The case study bwin: Global Online Gaming Company Deploying SQL Server 2008 to support 100 Terabytes12 describes the experience of bwin, a provider of online gaming services.
The white paper High Availability and Disaster Recovery at ServiceU: A SQL Server 2008 Technical Case Study13 describes the experiences of ServiceU, a provider of online and on-demand event management software.
Questions and Considerations
This section provides questions and issues to consider:
SQL Server transactions can be "explicit" transactions where the user or API explicitly starts the transactions. SQL Server also supports the use of "implicit" transactions where transactions are automatically started when particular Transact-SQL statements are executed as discussed in the article Implicit Transactions.14
Marked Transactions are critical for helping to maintain consistency in multi-database applications. Prior to performing backups of multiple databases, a marked transaction can be written to the transaction log of each database. When databases need to be restored, each can then be restored with the STOPATMARK or STOPBEFOREMARK option. Marking a transaction is described in the article BEGIN TRANSACTION (Transact-SQL).15 An example of restoring to a mark is described in the article RESTORE (Transact-SQL).16
A batch or stored procedure which starts a manual or implicit transaction when MARS is enabled must complete the transaction before the batch exits. If it does not, SQL Server rolls back all changes made by the transaction when the batch finishes. Such a transaction is managed by SQL Server as a batch-scoped transaction. This is a new type of transaction introduced in SQL Server 2005 to enable existing well-behaved stored procedures to be used when MARS is enabled. This is discussed in the article Using Multiple Active Result Sets (MARS).17
SQL Server supports distributed transactions that are managed by the Microsoft Distributed Transaction Coordinator (MSDTC). Distributed transactions are described in the article BEGIN DISTRIBUTED TRANSACTION (Transact-SQL).18 Note in particular that distributed transactions are not supported in conjunction with snapshot transaction isolation level.
SQL Server will raise error 266 if the @@TRANCOUNT at the beginning of the execution of a stored procedure is not the same at the end of the execution of the stored procedure. This behavior is described in the article Rollbacks and Commits in Stored Procedures and Triggers.19 For this reason, be careful when using transactions with triggers. SQL Server treats triggers as part of the original transaction (INSERT, UPDATE or DELETE) that fired the trigger. Therefore, the value of @@TRANCOUNT is 1 as soon as trigger execution begins. The ROLLBACK TRAN command executed within a trigger rolls back not only your transaction defined within the trigger but also the original transaction.
Following are the full URLs for the hyperlinked text.
1 Controlling Transactions (Database Engine)http://msdn.microsoft.com/en-us/library/ms175523.aspx
2 Transactions and Concurrency (ADO.NET)http://msdn.microsoft.com/en-us/library/777e5ebh.aspx
3 Using Multiple Active Result Sets (MARS)http://msdn.microsoft.com/en-us/library/ms131686.aspx
4 Coding Efficient Transactionshttp://msdn.microsoft.com/en-us/library/ms187484.aspx
5 Chapter 12: Improving .NET Application Performance and Scalabilityhttp://msdn.microsoft.com/en-us/library/ff647768.aspx
6 Table-Valued Parameters (Database Engine)http://msdn.microsoft.com/en-us/library/bb510489.aspx
7 Using TRY…CATCH in Transact-SQLhttp://msdn.microsoft.com/en-us/library/ms179296.aspx
9 SET XACT_ABORT (Transact-SQL)http://msdn.microsoft.com/en-us/library/ms188792.aspx
10 Using Row Versioning-based Isolation Levelshttp://msdn.microsoft.com/en-us/library/ms179599.aspx
11 Advanced Transaction Topicshttp://msdn.microsoft.com/en-us/library/ms180799.aspx
12 bwin: Global Online Gaming Company Deploying SQL Server 2008 to support 100 Terabyteshttp://www.microsoft.com/casestudies/Case_Study_Detail.aspx?casestudyid=4000001470
13 High Availability and Disaster Recovery at ServiceU: A SQL Server 2008 Technical Case Studyhttp://sqlcat.com/whitepapers/archive/2009/08/04/high-availability-and-disaster-recovery-at-serviceu-a-sql-server-2008-technical-case-study.aspx
14 Implicit Transactionshttp://msdn.microsoft.com/en-us/library/ms188317.aspx
15 BEGIN TRANSACTION (Transact-SQL)http://msdn.microsoft.com/en-us/library/ms188929.aspx
16 RESTORE (Transact-SQL)http://msdn.microsoft.com/en-us/library/ms186858(v=SQL.105).aspx
17 Using Multiple Active Result Sets (MARS)http://msdn.microsoft.com/en-us/library/ms131686.aspx
18 BEGIN DISTRIBUTED TRANSACTION (Transact-SQL)http://msdn.microsoft.com/en-us/library/ms188386.aspx
19 Rollbacks and Commits in Stored Procedures and Triggershttp://msdn.microsoft.com/en-us/library/ms187844.aspx