Database Reference
In-Depth Information
Isolation Level
Read
Uncommitted
Read
Committed
Repeatable
Read
Serializable
Dirty Read
Nonrepeatable
Read
Phantom Read
Possible
Possible
Not Possible
Possible
Not Possible
Not Possible
Not Possible
Not Possible
Problem
Type
Possible
Possible
Possible
Not Possible
Figure 9-12
Summary of transaction
Isolation Levels
A
Nonrepeatable read
occurs when a transaction rereads data it has previously read
and finds modifications or deletions caused by a committed transaction.
●
A
Phantom read
occurs when a transaction rereads data and finds new rows that
were inserted by a committed transaction since the prior read.
●
In order to deal with these potential data read problems, the SQL standard defines four
transaction isolation levels
or
isolation levels
that control which of these problems are
allowed to occur. Using these SQL defined isolation levels, the application programmer can
declare the type of isolation level he or she wants, and the DBMS will create and manage locks
to achieve that level of isolation.
These transacation isolation levels are summarized in Figure 9-12, and can be defined as:
the
read-uncommitted isolation level
allows dirty reads, nonrepeatable reads, and
phantom reads to occur.
●
The
read-committed isolation level
allows nonrepeatable reads and phantom
reads, but disallows dirty reads.
●
The
repeatable-read isolation level
allows phantom reads, but disallows both dirty
reads and nonrepeatable reads.
●
The
serializable isolation level
will not allow any of these three data read problems
to occur.
●
Generally, the more restrictive the level, the less the throughput, though much depends on
the workload and how the application programs are written. Moreover, not all DBMS products
support all of these levels. As usual, the support of SQL transaction isolation levels varies between
DBMS products, and you will learn how SQL Server 2012, Oracle Database 11
g
Release 2, and
MySQL 5.6 support isolation levels in Chapter 10A, Chapter 10B, and Chapter 10C respectively.
1
SQL Cursors
An
SQL cursor
or
cursor
is a pointer into a set of rows. SQL cursors are usually defined in
an
SQL DECLARE CURSOR statement
that defines the cursor by using an SQL SELECT
statement. For example, the following DECLARE CURSOR statement defines a cursor named
TransCursor that operates over the set of rows indicated by the included SELECT statement:
/* *** EXAMPLE CODE - DO NOT RUN *** */
/* *** SQL-Code-Example-CH09-02 *** */
DECLARE CURSOR TransCursor AS
SELECT *
FROM
TRANS
WHERE
PurchasePrice > '10000';
As was explained in Chapter 7, after an application program opens a cursor, it can place
the cursor somewhere in the result set. Most commonly, the cursor is placed on the first or last
row, but other possibilities exist.
1
For more information about transaction isolation levels, see the Wikipedia article Isolation (database systems)
Search WWH ::
Custom Search