SQL Server Transactions and Locking – Part 1
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.
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
- 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
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
- Completed and committed transactions are applied (rolled
- Incomplete or uncommitted transactions are rolled back.
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
- 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,
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)
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
- 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
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:
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
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 is essentially the same as ROLLBACK TRAN except that transaction
names and savepoint names aren’t supported.
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
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.
To turn support for implicit transactions on and off, you use the
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
- CREATE TABLE, ALTER TABLE, TRUNCATE TABLE
- CREATE INDEX
- All DROP statements
- GRANT or REVOKE
- DISK INIT, LOAD DATABASE, LOAD TRANSACTION
- Any user or system stored procedure that uses any of the
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
- CREATE DATABASE, ALTER DATABASE, DROP DATABASE
- BACKUP, RESTORE
In the second
part, I will discuss locking overview, transaction isolation level, and SQL
Server locking options.