SQL Bible [Electronic resources] نسخه متنی

Summary

All communications with RDBMS happen within
the context of a session. When a session between a client program and RDBMS is
established, it possesses certain default properties that determine its
behavior. Some of these can be changed for the duration of the session, and the
database administrator can change the defaults. Some parameters may be changed
through use of SQL statements; some are client-dependent and must be set in the
client's environment.

The next level of granularity is
transactions — when one or more SQL statements comprise a single logical unit
of work. Within the session, an SQL statement runs as a transaction — by the
SQL standard definition. RDBMS implementations may treat it differently, some
starting an implicit transaction by default, and some requiring explicit
statements to begin a transaction. Transactions must satisfy certain criteria
(the so-called ACID test) to comply with these standards, but these details are
usually taken care of by the RDBMS itself.

Transactions accessing shared resources
must implement some concurrency control. One of a transaction's properties is
its isolation level established for the transaction. The isolation level
regulates what this transaction may access, and what data it is allowed to
"see."

There are four isolation levels defined by
the SQL standard and some RDBMS (Microsoft SQL Server 2000 and IBM DB2 UDB)
have implemented all of them, while some (Oracle 9i being
one example) have implemented only two.

Some RDBMS implement intricate locking
systems to address the concurrency issue, though locks are not part of the SQL
standard. The locks might be of different types; they can be specified within
the SQL statement itself, or they may be specified properties of the session. A
deadlock situation may occur in a high-volume of transactions or improperly
designed systems. Deadlocks may be resolved automatically by the RDBMS or
manually by database administrators.