SQL Server Architecture - SQL Server 2012 Internals and Troubleshooting - page 4

You will also discover when the transaction log is used and how it's affected by the coni gured
recovery model; what happens when a checkpoint occurs and how you can inl uence the frequency;
and what the lazy writer does.
The chapter starts by dei ning a “transaction” and outlining the database system's requirements to
reliably process them. You'll then look at the life cycle of a simple query that reads data, looking at
the components employed to return a result set, before examining how the process differs when data
needs to be modii ed.
Finally, you'll learn about the components and terminology that support the recovery process in
SQL Server, and the SQLOS “framework” that consolidates a lot of the low-level functions required
by many SQL Server components.
NOTE Coverage of some areas of the life cycle described in this chapter is
intentionally shallow in order to keep the l ow manageable; where that's the case,
you are directed to the chapter or chapters that cover the topic in more depth.
DATABASE TR ANSACTIONS
A transaction is a unit of work in a database that typically contains several commands that read
from and write to the database. The most well-known feature of a transaction is that it must
complete all the commands in their entirety or none of them. This feature, called atomicity , is just
one of four properties dei ned in the early days of database theory as requirements for a database
transaction, collectively known as ACID properties.
ACID Properties
The four required properties of a database transaction are atomicity, consistency, isolation, and
durability.
Atomicity
Atomicity means that all the effects of the transaction must complete successfully or the changes are
rolled back. A classic example of an atomic transaction is a withdrawal from an ATM machine; the
machine must both dispense the cash and debit your bank account. Either of those actions completing
independently would cause a problem for either you or the bank.
Consistency
The consistency requirement ensures that the transaction cannot break the integrity rules of the
database; it must leave the database in a consistent state. For example, your system might require
that stock levels cannot be a negative value, a spare part cannot exist without a parent object, or the
data in a sex i eld must be male or female. In order to be consistent, a transaction must not break
any of the constraints or rules dei ned for the data.