Transactions (OLTP)---a Technical Reference Guide for Designing Mission-Critical OLTP Solutions

  • Article

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.

Best Practices

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

Design Guidelines

  • 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 IsolationLevels.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:

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.

Appendix

Following are the full URLs for the hyperlinked text.

1 Controlling Transactions (Database Engine)https://msdn.microsoft.com/en-us/library/ms175523.aspx

2 Transactions and Concurrency (ADO.NET)https://msdn.microsoft.com/en-us/library/777e5ebh.aspx

3 Using Multiple Active Result Sets (MARS)https://msdn.microsoft.com/en-us/library/ms131686.aspx

4 Coding Efficient Transactionshttps://msdn.microsoft.com/en-us/library/ms187484.aspx

5 Chapter 12: Improving .NET Application Performance and Scalabilityhttps://msdn.microsoft.com/en-us/library/ff647768.aspx

6 Table-Valued Parameters (Database Engine)https://msdn.microsoft.com/en-us/library/bb510489.aspx

7 Using TRY…CATCH in Transact-SQLhttps://msdn.microsoft.com/en-us/library/ms179296.aspx

8 XACT_STATE(Transact-SQL)https://msdn.microsoft.com/en-us/library/ms189797.aspx

9 SET XACT_ABORT (Transact-SQL)https://msdn.microsoft.com/en-us/library/ms188792.aspx

10 Using Row Versioning-based Isolation Levelshttps://msdn.microsoft.com/en-us/library/ms179599.aspx

11 Advanced Transaction Topicshttps://msdn.microsoft.com/en-us/library/ms180799.aspx

12 bwin: Global Online Gaming Company Deploying SQL Server 2008 to support 100 Terabyteshttps://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 Transactionshttps://msdn.microsoft.com/en-us/library/ms188317.aspx

15 BEGIN TRANSACTION (Transact-SQL)https://msdn.microsoft.com/en-us/library/ms188929.aspx

16 RESTORE (Transact-SQL)https://msdn.microsoft.com/en-us/library/ms186858(v=SQL.105).aspx

17 Using Multiple Active Result Sets (MARS)https://msdn.microsoft.com/en-us/library/ms131686.aspx

18 BEGIN DISTRIBUTED TRANSACTION (Transact-SQL)https://msdn.microsoft.com/en-us/library/ms188386.aspx

19 Rollbacks and Commits in Stored Procedures and Triggershttps://msdn.microsoft.com/en-us/library/ms187844.aspx