Database Isolation Levels and Serialization for Transactions

Before this chapter delves into a detailed explanation of how locks work within Oracle, it would be beneficial to quickly review a few basics on how transactions operate within the relational database model.

In terms of how database isolation levels function, the ANSI/ISO SQL standard (SQL92) breaks these down into four levels of transaction isolation. Each isolation level contains various degrees of impact on transaction processing. The isolation levels are explained in terms of the following three phenomena that must be prevented between concurrently executing transactions:

• Dirty reads - Dirty reads occur when a transaction reads data that has been written by another transaction that has not yet been committed.
  
• Fuzzy or non-repeatable reads - Fuzzy reads occur when a database transaction re-reads data it has already read and then finds that another committed transaction has modified or deleted the same data.
  
• Phantom reads - Phantom reads occur when a transaction in Oracle re-executes a query that returns the rows that satisfy a particular search and discovers that another committed transaction has already inserted additional rows that meet the condition.

The database standard for SQL92 expounds these four levels of isolation as shown in the following table.

Isolation Level	Dirty Read	Nonrepeatable Read	Phantom Read
Read uncommitted	Possible	Possible	Possible
Read committed	Not possible	Possible	Possible
Repeatable read	Not possible	Not possible	Possible
Serializable	Not possible	Not possible	Not possible

The Oracle database uses the read committed and serializable isolation levels in addition to a read-only mode. By default, Oracle uses the Read committed isolation level for database transactions. Next to be examined are how locks operate within Oracle.

Since Oracle is a database system that permits hundreds, if not thousands, of multiple users to access data, it requires a method to lock data in order to resolve problems associated with data concurrency, consistency, and integrity. Here is where locks enter the picture.

Database locks are mechanisms that prevent errors from occurring that could cause dire consequences during user interaction between transactions accessing the same particular resource within Oracle. As such, these resources contain two basic types of data locks for user objects such as tables and data and system objects which are the hidden underlying data structures within the database engine such as the Oracle data dictionary and memory.

For all purposes of explanation, the Oracle database will automatically obtain the necessary locks when SQL statements are executed by users. This process is hidden from users so that they are able to perform tasks without manual intervention.  By default, Oracle will use the lowest possible level of restriction to provide for the highest rate of data concurrency while also providing perfect data integrity. In addition, users have the ability to lock data manually within Oracle based on the nature of the application design.

As was mentioned earlier, locks function to protect data integrity and consistency within the Oracle database environment. Locks operate both implicitly when SQL statements are executed by users as well as explicitly when user application design dictates that locking be performed in such a manner. Explicit locking prevents the resource from being shared by other users so that a particular application user can modify data without interference.

Share locks allow resources to be shared based on the nature of the operation performed. Multiple transactions can obtain a shared lock on the same resource within Oracle.