--- PDF PAGE 682 ---
675
Transactional Anomalies
Transactional anomalies can cause queries to return unpredictable results. Three types 
of transactional anomalies are possible within SQL Server: dirty reads, nonrepeatable 
reads, and phantom reads. These are discussed in the following sections.
Dirty Reads
A dirty read occurs when a transaction reads data that never existed in the database. An 
example of how this anomaly can occur is outlined in Table 18-7.
Table 18-7.  A Dirty Read
Transaction1
Transaction2
Inserts row1 into Table1
Reads row1 from Table1
Rolls back
Table 18-8.  A Nonrepeatable Read
Transaction1
Transaction2
Reads row1 from Table1
Updates row1 in Table1
Commits
Reads row1 from Table1
In this example, because Transaction1 rolled back, Transaction2 read a row that 
never existed in the database. This anomaly can occur if shared locks are not acquired for 
reads, since there is no lock to conflict with the exclusive lock taken out by Transaction1.
Nonrepeatable Read
A nonrepeatable read occurs when a transaction reads the same row twice but receives 
different results each time. An example of how this anomaly can occur is outlined in 
Table 18-8.
Chapter 18  Locking and Blocking

--- PDF PAGE 682 ---
675
Transactional Anomalies
Transactional anomalies can cause queries to return unpredictable results. Three types 
of transactional anomalies are possible within SQL Server: dirty reads, nonrepeatable 
reads, and phantom reads. These are discussed in the following sections.
Dirty Reads
A dirty read occurs when a transaction reads data that never existed in the database. An 
example of how this anomaly can occur is outlined in Table 18-7.
Table 18-7.  A Dirty Read
Transaction1
Transaction2
Inserts row1 into Table1
Reads row1 from Table1
Rolls back
Table 18-8.  A Nonrepeatable Read
Transaction1
Transaction2
Reads row1 from Table1
Updates row1 in Table1
Commits
Reads row1 from Table1
In this example, because Transaction1 rolled back, Transaction2 read a row that 
never existed in the database. This anomaly can occur if shared locks are not acquired for 
reads, since there is no lock to conflict with the exclusive lock taken out by Transaction1.
Nonrepeatable Read
A nonrepeatable read occurs when a transaction reads the same row twice but receives 
different results each time. An example of how this anomaly can occur is outlined in 
Table 18-8.
Chapter 18  Locking and Blocking

--- PDF PAGE 683 ---
676
In this example, you can see that Transaction1 has read row1 from Table1 twice. 
The second time, however, it receives a different result, because Transaction2 has 
updated the row. This anomaly can occur if Transaction1 takes out shared locks but 
does not hold them for the duration of the transaction.
Phantom Read
A phantom read occurs when a transaction reads a range of rows twice but receives a 
different number of rows the second time it reads the range. An example of how this 
anomaly can occur is outlined in Table 18-9.
Table 18-9.  Phantom Reads
Transaction1
Transaction2
Reads all rows from Table1
Inserts ten rows into Table1
Commits
Reads all rows from Table1
In this example, you can see that Transaction1 has read all rows from Table1 twice. 
The second time, however, it reads an extra ten rows, because Transaction2 has inserted 
ten rows into the table. This anomaly can occur when Transaction1 does not acquire a 
key-range lock and hold it for the duration of the transaction.
Isolation Levels
SQL Server provides four pessimistic and two optimistic transaction isolation levels 
for transactions that involve disk-based tables. Pessimistic isolation levels use locks 
to protect against transactional anomalies, and optimistic isolation levels use row 
versioning.
Pessimistic Isolation Levels
Read Uncommitted is the least restrictive isolation level. It works by acquiring locks for 
write operations but not acquiring any locks for read operations. This means that under 
this isolation level, read operations do not block other readers or writers. The result is 
that all transactional anomalies described in the previous sections are possible.
Chapter 18  Locking and Blocking

--- PDF PAGE 687 ---
680
SQL Server relaxed these rules, however, by introducing a feature called delayed 
durability. This feature works by delaying the flush of the log cache to disk until one of 
the following events occurs:
•	
The log cache becomes full and automatically flushes to disk.
•	
A fully durable transaction in the same database commits.
•	
The sp_flush_log system stored procedure is run against the 
database.
When delayed durability is used, the data is visible to other transactions as soon as 
the transaction commits; however, the data committed within the transaction could 
potentially be lost, if the instance goes down or is restarted, until the log records have 
been flushed. Support for delayed durability is configured at the database level, using 
one of the three options detailed in Table 18-10.
Table 18-10.  Support Levels for Delayed Durability
Support Level
Description
ALLOWED
Delayed durability is supported within the database and specified on a 
transaction level basis.
FORCED
All transactions within the database will use delayed durability.
DISABLED
The default setting. No transactions within the database are permitted to use 
delayed durability.
The command in Listing 18-5 shows how to allow delayed durability in the 
Chapter18 database.
Listing 18-5.  Allowing Delayed Durability
ALTER DATABASE Chapter18
SET DELAYED_DURABILITY  = ALLOWED ;
If a database is configured to allow delayed durability, then Full or Delayed durability 
is configured at the transaction level, in the COMMIT statement. The script in Listing 18-6 
demonstrates how to commit a transaction with delayed durability.
Chapter 18  Locking and Blocking