Managing Multiuser Databases - Database Processing: Fundamentals, Design, and Implementation - page 407

If the SQL statement is consistent, such update requests will be disallowed. Hence, the update
shown in SQL-UPDATE-CH09-03 will apply to the set of rows as they existed at the time the SQL
statement started. Such consistency is called statement-level consistency .
Now, consider a transaction (SQL-Code-Example-CH09-01) that contains two SQL
UPDATE statements as part (with possible other transaction actions) of a transaction marked
by SQL transaction boundaries:

/* *** EXAMPLE CODE - DO NOT RUN *** */
/* *** SQL-Code-Example-CH09-04 *** */
BEGIN TRANSACTION;
/* *** SQL-UPDATE-CH09-03 *** */
UPDATE CUSTOMER
SET AreaCode = '425'
WHERE ZIPCode = '98050';
— Other transaction work
/* *** SQL-UPDATE-CH09-04 *** */
UPDATE CUSTOMER
SET Discount = 0.05
WHERE AreaCode = '425';
— Other transaction work
COMMIT TRANSACTION;

In this context, what does consistent mean? Statement-level consistency means that
each statement independently processes rows consistently, but that changes from other
users to these rows might be allowed during the interval between the two SQL statements.
Transaction-level consistency means that all rows affected by either of the SQL statements
are protected from changes during the entire transaction.
Observe that transaction-level consistency is so strong that, for some implementations
of it, a transaction will not see its own changes. In this example, the SQL statement SQL-
Update-CH09-04 may not see rows changed by the SQL statement SQL-Update-CH09-03.
Thus, when you hear the term consistent, look further to determine which type of consis-
tency is meant. Be aware as well of the potential trap of transaction-level consistency.

Transaction Isolation Level
The term isolated has several different meanings. To understand those meanings, we need first
to define several new terms that describe various problems that can occur when we read data
from a database, and which are summarized in Figure 9-11.

A dirty read occurs when a transaction reads a row that has been changed but for
which the change has not yet been committed to the database. The danger of a dirty
read is that the uncommitted change can be rolled back. If so, the transaction that
made the dirty read will be processing incorrect data.

●

Figure 9-11
Summary of Data read
Problems

Data Read Problem Type Definition

Dirty Read The transaction reads a row that has been

changed, but the change has not been committed.
If the change is rolled back, the transaction has
incorrect data.

Nonrepeatable Read The transaction rereads data that has been

changed, and finds changes due to committed
transactions.

Phantom Read The transaction rereads data and finds new rows

inserted by a committed transaction.