Database Reference
In-Depth Information
C H A P T E R 8
Understanding Transactions
For any business, transactions play a key role. They may comprise many individual operations and even
other transactions. Transactions are essential for maintaining data integrity both for multiple related
operations and when multiple users update the database concurrently.
This chapter will talk about the concepts related to transactions and how transactions can be used
in SQL Server 2012.
In this chapter, I'll cover the following:
•
What is a transaction?
•
When to use transactions
•
Understanding ACID properties
•
Transaction design
•
Transaction state
•
Specifying transaction boundaries
•
T-SQL statements allowed in a transaction
•
Local transactions in SQL Server 2012
•
Distributed transactions in SQL Server 2012
•
Guidelines to code efficient transactions
•
How to code transactions
What Is a Transaction?
A
transaction
is a set of operations performed so all operations are guaranteed to succeed or fail as one
unit.
A common example of a transaction is the process of transferring money from a checking account to
a savings account. This involves two operations: deducting money from the checking account and
adding it to the savings account. Both must succeed together and be
committed
to the accounts, or both
must fail together and be
rolled back
so that the accounts are maintained in a consistent state. Under no
circumstances should money be deducted from the checking account but not added to the savings
account (or vice versa). By using a transaction, both the operations, namely, debit and credit, can be
guaranteed to succeed or fail together. So, both accounts remain in a consistent state all the time.
