Sql server® 2012 t-sql fundamentals

  

297

C H A P T E R   9

Transactions and Concurrency

T

his chapter covers transactions and their properties and describes how Microsoft SQL Server handles 

users who are concurrently trying to access the same data. I explain how SQL Server uses locks to 

isolate inconsistent data, how you can troubleshoot blocking situations, and how you can control the 

level of consistency when you are querying data with isolation levels. This chapter also covers dead-

locks and ways to mitigate their occurrence.

Transactions

A transaction is a unit of work that might include multiple activities that query and modify data and 

that can also change data definition. 

You can define transaction boundaries either explicitly or implicitly. You define the beginning of a 

transaction explicitly with a BEGIN TRAN (or BEGIN TRANSACTION) statement. You define the end of 

a transaction explicitly with a COMMIT TRAN statement if you want to confirm it and with a ROLLBACK 

TRAN (or ROLLBACK TRANSACTION) statement if you do not want to confirm it (that is, if you want 

to undo its changes). Here’s an example of marking the boundaries of a transaction with two INSERT 

statements.

BEGIN TRAN; 

  INSERT INTO dbo.T1(keycol, col1, col2) VALUES(4, 101, 'C'); 

  INSERT INTO dbo.T2(keycol, col1, col2) VALUES(4, 201, 'X'); 

COMMIT TRAN;

If you do not mark the boundaries of a transaction explicitly, by default, SQL Server treats each in-

dividual statement as a transaction; in other words, by default, SQL Server automatically commits the 

transaction at the end of each individual statement. You can change the way SQL Server handles im-

plicit transactions with a session option called IMPLICIT_TRANSACTIONS. This option is off by default. 

When this option is on, you do not have to specify the BEGIN TRAN statement to mark the beginning 

of a transaction, but you have to mark the transaction’s end with a COMMIT TRAN or a ROLLBACK 

TRAN statement. 

www.it-ebooks.info

---

298  

Microsoft SQL Server 2012 T-SQL Fundamentals

Transactions have four properties—atomicity, consistency, isolation, and durability—abbreviated 

with the acronym ACID.

■

■

Atomicity  A transaction is an atomic unit of work. Either all changes in the transaction take 

place or none do. If the system fails before a transaction is completed (before the commit in-

struction is recorded in the transaction log), upon restart, SQL Server undoes the changes that 

took place. Also, if errors are encountered during the transaction, normally SQL Server auto-

matically rolls back the transaction, with a few exceptions. Some errors, such as primary key 

violation and lock expiration timeout (discussed later in this chapter, in the “Troubleshooting 

Blocking” section), are not considered severe enough to justify an automatic rollback of the 

transaction. You can use error-handling code to capture such errors and apply some course of 

action (for example, log the error and roll back the transaction). Chapter 10, “Programmable 

Objects,” provides an overview of error handling.

Tip

  At any point in your code, you can tell programmatically whether you are in an 

open transaction by querying a function called @@TRANCOUNT. This function re turns 

0 if you’re not in an open transaction and returns a value greater than 0 if you are.

■

■

Consistency  The term consistency refers to the state of the data that the RDBMS gives you 

access to as concurrent transactions modify and query it. As you can probably imagine, con-

sistency is a subjective term, which depends on your application’s needs. The “Isolation Levels” 

section later in this chapter explains the level of consistency that SQL Server provides by de-

fault and how you can control consistency if the default behavior is not suitable for your appli-

cation. Consistency also refers to the fact that the database must adhere to all integrity rules 

that have been defined within it by constraints (such as primary keys, unique constraints, and 

foreign keys). The transaction transitions the database from one consistent state to another.

■

■

Isolation  Isolation is a mechanism used to control access to data and ensure that transac-

tions access data only if the data is in the level of consistency that those transactions expect. 

SQL Server supports two different models to handle isolation: a traditional one based on lock-

ing and a newer one based on row versioning. The model based on locking is the default in an 

on-premises SQL Server installation. In this model, readers require shared locks. If the cur-

rent state of the data is inconsistent, readers are blocked until the state of the data becomes 

consistent. The model based on row versioning is the default in Windows Azure SQL Database. 

In this model, readers don’t take shared locks and don’t need to wait. If the current state of 

the data is inconsistent, the reader gets an older consistent state. The “Isolation Levels” section 

later in this chapter provides more details about both ways of handling isolation.

■

■

Durability  Data changes are always written to the database’s transaction log on disk before 

they are written to the data portion of the database on disk. After the commit instruction 

is recorded in the transaction log on disk, the transaction is considered durable even if the 

change hasn’t yet made it to the data portion on disk. When the system starts, either normally 

or after a system failure, SQL Server inspects the transaction log of each database and runs a 

recovery process with two phases—redo and undo. The redo phase involves rolling forward 

www.it-ebooks.info

---