SQL:1999 specifies four main isolation levels—Read Uncommitted, Read Committed, Repeatable Read, and Serializable—each designed to manage the concurrent execution of transactions and prevent specific data anomalies. Each isolation level determines how much a transaction should be “isolated” from the effects of others, primarily by controlling the visibility of data changes between transactions.
Importantly, these isolation levels only impact read operations, as write locks are governed by a strict protocol regardless of the isolation level.
Write locks, following the strict two-phase locking (2PL) approach, are retained until the transaction completes. This prevents other transactions from modifying data that is currently being changed, ensuring data integrity.
In contrast, read operations are more flexible, with isolation levels controlling the degree of protection against interference from concurrent modifications by other transactions.
Long-duration write locks are crucial to maintain consistency in situations where transactions may abort. In cases where write locks are held only briefly, concurrent transactions can overwrite each other’s modifications, leading to issues if one transaction subsequently fails.
Example
Consider the following hypothetical schedule (admissible if write locks are short duration):
Transaction writes to an object .
Before completes, writes to the same object .
If then aborts (rollback occurs), the initial state of would need to be restored. However, this would inadvertently remove ‘s update if it committed after ‘s abort.
This issue, known as a “dirty write,” illustrates why write locks must be held until a transaction completes. If ’s changes need to be reverted, holding the write lock until transaction completion ensures that cannot overwrite or lose updates, thus preserving a coherent state for all objects. By holding these locks, the system allows for the correct handling of abort events, ensuring that concurrent transactions do not interfere with each other’s data integrity.
SQL isolation levels dictate protections against data anomalies such as dirty reads, non-repeatable reads, and phantoms. The behaviors of each isolation level regarding these anomalies are as follows:
READ UNCOMMITTED: This level provides the lowest isolation, allowing transactions to read data changes from other ongoing transactions, which can lead to inconsistencies. Specifically, dirty reads (reading uncommitted changes), non-repeatable reads (seeing changes to data between reads), and phantoms (seeing new records added by others during re-reads) are all possible. This level does not use read locks, meaning it disregards locks from other transactions, making it highly permissive but risky for consistency.
READ COMMITTED: This level prevents dirty reads by allowing transactions to see only committed data from others. However, it still permits non-repeatable reads and phantoms, as read locks are only held briefly during the read operation. Transactions in this mode obtain and release read locks immediately after each read, making them susceptible to changes by other transactions on re-reads, but preventing interactions with uncommitted data.
REPEATABLE READ: Transactions running at this level prevent both dirty reads and non-repeatable reads by enforcing long-duration read locks, which maintain data stability for each transaction. However, REPEATABLE READ does not protect against phantom rows—new rows added by other transactions can still be seen if the transaction re-reads data with an updated query. Here, 2PL is applied to both read and write locks, ensuring read data cannot be modified by others until the transaction completes.
SERIALIZABLE: This level provides the highest isolation, preventing all forms of read anomalies, including phantoms, through the use of long-duration predicate and data read locks. SERIALIZABLE mode makes each transaction appear as though it were executed in isolation from all others, as if transactions ran sequentially. Although this strict level of isolation effectively eliminates anomalies, it can lead to deadlocks, as transactions might hold locks that block each other. Due to its stringent locking requirements, SERIALIZABLE is not typically set as the default in commercial systems.
Summary of Anomaly Protections in Isolation Levels
The following table summarizes the anomaly protections provided by each SQL:1999 isolation level:
Isolation Level
Dirty Read
Non-repeatable Read
Phantoms
Read Uncommitted
✔️
✔️
✔️
Read Committed
❌
✔️
✔️
Repeatable Read
❌
❌
✔️ (insert)
Serializable
❌
❌
❌
SQL92 Serializable vs. Serial Execution
In SQL92, transactions operating at the SERIALIZABLE isolation level do not execute in a strictly serial order, meaning they do not necessarily run one after the other. Instead, the SERIALIZABLE isolation level ensures that transactions yield a result equivalent to some serial order. This requirement allows for concurrent execution, as long as the final state of the database is identical to what it would be if the transactions had run serially in any order. This approach maximizes efficiency while maintaining strict data consistency.
Achieving this isolation level requires a robust locking mechanism, typically involving long-duration locks on both read and write operations to prevent conflicts. However, the requirement for long-duration locks often increases the likelihood of deadlocks, where two transactions wait indefinitely for each other to release locks. In these cases, one transaction must be rolled back to resolve the deadlock, introducing an additional performance consideration. Consequently, the SERIALIZABLE isolation level is generally not the default setting in most commercial database systems, as it can lead to significant overhead, particularly in high-concurrency environments.
Each isolation level in SQL manages read and write locks differently, balancing data integrity with performance.
Isolation Level
Read Locks
Write Locks
READ UNCOMMITTED
Not required
Well-formed writes with long-duration locks
READ COMMITTED
Well-formed reads, short duration locks (data and predicate)
Well-formed writes with long-duration locks
REPEATABLE READ
Well-formed reads with long-duration data locks, short duration predicate locks
Long-duration write locks
SERIALIZABLE
Well-formed reads with long-duration locks (both predicate and data)
Long-duration write locks
In practice, isolation levels use an appropriate combination of read and write locks to ensure data consistency. Many commercial systems implement additional concurrency controls, such as timestamp-based mechanisms or multi-version concurrency control (MVCC), to provide further optimization without always enforcing long-duration locks.
Setting Transaction Characteristics in SQL
The SQL language provides flexibility in managing transaction properties through the SET TRANSACTION statement, which allows developers to configure critical transaction characteristics such as isolation levels and access modes. This configuration ensures that the transaction executes with the appropriate level of data isolation and access control, tailoring its behavior to specific application requirements.
The SET TRANSACTION syntax is defined as follows:
SET [ LOCAL ] TRANSACTION <transaction characteristics>
Within <transaction characteristics>, multiple modes can be specified to dictate how the transaction should operate. These modes include <isolation level>, which controls the transaction’s visibility to concurrent changes, and <transaction access mode>, which specifies whether the transaction can modify data. Each of these modes plays a role in determining the transaction’s integrity and concurrency behavior.
The <transaction access mode> defines whether a transaction is restricted to read-only operations or is allowed to make modifications. This mode can be set to:
READ ONLY: The transaction can view data but cannot make any updates. This is useful for reporting operations where consistency is critical but data integrity will not be compromised by modifications.
READ WRITE: The transaction has permission to read and modify data, which is the default mode for most transactions that involve data updates or inserts.
<transaction access mode> ::= READ ONLY | READ WRITE
The <isolation level> specifies the degree of data protection from concurrent transactions, effectively controlling the potential for anomalies such as dirty reads, non-repeatable reads, and phantom reads. The following isolation levels can be applied:
READ UNCOMMITTED: Allows reading uncommitted changes from other transactions, increasing concurrency but allowing data anomalies.
READ COMMITTED: Ensures only committed data is visible to the transaction, avoiding dirty reads while still permitting some anomalies like non-repeatable reads.
REPEATABLE READ: Prevents dirty and non-repeatable reads by holding locks on all data read during the transaction. It may still allow phantom reads, depending on the database system.
SERIALIZABLE: Guarantees full isolation by ensuring that the transaction operates as if it were the only one in the system, eliminating all potential anomalies but at a performance cost.
To set a transaction to SERIALIZABLE isolation with read-write access, the SQL command would be:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE, READ WRITE;
This configuration will make sure the transaction executes with the highest level of data integrity, although it may reduce concurrency performance due to stricter locking requirements. Through this level of customization, SQL enables developers to optimize transaction performance and data consistency as needed for specific application demands.