Database Isolation levels
Managing queries on a database becomes challenging when multiple users are trying to fetch/update information concurrently. As your application grows and attracts more users, there is a need to ensure integrity and prevent conflicts. Here database locking comes into the picture to address these concerns. It is one of the fundamental aspect of the ACID properties of a database.
Isolation levels define the degree to which transactions are isolated from one another. Isolation is achieved by using locks to control concurrent access to data.
Locks are mechanisms used to control access to data and prevent simultaneous modifications that could result in inconsistencies or errors.
Why do we need database isolation?
Let’s say we want to implement e-commerce website handling, where numerous operations occur simultaneously. Database isolation plays a crucial role in maintaining consistency. Here’s how isolation controls various aspects to ensure data integrity:
- Isolation levels determine the duration of read locks, balancing concurrency and consistency. Example: Holding a read lock for a short duration to allow multiple customers to view product information simultaneously.
- Isolation controls whether locks are taken when reading data and the type of locks requested, preventing conflicts. Example: Acquiring a lock when a customer wants to purchase a limited-stock item to avoid overselling.
- Isolation determines whether a read operation referencing modified data blocks until the exclusive lock is released or retrieves the committed version. Example: Blocking a read operation until a transaction updating the product price completes to ensure accurate pricing information.
- High isolation levels, like serializable, provide strong consistency by isolating transactions completely. Example: Ensuring that concurrent customers purchasing the same item see consistent inventory availability.
- Lower isolation levels, such as read committed or read uncommitted, prioritize concurrency but may allow reading uncommitted or inconsistent data. Example: Allowing customers to view product details even if another customer is in the process of updating them.
- Selecting the appropriate isolation level balances data integrity and concurrency based on specific requirements. Example: Choosing a higher isolation level for critical operations like processing payments and a lower level for less critical actions like viewing product descriptions.
- Database isolation ensures that concurrent operations, such as purchasing, price changes, and product deliveries, are handled consistently and accurately in a large e-commerce system.
Problems without proper isolation
Without proper isolation, conflicts and data inconsistencies can occur. Let’s explore these scenarios, we will be using an account table to illustrate scenarios.
- Dirty Read: A dirty read occurs when one transaction reads data that has been modified by another transaction but has not been committed yet.
Here T2 reads the value of a, while T1 is still updating it. If T2 uses this uncommitted data for further processing, it may lead to incorrect calculations or decisions.
To demonstrate dirty reads, we need to set the isolation level to READ UNCOMMITTED.
Here I have not committed T1(updating the value of balance to 100), still, T2 is able to read uncommitted data.
2. Non-Repeatable Reads: A transaction reads the same data multiple times within its duration and obtains different results each time. This inconsistency arises due to other concurrent transactions modifying and committing the data between the reads.
Here T1 reads the value of a, while T2 is still updating it and after T2 commits its changes ,T1 reads the updated value of a in the same transaction.
To demonstrate non-repeatable reads, you can use the “Read Committed” isolation level. However, it prevents dirty reads.
The “Read Committed” isolation level allows each read to see only the committed data at the time of reading. If data is modified or deleted and committed between the reads within the same transaction, non-repeatable reads can occur.
3. Phantom Reads: Phantom reads happen when a transaction retrieves a set of rows based on a certain condition, but another concurrent transaction inserts or deletes rows that meet the same condition. As a result, the set of rows appears to change “phantomly.”
Here T1 fetches the rows of tableA, while T2 is still updating it and after T2 commits its changes ,T1 fetches the rows of tableA in same transaction and number of rows are different for same query.
In above example, output1 != output2 for the same query in same transaction.
Database isolation level
To prevent all above scenarios, we need to define degree of isolation at database level. They determine how transactions interact with each other and the level of data consistency and integrity guaranteed by the system. Here are the commonly used database isolation levels:
- Read Uncommitted: We have already discussed it while discussing Dirty read. Please refer dirty read section to know more. This is the lowest level of isolation, and does almost nothing.
- Allows transactions to read uncommitted and potentially inconsistent data modified by other concurrent transactions.
- Does not enforce any locks, allowing for high concurrency but risking dirty reads, non-repeatable reads, and phantom reads.
2. Read Committed: We have already discussed it while discussing Non-Repeatable Reads and Phantom read. Please refer that section to know more.
- Ensures that transactions only read committed data, preventing dirty reads.
- Acquires read locks on accessed data, preventing dirty reads but allowing non-repeatable reads and phantom reads.
3. Repeatable Read: The most popular isolation level is REPEATABLE_READ,
- Makes sure that any transaction that reads data from a row, blocks any other concurrent writing transactions from accessing the same row.
- Acquires locks on accessed data, preventing dirty reads and non-repeatable reads but allowing phantom reads.
Here output1 and output2 for the same query in same transaction fetches different number of rows.
4. Serializable:
- Provides the highest level of isolation by ensuring that transactions execute as if they were serialized, one after the other.
- Acquires range locks or table-level locks, preventing dirty reads, non-repeatable reads, and phantom reads, but may result in decreased concurrency.
Choice of transaction isolation level
Here are the key points to consider when choosing a transaction isolation level:
- Data Consistency: Higher isolation levels like REPEATABLE READ or SERIALIZABLE provide stronger consistency guarantees by preventing concurrency anomalies.
- Concurrency: Lower isolation levels like READ COMMITTED or READ UNCOMMITTED allow for higher concurrency but may introduce potential anomalies like dirty reads, non-repeatable reads, or phantom reads.
- Application Requirements: Consider the specific data integrity needs and critical operations of your application to determine the appropriate isolation level.
- Performance: Higher isolation levels may impact performance due to increased locks and reduced concurrency. Evaluate the performance implications for your specific use case.
