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.

&nbsp

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.




Array

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |