System Design: Database Transactions & ACID compliance (Part 1)

10 min readApr 19, 2020

This is my 4th blog in the system design series on which I am currently working. So in this blog, we are going to learn the in-depth details of one of the most important system design principle that is Database Transactions.

So just to create some motivation and curiosity as a Senior, Lead, Principal Developer, or Architect. I want you to consider some questions mentioned below:

Have you ever wondered why it’s generally said that NoSQL performs better than Relational databases in terms of handling very high transactions per second (TPS)?
Have you ever thought about what would happen if two people try to book the same meeting room in your organization for the same duration at the same time (Means both the people clicked the booking button at the same time. There is not even a difference of nanoseconds between the clicking event)?
Have you ever wondered why in the Banking domain, NoSQL databases aren’t recommended?
How ORMs (Object Relation Mapping) frameworks like Hibernate (Java) or Entity (DotNet) etc detect concurrency issues?
How In-Memory Databases like Redis etc handle or prevent concurrency issues or race conditions?

So there is no end to these types of how questions but yes you will be able to answer all of these questions easily after reading this blog completely. There is a little possibility this blog might look lengthy to you but it’s worth the effort trust me.

Let’s see the layout of this blog. So in this blog, we will discuss below-mentioned topics in-depth

What is a Transaction? What does ACID properties mean in the Database & Why do we need them?
Why do we need a Transaction with real-time scenarios?
What are Different types of race conditions like Dirty Reads, Dirty Writes, Non-Repeatable Reads that can occur in Databases?
What are the different types of Isolation levels in Transactional Databases with its advantages & disadvantages?
What are different algorithms to implement different Isolation levels in Databases?

What is a Transaction?

So basically in layman terms, a Transaction is a way for an application to group several reads and writes (CRUD) together into a logical unit. And with transactions, error handling becomes much simpler for an application.

So technically everything between a BEGIN TRANSACTION and a COMMIT statement is considered to be part of the same transaction.

A simple definition, isn’t it? :P Don’t worry have some patience we will surely go in-depth.

Safety guarantees: ACID

The safety guarantees provided by transactions are often described by the well-known acronym ACID, which stands for Atomicity, Consistency, Isolation, Durability.

Atomicity

Let’s first discuss Atomicity. So in layman terms, Atomicity refers to something that cannot be broken down into smaller parts.

Now, why do we need it? What’s the advantage?
So let’s create some scenario which will explain the necessity of it. As necessity is the mother of all inventions :P

Let’s say If I am a customer of a famous Bank named No-Bank which recently got bankrupted (As a guy named VJ didn’t pay back the loan amount which was in crores :P) Now I just wanted to transfer all of my money to my other account in another bank asap. No issues, I simply logged into my online banking portal and initiated a transaction for UPI transfer. Now as part of that transaction there are 2 steps. The 1st step will deduct the amount from my No-Bank account and the 2nd step will add the same amount to another bank account.

Now, what if when 1st step was completed banking server got crashed? kaboom your money will disappear within the air. Amount got deducted but didn’t get added to another bank account.

Don’t worry Atomicity will save you. Hence in the context of a transaction, Atomicity means that either all the statements inside the transaction are committed or all the statements are aborted (When a transaction fails). So there is no in-between state. It’s similar to binary either it’s a 0 or it’s 1.

So Atomicity simplifies a problem when a transaction was aborted, the application can be sure that it didn’t change anything, so it can safely be retried later if needed.

Consistency

Now let’s discuss Consistency (But don’t confuse it with eventual consistency).
So consistency refers to an idea of the database being in a “good state”. The idea of ACID consistency is that you have certain constraints about your data (invariants) that must always be satisfied.

But its the application’s responsibility to define its transactions correctly so that they preserve consistency. Some specific kinds of invariants can be checked by the database, for example using foreign key constraints or uniqueness constraints.

Isolation

The classic books on the database (DBMS) formalize isolation as serializability which is half-knowledge. Even when I was pursuing my Bachelor’s degree I was also under this blunder. However, in practice, serializable isolation is rarely used, because of its performance issues.

So in actual Isolation means that concurrently executing transactions are isolated from each other and they cannot see on each other’s operations.

There is a total of 4 levels of Isolation in general for any Database and serializable is one of the levels of Isolation between Transactions.

Some popular databases, such as Oracle 11g, don’t even actually implement serializable isolation. In Oracle there is an isolation level called “serializable” but it implements something called snapshot isolation, which is a weaker guarantee than serializability. So only just by name, it’s serializable but not by the implementation. But maybe in the newer version, Oracle might be following the general convention.

Durability

Durable just like Nokia 1100 attached to Thor’s hammer :P

Durability is a guarantee that once a transaction has been committed successfully, then any data it has written will not be lost, even if there is a hardware failure or the database crashes.

In simple terms, there is a continuous backup of data for disaster recovery.

By the way, perfect durability does not exist. if all of your hard disks and all your backups are destroyed at the same time, there’s nothing your database can do to save you.

