Datasheet

14
Part I ✦ Laying the Foundation
SQL Server, the hardware, the database design, the code, the database-recovery plan, and the
database-maintenance plan. When the database developer and DBA cooperate to properly
implement all these components, the database performs well and transactional integrity is high.
Transactional Faults
True isolation means that one transaction never affects another transaction. If the isolation
is complete, then no data changes from outside the transaction should be seen by the
transaction.
The isolation between transactions can be less than perfect in one of three ways: dirty reads,
non-repeatable reads, and phantom rows. In addition, transactions can fail due to lost
updates and deadlocks.
Dirty Reads
The most egregious fault is a transaction’s work being visible to other transactions before the
transaction even commits its changes. When a transaction can read another transaction’s
uncommitted updates, this is called a dirty read. The problem with dirty reads is that the data
being read is not yet committed, so the transaction writing the data might be rolled back.
Non-Repeatable Reads
A non-repeatable read is similar to a dirty read, but a non-repeatable read occurs when a
transaction can see the committed updates from another transaction. Reading a row inside a
transaction should produce the same results every time. If reading a row twice results in dif-
ferent values, that’s a non-repeatable read type of transaction fault.
Phantom Rows
The least severe transactional-integrity fault is a phantom row. Like a non-repeatable read, a
phantom row occurs when updates from another transaction affect not only the result set’s
data values, but cause the
select to return a different set of rows.
Of these transactional faults, dirty reads are the most dangerous, while non-repeatable reads
are less so, and phantom rows are the least dangerous of all.
Lost Updates
A lost update occurs when two users edit the same row, complete their edits, and save the
data, and the second user’s update overwrites the first user’s update.
Because lost updates occur only when two users edit the same row at the same time, the
problem might not occur for months. Nonetheless, it’s a flaw in the transactional integrity of
the database that needs to be prevented.
Deadlocks
A deadlock is a special situation that occurs only when transactions with multiple tasks com-
pete for the same data resource. For example, consider the following scenario:
✦ Transaction one has a lock on data A and needs to lock data B to complete its
transaction.
and
✦ Transaction two has a lock on data B and needs to lock data A to complete its
transaction.
05_542567 ch01.qxp 9/27/06 9:58 PM Page 14