Isolation in DBMS & Advisory Locks

Levels of isolation & problems they solve

Hey there 👋🏼,

This is Denny here.

In last week’s issue, we dived into basics of database locks and why you need them.

Today let’s talk about Isolation, one of the ACID properties, and about a special type of lock that Postgres provides called Advisory locks.

---

ACID-compliant databases need to make sure that each transaction is carried out in isolation. That means the results of the transaction are only visible after a commit to the database happens. Other processes should not be aware of what’s going on with the records while the transaction is carried out.

What happens when a transaction tries to read a row updated by another transaction?

It depends on what isolation level the database is operating on wrt that particular transaction. Let’s explore the problems that can occur.

Problems when transaction isolation is not done

1. Dirty Read - Let’s take a situation where one transaction updates a row or a table but doesn’t commit the changes. If the database lets another transaction read those changes (before it’s committed) then it’s called a dirty read. Why? Let’s say the first transaction rolls back its changes. The other transaction which read the row/table has stale data. This happens in concurrent systems where multiple transactions are going on in parallel. But this can be prevented by the database and we will explore how later.

2. Non-repeatable read - Another side effect of concurrent execution of transactions is that consecutive reads can retrieve different results if you allow another transaction to do updates in between. So if a transaction is querying a row twice, but between the reads, there is another transaction updating the same row, the reads will give different results.

3. Phantom read - In a similar situation as above, if one transaction does two reads of the same query, but another transaction inserts or deletes new rows leading to a change in the number of rows retrieved by the first transaction in its second read, it’s called a Phantom read. This is similar to non-repeatable read. The only difference is that, while in non-repeatable read, there will be inconsistency in the values of a row, in phantom reads, the number of rows retrieved by the queries will be different.

How do databases deal with this?

They implement levels of isolation to avoid such problems.

We discussed locks in the previous issue. While locks were supposed to be used for complete isolation of transactions, if they are going to limit other transactions from doing anything while the resource is locked then lock contentions might create a problem.

The solution to this is having different levels of isolation. Let’s discuss the most common ones. These are mentioned in increasing order of isolation levels.

Levels of Isolation

1. Read uncommitted - This level of isolation lets other transactions read data that was not committed to the database by other transactions. There is no isolation happening here. So if transaction 1 performs an update and before it’s able to commit, if transaction 2 tries to access the updated data, it will see the new data. This doesn’t solve any issues mentioned above.

2. Read committed - This, as the name suggests, lets other transactions only read data that is committed to the database. While this looks like an ideal level of isolation, it only solves the dirty read problem mentioned above. If a transaction is updating a row, and another transaction tries to access it, it won’t be able to. But this can still cause non-repeatable and phantom reads because this applies only to updates and not read queries

3. Repeatable read - To counter the transactions from getting inconsistent data, we need a higher level of isolation and that is offered by repeatable read. In this, the resource is locked throughout the transaction. So if the transaction contains two select queries and in between if another transaction tries to update the same rows, it would be blocked from doing so. This isolation level is not immune to phantom reads, though it helps against non-repeatable reads. This is the default level of isolation in many databases.

4. Serializable - This is the highest level of isolation. In this, all concurrent transactions “appear” to be executed serially. Pay attention to how I said it appears to be. That’s because it’s not truly serially or sequentially executed. This level works against phantom reads as well.

Advisory Locks in Postgres

This is an interesting feature of Postgres that I came across recently. One of the applications I was working on required to have a distributed locking strategy to control concurrency issues.

The problem was to have a common datastore that contains tasks that multiple, distributed workers need to execute. If one worker picks up a task, the other one shouldn’t be able to pick it up.

To have an application-level control over the locks, where a shared resource is accessed by multiple processes/distributed workers and you need to make sure that only one worker can access it at any point in time, the best option was to have advisory locks.

Advisory locks help an application define a meaning to the lock, i.e. if you want to acquire a lock while you perform certain tasks in your application you can do that using advisory locks. In the case of row or table-level locks, the code can get ugly with SQL statements mingled with application logic.

The idea is to acquire a lock for a custom ID you generate. E.g., here, for every task, an ID is generated. In a distributed system, if one worker process has already acquired a lock on a task using its ID, the others won’t be able to. Once the business logic is executed the lock is released. Since each task has different lock IDs, the processes can acquire locks simultaneously.

You can refer to this resource for more about Advisory locks

https://shiroyasha.io/advisory-locks-and-how-to-use-them.html

---

If you like my content and want to support me to keep me going, consider buying me a coffee ☕️ ☕️ ☕️

Connect with me!

If you need help with your service architecture, you can email me 💌 : dennysam14@gmail.com