Systems that do not meet the ACID criteria are sometimes called the BASE, which stands for Basically Available, Soft state, & Eventual consistent.
Most of NoSql today are actually BASE. But to compete with Relational databases in this competitive world NoSql vendors are saying that they are 100% ACID compliant.

Single-Object & Multi-Object Operations

Here objects can be a table, documents, records. Initially many NoSql databases only provided ACID-compliant transaction feature only on a single object (means for a single table only).

For example, even the famous MongoDB started providing multi-document ACID transactions only by 2018 in its 4.0 release which means initially it only provided transaction capability only on a single document at a time. You can read more about it here.

Moreover, In a relational data model, a row in one table often has a foreign key reference to a row in another table. Multi-object transactions allow you to ensure that these references remain valid.

ISOLATION LEVELS

Now let’s discuss one of the most important topics in this blog which is Isolation levels. As I mentioned earlier there are total 4 Isolation levels

Read Uncommitted (Weakest Isolation Level)
Read Committed
Repeatable Read or Snapshot Isolation (Default level for many DB)
Serializable (Strongest Isolation Level)

I am not going to discuss anything about Read Uncommitted because it’s not used anywhere.

Now again the same question why do we need Isolation?

So If two transactions don’t touch the same data, they can safely be run in parallel, because neither depends on the other. But Concurrency issues (race conditions) only come into play when one transaction reads data that is concurrently modified by another transaction, or when two transactions try to simultaneously modify the same data.

Concurrency bugs are hard to find by testing because such bugs are only triggered when you get unlucky with the timing. Such timing issues might occur very rarely and are usually difficult to reproduce. Concurrency bugs are generally caused by weak isolation levels of transactions.

Read Committed

Now let’s start with Read Committed isolation. The most basic level of transaction isolation is read committed.

It provides two guarantees:

When reading from the database, you will only see data that has been committed (no dirty reads).
When writing to the database, you will only overwrite data that has been committed (no dirty writes).

So in short if two transactions named T1 & T2 are getting executed, then Transaction T1 can see or read the changes (writes/updates) made by transaction T2 if T2 has been committed before T1.

So what’s a Dirty Read?

A dirty read occurs when a transaction is allowed to read data from a row that has been modified by another running transaction and not yet committed.

And what’s a Dirty Write?

A dirty write occurs when a transaction is allowed to overwrite data from a row that has been modified by another running transaction and not yet committed.

Implementing Read Committed

Read committed was a very popular isolation level. In earlier versions of some databases like Oracle 11g, SQL Server 2012, MemSQL was the default setting. You can check it here for Oracle 11g & here for MemSQL.

So databases prevent dirty writes & dirty reads by using row-level locks. When a transaction wants to modify a particular row or document, it must first acquire a lock on that object (row or document). It must then hold that lock until the transaction is committed or aborted.
Only one transaction can hold the lock for any given row or document.

However, the approach of requiring read locks does not work well in practice, because one long-running write transaction can force many read-only transactions to wait until the long-running transaction has completed.

This harms the response time of read-only transactions as a slowdown in one part of an application can have a knock-on effect in a completely different part of the application, due to waiting for locks.

Issues in Read Committed

Read Committed Isolation suffers from the problem of Non-Repeatable read or Read skew.

Now let’s understand the issue with a real-time scenario.

Let’s say there is a customer named Alice who has a total of $1000 bucks in her two accounts at a bank and the amount is split across those two accounts with $500 each.
Now Alice initiated a Transfer-Transaction $100 from one account to the other. Now this transaction consists of two statements first is to add $100 to Account 1 and the second is to deduct $100 from Account 2.
But before initiating a transfer transaction. Alice also initiated a Read-Transaction (just to verify the balances as we all do :P) which will first read the balance from Account 1 and then read the balance from Account 2.
As we can see in the above image, Read-Transaction was initiated before the Transfer-Transaction, and also Read-Transaction was committed after Transfer-Transaction.
Now according to the rule of Read Committed Isolation as Transfer-Transaction was committed before Read-Transaction. Hence Read-Transaction can see changes done by Transfer-Transaction.
So she may see that balance of Account 1 before the incoming payment has arrived (with a balance of $500), and see the balance of another account after the outgoing transfer has been made (the new balance is $400).
So to Alice, it now appears as she only has a total of $900 in her accounts ($500 in 1st read of Account 1 & $400 in 2nd read of Account 2). It seems that $100 has vanished into thin air.
This anomaly is called Non-Repeatable Read or Read Skew. But, if Alice were to read the balance of account 1 again at the end of the transaction, she would see a different value ($600) than she saw in her previous query.

Now as I also have to maintain the length of this blog. So, for now, that’s pretty much about Database Transactions in the scope of this blog.

Yes, I know you people might be wondering where are the other topics/questions that I promised to discuss. So don’t panic I have written another blog on the remaining topics in continuation of this blog here.

And it contains topics like What are Repeatable Read and Serializable Isolation with the implementation and Issues of both, What are Optimistic and Pessimistic concurrency control. Last but not least I will also discuss the Two-phase commit (2PL) mechanism in that blog.

Moreover, if you have any doubts you can connect with me on Linkedin, Github, Twitter, Facebook. And if you liked this article then please share and follow.