Part 2 — Database Transactions — Exploring Concurrency Control and Isolation Levels

Understanding a topic through intuition is key to learning.

In Part-1, we covered — What is Transactions, and the role of ACID properties in the Database to ensure data consistency.

Now, let’s understand Concurrency Control and Isolation Levels.

Concurrency Control

Just as the traffic lights regulate the flow, concurrency control governs the access and interaction of transactions, avoiding collisions and maintaining the smooth operation of the database.

Concurrency control mechanisms are important in managing simultaneous access to data within a database.

• Lock-Based Concurrency Control
• Two Phase Locking

Lock-Based Concurrency Control

1. Shared (Read) Locks: These locks allow multiple transactions to read data simultaneously but prevent any transaction from making changes while the data is being read. This type of lock is useful when multiple users need to access the same data concurrently without modifying it.
2. Exclusive (Write) Locks: Exclusive locks restrict access to a single transaction, ensuring that only one transaction can modify the data at any given time. This lock prevents other transactions from reading or writing to the data until the lock is released.

Two-Phase Locking

Two-phase locking includes Acquiring the lock and Releasing the lock.

In the Acquiring phase — Transactions acquire the locks they need before accessing or modifying the data.

Releasing the lock — After a transaction has acquired all necessary locks and completed the operations, it releases all the locks it acquired. Once the lock is released, other transactions can acquire the lock.

This will help in avoiding Deadlocks and the problem of Data inconsistency.

Isolation Levels

Imagine a shared kitchen with several chefs. Each chef works on a dish independently (higher isolation levels), avoiding conflicts over ingredients. on the other hand, some chefs freely share ingredients and space (lower isolation levels), risking clashes but allowing collaboration.

Isolation levels determine the degree of visibility and interaction between the concurrent transactions.

https://jlvbcoop.com/en/transaction-isolation-levels-2/

• Read Uncommitted: This is the lowest isolation level where a transaction can read uncommitted changes made by other transactions. It offers no guarantees about data consistency and can result in dirty reads, non-repeatable reads, and phantom reads.
• Read Committed: A transaction can only read committed data. It prevents dirty reads, but it might still encounter non-repeatable reads and phantom reads as other transactions commit changes concurrently.
• Repeatable Read: This ensures that once a record is read within a transaction, subsequent reads of the same record during the transaction will return the same data. It prevents non-repeatable reads but may still face phantom reads.
• Serializable: This is the highest level of isolation. It ensures strict transaction isolation by preventing concurrent transactions from affecting each other. However, please keep in mind — that strict locking can lead to performance issues.

In Part 3 — I will try to cover the real-world case studies so that you can relate and understand more in-depth about these concepts.

…Stay tuned

Thanks for reading :)

To learn more about me — https://www.linkedin.com/in/mayurssoni2456/