In many situations, data modification requires several steps. For example, you may need to change the values in two separate tables. You can use transactions to complete these two operations as a unit, or if an error occurs, does not change any of the tables. Another key consideration is that most databases must be compatible with many operations simultaneously. You have several options to configure how data is locked while it is being modified by a user. In the first part of Transaction and Locking article series, you will learn fundamentals about SQL Server transactions, how they work and how to manage them, and how to write procedures that use transactions.
 
Transaction fundamentals
A transaction is a series of SQL command statements and procedures that are executed as a single logical unit. A successful transaction must exhibit the following four properties:
- Atomicity – The transaction is a single unit of work in which all steps or no steps are completed.
- Consistency – The transaction must leave all data in a consistent state.
- Isolation –The changes made by concurrent transactions must be isolated from each other, which mean that no transaction should find data in an indeterminate state (in the process of change).
- Durability – The changes made by the transaction are persisted.
SQL Server commits (writes permanently) the statements in a transaction to the database only after they all execute successfully. This is an example of the property of atomicity. Atomicity is critical when either all or none of the manipulations must be completed together. If the statements in a transaction fail, the developer undoes the entire transaction. When a transaction is rolled back, SQL Server doesn’t commit any statements to the database. You enforce consistency through database objects such as:
- Primary keys constraints
- Foreign key constraints
- Check constraints
- Triggers
SQL Server is responsible for internal consistency, such as making sure that internal data structures aren’t corrupted. You enforce isolation through transaction isolation settings. You can control how strongly isolation is enforced, whether or not there is any access allowed to the data while a transaction is applied, and the scope of the isolation. Isolation scope varies from a single row to an entire database.
You enforce durability when you commit a transaction. SQL Server uses the transaction log to ensure durability by maintaining a copy of the transaction until it is physically applied to the database objects.
How transactions work
The following is an example of how transactions work. You have two tables: Savings and Checking. When you transfer money from Savings to Checking, two actions must occur:
- The amount must be deducted from the Balance column in Savings.
- The amount must be added to the Balance column in Checking.
If only one of these actions occur, the data is inconsistent. You can ensure data consistency by using transactions.
Transaction log role
The transaction log makes ACID transaction possible.
- Transactions are written to the transaction log.
- Should a system failure occur, or after recovering from backups, SQL Server processes the transaction log contents to bring the database tables and indexes to a consistent state up to the point of failure.
- Completed and committed transactions are applied (rolled forward.)
- Incomplete or uncommitted transactions are rolled back.
Transaction modes
SQL Server supports the following transaction modes:
- Explicit – Statement must explicitly begin, commit, and roll back each transaction.
- Implicit – Next transaction begins automatically when the previous transaction commits or is rolled back.
- Autocommit – Each statement is its own transaction.
- Batch-scoped – All transactions that start under a multiple active result sets (MARS) session are part of a batch-scoped transaction and any transactions not completed when the batch completes are rolled back.
You manage transactions separately for each connection. You can configure a different transaction mode for each connection as needed. Unless otherwise specified, SQL Server operates in autocommit mode with each statement treated as a stand-alone transaction.
Multiple Active Result Sets (MARS)
Previous SQL Server versions didn’t permit multiple active statements in a single connection. The applications had to either process or cancel the result sets from one batch before executing a same batch on the same connection. This met the needs of most, but not all, applications.
SQL Server 2005introduced support for multiple active result sets (MARS) in applications accessing the Database Engine. This means an application can maintain multiple results sets, known as multiple active result sets (MARS) on a single connection. MARS operates by interleaving requests, and not processing requests in parallel. MARS support is disabled by default. For more on MARS, refer to Using Multiple Active Result Sets (MARS)
Transaction statements
You manage transactions by grouping a set of statements. You group the statements by defining the beginning, end, and between these, savepoints and rollback points for explicit transactions. SQL Server supports the following statements for implementing transactions:
- BEGIN TRANSACTION – Identifies the beginning of a transaction. After it is opened, a transaction remains open until committed or rolled back.
- COMMIT TRANSACTION – Identifies the end of a transaction. The statement informs SQL Server that it has all of the statements necessary to complete the transaction.
- SAVE TRANSACTION – Places a savepoint in the transaction as a safe place to which the transaction can be rolled back. You can reference the name in a ROLLBACK TRANSACTION statement.
- ROLLBACK TRANSACTION – Rolls a transaction back to the last savepoint or a savepoint specified by name. If a savepoint has not been set, the transaction rolls back to the beginning of the transaction. If the transaction is already committed, it cannot be rolled back.
The @@TRANCOUNT function returns the number of open transactions for a connection. Each time a transaction is started, SQL Server increments @@TRANCOUNT by 1. Each time a transaction commits, SQL Server decrements @@TRANCOUNT by 1. If a transaction rolls back to the beginning (not to a savepoint), SQL Server sets @@TRANCOUNT to 0. You most frequently use @@TRANCOUNT when you need to nest transactions. A nested transaction is a transaction defined inside the boundaries of another transaction. For more information, refer to Nested Transactions.
The BEGIN TRANSACTION statement
The syntax for the BEGIN TRANSACTION statement is:
BEGIN TRAN[SACTION] [transaction_name | @tran_name_var [WITH MARK ['description']]]
You can use the transaction_name or @tran_name_var (transaction name variable) value to assign an identifier to the transaction. The transaction name is required if you use the WITH MARK clause. When you define a transaction by using WITH MARK, the description is entered in the transaction log. You can use transaction marks during database restore to restore to the point of a specific transaction.
The COMMIT TRANSACTION statement
The syntax for the COMMIT TRANSACTION statement is:
COMMIT [TRAN[SACTION]] [transaction_name | @tran_name_var]]
If you execute COMMIT TRAN when @@TRANCOUNT is equal to zero, SQL Server generates an error because there isn’t a corresponding BEGIN TRAN.
Alternately, you can run commit transactions with:
COMMIT [WORK]
COMMIT specified by itself with the WORK keyword is SQL-92 compatible.
The SAVE TRANSACTION statement
You use the SAVE TRANSACTION statement to establish savepoints in a transaction that allow partial rollbacks. SQL Server doesn’t release transaction resources until the entire transaction either commits or rolls back.
The syntax for SAVE TRANSACTION is:
SAVE TRAN[SACTION] savepoint_name | @savepoint_name_var
You must use a valid identifier for the savepoint name. SQL Server doesn’t support savepoints for distributed transactions. A distributed transaction is one that involves actions on multiple servers.
The ROLLBACK TRANSACTION statement
The ROLLBACK TRANSACTION statement rolls a transaction back to the beginning of the transaction or to a savepoint set inside of the transaction. When you roll back a transaction, all changes are erased either to the defined savepoint (if one exists) or to the beginning of the transaction. The syntax for this command is:
ROLLBACK TRAN[SACTION] [transaction_name | @tran_name_var | savepoint_name | @savepoint_name_var]
If you use ROLLBACK TRAN in a trigger, any statements in the trigger that follow the rollback still execute. However, after the trigger ends, the batch that called the statement that caused the trigger to fire is terminated. ROLLBACK TRAN works differently if it occurs inside a stored procedure. The statements in the batch that called the stored procedure still execute. The SQL-92 compatible version of this command is:
ROLLBACK [WORK]
ROLLBACK WORK is essentially the same as ROLLBACK TRAN except that transaction names and savepoint names aren’t supported.
Transaction example
As an example, you have two stored procedures that need to be completed with atomicity. This example assumes that you have already defined two stored procedures named rev_inv and rev_cust that are used for updating database tables and that the transaction is being used inside of a stored procedure that defines the @paid, @invdate, and @invnum variables. To define this as a transaction, you run:
BEGIN TRAN cust_pay
EXEC rev_inv @paid, @invnum
EXEC rev_cust @paid, @invdate
COMMIT TRAN
To modify the example to use structured error checking, you run the following:
BEGIN TRAN cust_pay
BEGIN TRY
EXEC rev_inv @payed, @invnum
EXEC rev_cust @payed, @invdate
COMMIT TRAN
END TRY
BEGIN CATCH
print "An error occurred"
ROLLBACK TRAN
END CATCH
You have some control over how SQL Server reacts to errors. By default, if a run-time statement error occurs during batch processing; only the statement that caused the error is rolled back automatically by the database engine. You control this behaviour by using the XACT_ABORT option, which is set to OFF by default. The syntax of this option is:
SET XACT_ABORT ON | OFF
When you set the XACT_ABORT option ON and a run-time error occurs, the database engine rolls back the complete current transaction. However, rather than relying on automatic rollbacks, you should include explicit error handling code in batches that execute transactions.
Implicit transactions
To turn support for implicit transactions on and off, you use the following:
SET IMPLICIT_TRANSACTIONS ON | OFF
When set to ON, a transaction will start when ALTER TABLE, CREATE, DELETE, DROP, FETCH, GRANT, INSERT, OPEN, REVOKE, SELECT, TRUNCATE TABLE, or UPDATE is executed and a transaction isn’t already open. When the IMPLICIT_TRANSACTION option is set to ON, you must commit or roll back the transaction. If not, SQL Server rolls back changes when the user disconnects. When you have the IMPLICIT_TRANSACTION option set to OFF, each statement is considered a transaction (autocommit mode).
Illegal statements and considerations
You shouldn’t use certain statements in user-defined transactions because the functions they perform can’t be undone. Rollbacks must be able to undo functions; therefore, you should avoid using the following statements in a transaction:
- CREATE TABLE, ALTER TABLE, TRUNCATE TABLE
- CREATE INDEX
- All DROP statements
- SELECT…INTO
- GRANT or REVOKE
- DISK INIT, LOAD DATABASE, LOAD TRANSACTION
- Any user or system stored procedure that uses any of the preceding statements
In many cases, SQL Server doesn’t generate an error when you use these statements in a transaction. However, they shouldn’t be used due to the nature of the statements. In addition, the database engine generates an error when you attempt to execute any of the following statements inside a transaction:
- CREATE DATABASE, ALTER DATABASE, DROP DATABASE
- BACKUP, RESTORE
- RECONFIGURE
- UPDATE STATISTICS
In the second part, I will discuss locking overview, transaction isolation level, and SQL Server locking options.
]]>