Discovering SQL: A Hands-On Guide for Beginners

SUMMARY

All communications with RDBMSs 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 properties can be modified for the duration of the session, and the database administrator can make these modifications persistent. The enterprise level RDBMS provide transactional support ability to execute SQL statements as a batch, a single logical unit of work. SQL Standard stipulates that a SQL statement always runs as a transaction. RDBMS implementations may treat it differently: some start an implicit transaction by default, and some do not, 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.

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 access and modify.

Some RDBMSs implement intricate locking systems to address the concurrency issue, though locks are not part of SQL Standard. The locks might be of different types. They can be specified within the SQL statement or they can be specified as properties for the session. A deadlock situation may occur in a high-volume transaction ...