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.