Database Transactions - ACID Properties

Introduction

Yesterday, I went to a restaurant with my friends. We ordered some food, finished eating, and asked the waiter for the bill.

While the waiter was preparing the bill, I started thinking about how the billing system works behind the scenes.

When we place an order, the system has to do multiple things, like saving the order details, calculating the bill, and updating the payment status.

But what if something goes wrong in the middle? What if the system crashes before completing all these operations?

This is where database transactions come into the picture.

Recently, I was learning about database transactions, ACID properties, and isolation levels. In this article, I'll share what I understood, along with some simple examples.

What is a Database Transaction?

A database transaction is a group of one or more database operations that are treated as a single unit of work.

Let's take our restaurant example.

When a customer places an order, the system might need to perform these operations:

1. Create an order.
2. Save the ordered items.
3. Update the available stock.
4. Calculate and save the total bill.

All these operations are related. If one of them fails, we don't want the database to be left with incomplete information.

So, instead of executing them as separate independent operations, we can group them into a single transaction.

A transaction generally involves three important commands:

• BEGIN — Starts a transaction.
• COMMIT — Saves the changes made by the transaction.
• ROLLBACK — Undoes the uncommitted changes if something goes wrong.

For example:

BEGIN;

INSERT INTO orders (order_id, table_id, total_amount)
VALUES (101, 5, 500);

INSERT INTO order_items (order_id, item_id, quantity)
VALUES (101, 10, 2);

UPDATE inventory
SET quantity = quantity - 2
WHERE item_id = 10;

COMMIT;

Here, all three database operations are part of one transaction.

If everything succeeds, we commit the transaction.

If an operation fails, we can roll back the transaction so that the database doesn't keep only part of the changes.

But what happens if the database itself crashes while executing a transaction?

A database can crash for several reasons, such as power failures, hardware problems, operating system failures, or software bugs.

Even in these situations, the database needs a way to recover without leaving incomplete transactions behind.

To handle these kinds of problems and maintain reliable data, database transactions follow four important properties, commonly called ACID properties.

ACID Properties

ACID stands for:

• A — Atomicity
• C — Consistency
• I — Isolation
• D — Durability

Each property solves a different problem.

Atomicity ensures that either all operations in a transaction happen or none of them happen.

Consistency ensures that a transaction preserves the rules and constraints defined in the database.

Isolation controls how multiple transactions running at the same time interact with each other.

Durability ensures that once a transaction is committed, its changes survive a crash, under the database's durability guarantees.

Let's understand each property with examples.

Atomicity

Atomicity means that either all the operations in a transaction should succeed, or none of them should happen.

Let's take our restaurant example.

Suppose a customer orders 2 pizzas, and each pizza costs ₹200.

When the waiter confirms the order, the system needs to perform two operations:

1. Create an order with a total amount of ₹400.
2. Reduce the available pizza stock by 2.

Now, let's say the order is created successfully, but the system crashes before updating the stock.

In this case, the order exists in the database, but the stock has not been updated. This leaves the data in an incorrect state.

To avoid this, we can execute both operations inside a transaction.

BEGIN;

INSERT INTO orders (order_id, amount)
VALUES (101, 400);

UPDATE inventory
SET quantity = quantity - 2
WHERE item_id = 1;

COMMIT;

If both operations succeed, the transaction is committed.

But if something goes wrong before the transaction is committed, the database rolls back the changes.

So, even if the order was inserted successfully, it won't remain in the database if the transaction fails.

This is what atomicity guarantees: All or nothing.

How does the database maintain atomicity?

Databases use transaction logs and recovery mechanisms to handle failures.

For example, PostgreSQL uses Write-Ahead Logging (WAL).

Before modified data pages are written to their final location on disk, PostgreSQL records the changes in WAL.

If the database crashes, it uses WAL and transaction status information during recovery to ensure that committed transactions are recovered and incomplete transactions do not become visible as committed changes.

We'll understand WAL in more detail when we discuss durability.

Consistency

Consistency means that a transaction should maintain the rules and constraints defined in the database.

While learning about consistency, I came across two different concepts: Data Consistency and Read Consistency.

1. Data Consistency (Correctness)

Data consistency is about keeping the data correct and making sure it follows the rules defined in the database.

Let's say we have three tables.

Blogs Table

blog_id	likes
1	5
2	1

Users Table

user_id	name
1	John
2	Edmon

Pictures Table

user_id	picture_id
1	1
1	2
2	3
2	4

Here, user_id in the Pictures table is a foreign key referencing the Users table.

Now suppose we try to insert a picture with user_id = 5, but there is no user with that ID in the Users table.

INSERT INTO pictures (user_id, picture_id)
VALUES (5, 5);

The database rejects this operation because it violates the foreign key constraint.

This is called referential integrity.

Similarly, databases maintain data consistency using constraints such as primary keys, foreign keys, UNIQUE, NOT NULL, and CHECK.

Apart from these constraints, we may also have business rules that need to be maintained by our application or database.

So, the main idea of ACID Consistency is that a transaction should move the database from one valid state to another valid state without breaking the defined rules.

In simple terms, it focuses on correctness of data.

2. Read Consistency (Availability and Freshness)

Now let's look at consistency from another perspective.

In real applications, we may have multiple copies of the same database, called replicas.

Why do we need replicas?

One reason is availability. If one database server goes down, another server may still be able to serve requests. Replicas can also help distribute read traffic.

Let's say we have one primary database and two replicas.

Initially, all three databases contain the same value.

• Primary Database → 90
• Replica 1 → 90
• Replica 2 → 90

Now suppose a transaction updates the value from 90 to 100 in the primary database and commits.

But replication may take some time.

At that moment, the data might look like this:

• Primary Database → 100
• Replica 1 → 90
• Replica 2 → 90

Now imagine two users trying to read that value.

T1:

User 1 reads from the primary database and gets 100.

T2:

User 2 reads from Replica 1 and gets 90.

Both users are reading the same record, but they are getting different values.

This happens because the replicas haven't received the latest update yet.

After some time, replication catches up.

• Primary Database → 100
• Replica 1 → 100
• Replica 2 → 100

Now all three databases have the same value.

This is an example of Eventual Consistency.

It means different replicas may temporarily have different values, but eventually they become consistent if updates stop and replication completes successfully.

Availability vs. Consistency

Here, we have an interesting trade-off.

Suppose a replica hasn't received the latest update yet.

We have two possible approaches.

Option 1: Allow the read

We can allow the user to read from that replica, even though it has older data.

The user gets a response immediately, but the value might be stale.

This favors availability, although it doesn't guarantee that the user gets the latest data.

Option 2: Wait for the latest data

We can wait until the replica catches up, or route the request to a database that has the required version.

The user gets fresher data, but the request may take longer or become unavailable if the required data cannot be reached.

So, depending on the system's requirements, we may need to make trade-offs between availability and the consistency of reads, especially during network failures.

What's the Difference?

The main thing I understood is that these two concepts focus on different problems.

ACID Consistency → Correctness

It makes sure the database follows its defined rules and constraints. A transaction should not leave the data in an invalid state.

Read Consistency → Freshness of Data

It defines what values a reader is allowed to see, especially when data is replicated across multiple servers.

Availability is related to read consistency because allowing reads from replicas with slightly older data can help a system continue serving requests.

So, ACID consistency is mainly about keeping data correct, while consistency in distributed reads is about how up-to-date and consistent the data needs to be across different replicas.

Isolation

Until now, we have seen how transactions maintain atomicity and consistency.

But what happens when multiple transactions run at the same time?

Let's take our restaurant example.

Suppose two waiters are taking orders from different tables. Both are using the same billing system.

Now imagine only one pizza is left in stock, and both waiters are trying to place an order for it.

If both transactions read the available quantity at the same time, they might both think the pizza is available.

This is where Isolation comes in.

Isolation controls how multiple transactions running at the same time interact with each other.

Ideally, we want the result of concurrent transactions to be the same as some valid order of executing them one after another. But running every transaction one after another can affect performance.

So databases provide different isolation levels.

Before understanding them, let's look at four common problems that happen when transactions execute concurrently.

• Dirty Read
• Non-Repeatable Read
• Phantom Read
• Lost Update

1. Dirty Read

A dirty read happens when a transaction reads a change made by another transaction that hasn't committed yet.

Let's take a sales table.

pid	qnt	price
P1	10	5
P2	20	4

Suppose two transactions are running.

Time	TX1	TX2
T1	BEGIN;
T2	SELECT pid, qnt * price FROM sales; → P1: 50, P2: 80
T3		BEGIN;
T4		UPDATE sales SET qnt = qnt + 5 WHERE pid = 'P1';
T5	SELECT SUM(qnt * price) FROM sales; → 155
T6		ROLLBACK;
T7	COMMIT;

At T5, TX1 reads the updated value from TX2 even though TX2 hasn't committed yet.

Later, TX2 rolls back its changes, but TX1 has already used that uncommitted data.

This is called a Dirty Read.

PostgreSQL note: This example represents a database that allows dirty reads. PostgreSQL doesn't allow them, even with Read Uncommitted.

2. Non-Repeatable Read

A non-repeatable read happens when a transaction reads the same record twice and gets different values because another transaction committed an update in between.

Let's use the same Sales table.

Time	TX1	TX2
T1	BEGIN;
T2	SELECT pid, qnt * price FROM sales; → P1: 50, P2: 80
T3		BEGIN;
T4		UPDATE sales SET qnt = qnt + 5 WHERE pid = 'P1';
T5		COMMIT;
T6	SELECT SUM(qnt * price) FROM sales; → 155
T7	COMMIT;

Here, TX1 initially reads the sales values.

Meanwhile, TX2 updates the quantity and commits.

When TX1 later calculates the total, it sees the updated data.

So TX1 gets different results within the same transaction.

This demonstrates the effect of a non-repeatable read. Strictly speaking, the standard definition involves reading the same row twice; here, the second statement is an aggregation over the changed rows.

This can happen under PostgreSQL's default Read Committed isolation level.

3. Phantom Read

A phantom read happens when a transaction executes a query and later gets a different set of matching rows because another transaction inserts, deletes, or changes matching records.

Suppose we have this table:

id	ch
1	a
2	a
3	b
4	b

Time	TX1	TX2
T1	BEGIN;
T2	SELECT * FROM test WHERE ch = 'b'; → 2 rows
T3		BEGIN;
T4		INSERT INTO test (id, ch) VALUES (5, 'b');
T5		COMMIT;
T6	SELECT * FROM test WHERE ch = 'b'; → 3 rows
T7	COMMIT;

Initially, TX1 gets two records.

Meanwhile, TX2 inserts another record with ch = 'b' and commits.

When TX1 executes the same query again, it gets three records.

The newly appearing record is called a phantom row.

PostgreSQL's Repeatable Read prevents phantom reads because the transaction continues using the same snapshot.

4. Lost Update

A lost update happens when two transactions read the same value and update it independently, causing one transaction's change to be overwritten.

Suppose P1's current quantity is 10.

TX1 wants to increase it by 10, and TX2 wants to increase it by 5.

The expected final quantity is 25.

Time	TX1	TX2
T1	BEGIN;
T2	SELECT qnt FROM sales WHERE pid = 'P1'; → 10
T3		BEGIN;
T4		SELECT qnt FROM sales WHERE pid = 'P1'; → 10
T5	UPDATE sales SET qnt = 20 WHERE pid = 'P1';
T6		UPDATE sales SET qnt = 15 WHERE pid = 'P1'; → Waits for TX1
T7	COMMIT;
T8		TX2's UPDATE completes
T9		COMMIT;

The final quantity becomes 15 instead of 25.

TX2 overwrites the value written by TX1, so TX1's update is lost.

This is called a Lost Update.

This can happen when an application calculates a new value using an earlier read and then writes that fixed value back.

One way to prevent it is by using SELECT FOR UPDATE.

BEGIN;

SELECT qnt
FROM sales
WHERE pid = 'P1'
FOR UPDATE;

UPDATE sales
SET qnt = qnt + 10
WHERE pid = 'P1';

COMMIT;

Here, PostgreSQL locks the selected row. Another transaction trying to update the same row must wait.

If we simply want to increment a value, we can also do it directly:

UPDATE sales
SET qnt = qnt + 5
WHERE pid = 'P1';

This avoids calculating the new value using an outdated application-side read.

---

Database Isolation Levels

Now that we have seen these problems, let's understand how databases prevent them.

SQL defines four common isolation levels:

1. Read Uncommitted
2. Read Committed
3. Repeatable Read
4. Serializable

1. Read Uncommitted

This is the weakest standard isolation level.

A transaction may read changes made by another transaction before they are committed.

So dirty reads, non-repeatable reads, and phantom reads are possible.

For example, in our Dirty Read scenario, TX1 could read the updated sales total even though TX2 later rolls back.

We can request this isolation level using:

BEGIN TRANSACTION
ISOLATION LEVEL READ UNCOMMITTED;

However, PostgreSQL treats Read Uncommitted as Read Committed.

So we cannot reproduce dirty reads in PostgreSQL.

2. Read Committed

Read Committed prevents dirty reads.

A transaction only reads committed data.

However, each statement gets a new snapshot, so the same query may return different results when executed twice.

Let's take our sales example.

Time	TX1	TX2
T1	BEGIN ISOLATION LEVEL READ COMMITTED;
T2	SELECT pid, qnt * price FROM sales; → P1: 50, P2: 80
T3		BEGIN;
T4		UPDATE sales SET qnt = qnt + 5 WHERE pid = 'P1';
T5		COMMIT;
T6	SELECT SUM(qnt * price) FROM sales; → 155
T7	COMMIT;

Even though TX1 hasn't finished, its next query sees the changes committed by TX2.

This is allowed because Read Committed creates a new snapshot for each statement.

Read Committed is PostgreSQL's default isolation level.

3. Repeatable Read

In Repeatable Read, a transaction continues reading from the same snapshot.

Let's use the same example.

Time	TX1	TX2
T1	BEGIN ISOLATION LEVEL REPEATABLE READ;
T2	SELECT pid, qnt * price FROM sales; → P1: 50, P2: 80
T3		BEGIN;
T4		UPDATE sales SET qnt = qnt + 5 WHERE pid = 'P1';
T5		COMMIT;
T6	SELECT SUM(qnt * price) FROM sales; → 130
T7	COMMIT;

Notice the difference.

Even though TX2 committed its update, TX1 still calculates the total using its original snapshot.

This happens because PostgreSQL uses a consistent snapshot established when the transaction first executes a statement that needs one.

So changes committed by other transactions after that snapshot aren't visible to TX1.

One interesting thing about PostgreSQL is that Repeatable Read also prevents phantom reads.

This is stronger than the minimum Repeatable Read guarantees described by the SQL standard.

However, Repeatable Read doesn't prevent every possible concurrency anomaly.

For example, write skew can still happen.

4. Serializable

Serializable is the strongest standard isolation level.

It ensures that concurrent transactions produce a result equivalent to executing those transactions one after another in some order.

This doesn't mean PostgreSQL actually runs every transaction sequentially.

Transactions can still run concurrently.

But PostgreSQL checks for dangerous dependencies that could produce a result that isn't equivalent to any serial execution.

PostgreSQL uses Serializable Snapshot Isolation (SSI).

Let's take an example.

Suppose we have two records.

Record	Value
A	100
B	100

Our business rule is that at least one record must always have a value of 100.

Two transactions are running at Serializable isolation.

Time	TX1	TX2
T1	BEGIN ISOLATION LEVEL SERIALIZABLE;
T2	SELECT value FROM test WHERE id IN ('A', 'B'); → 100, 100
T3		BEGIN ISOLATION LEVEL SERIALIZABLE;
T4		SELECT value FROM test WHERE id IN ('A', 'B'); → 100, 100
T5	UPDATE test SET value = 0 WHERE id = 'A';
T6		UPDATE test SET value = 0 WHERE id = 'B';
T7	COMMIT;
T8		COMMIT; → Serialization failure

Both transactions initially read A = 100 and B = 100.

TX1 changes A to 0.

TX2 changes B to 0.

If both transactions committed successfully, we would have:

A = 0
B = 0

This would violate our business rule.

This kind of anomaly is called Write Skew.

Under PostgreSQL Repeatable Read, both transactions may successfully commit because they update different rows.

But PostgreSQL Serializable can detect the dangerous dependency and abort one transaction.

We may see an error like:

ERROR: could not serialize access due to
read/write dependencies among transactions

When this happens, the application needs to retry the entire transaction.

Serializable is useful when we need stronger correctness guarantees, but we should also be prepared to handle transaction retries.

---

Pessimistic and Optimistic Concurrency Control

While learning about isolation, I also came across two ways of handling concurrent updates.

Pessimistic Concurrency Control

Here, we lock the data before changing it.

For example:

BEGIN;

SELECT qnt
FROM sales
WHERE pid = 'P1'
FOR UPDATE;

UPDATE sales
SET qnt = qnt + 10
WHERE pid = 'P1';

COMMIT;

The FOR UPDATE statement locks the selected row.

Another transaction trying to acquire a conflicting lock must wait until the first transaction releases it.

This is useful when conflicts are likely.

Optimistic Concurrency Control

In optimistic concurrency control, we allow transactions to proceed without locking the record exclusively in advance.

Before applying the update, we check whether another transaction has changed the data.

If a conflicting change is detected, the operation may fail and need to be retried.

This approach can be useful when conflicts are less frequent.

PostgreSQL's Serializable Snapshot Isolation also uses conflict detection, although its implementation is different from application-level version checking.

---

Comparison of Isolation Levels

Isolation Level	Dirty Read	Non-Repeatable Read	Phantom Read
Read Uncommitted	Possible	Possible	Possible
Read Committed	Prevented	Possible	Possible
Repeatable Read	Prevented	Prevented	Possible under SQL standard
Serializable	Prevented	Prevented	Prevented

This table describes the general SQL-standard guarantees.

PostgreSQL has two important differences:

• Read Uncommitted behaves like Read Committed, so dirty reads are prevented.
• Repeatable Read also prevents phantom reads.

Lost updates need separate consideration because their behavior depends on the type of update and the database's concurrency-control mechanisms.

What I Understood About Isolation

Isolation is not simply about making transactions run one after another.

It is about controlling how concurrent transactions interact.

Different isolation levels provide different guarantees, and the right choice depends on what our application needs.

For many applications, Read Committed is sufficient.

But when multiple queries need to see a consistent snapshot, Repeatable Read may be useful.

And when concurrent transactions must behave like some serial execution, Serializable provides the strongest guarantee.

The important thing is to understand what problems each isolation level prevents and choose accordingly.

Durability

Durability means that once a transaction is committed, its changes should not be lost, even if the database crashes or restarts.

Let's take our restaurant example.

Suppose a customer finishes eating, pays the bill, and the waiter confirms the payment in the billing system.

The database successfully commits the transaction.

But immediately after that, the database server crashes.

When the server restarts, the payment information should still be there.

This is what Durability guarantees.

How do databases maintain durability?

Databases use different techniques to make sure committed changes survive failures.

Some common techniques are:

1. Write-Ahead Logging (WAL)
2. OS Page Cache and fsync
3. Checkpoints
4. Asynchronous Snapshots
5. Append-Only Files (AOF)

1. Write-Ahead Logging (WAL)

Writing every change directly to the database files can be expensive.

For example, when we update a record, the database may need to modify multiple data pages and indexes.

Instead of writing all these changes to their final locations immediately, databases like PostgreSQL use Write-Ahead Logging (WAL).

The idea is simple.

Before modified data pages are written to disk, the database first records the changes in a log.

Let's see how PostgreSQL handles an UPDATE query.

BEGIN;

UPDATE orders
SET payment_status = 'Paid'
WHERE order_id = 101;

COMMIT;

When PostgreSQL executes this query, it modifies the data page in shared buffers and creates the required WAL records in WAL buffers.

Both are initially in RAM.

Under normal synchronous commit settings, PostgreSQL persists the required WAL before confirming the transaction.

The modified data pages can be written to disk later.

Here is how the process works.

Now suppose the database crashes after COMMIT, before the modified data pages are written to disk.

When PostgreSQL restarts, it can use the persisted WAL records to recover the committed changes.

This is why WAL plays an important role in durability.

2. OS Page Cache and fsync

One interesting thing I learned is that writing data to a file doesn't always mean it has reached the physical disk.

Usually, when a program writes to a file, the operating system may first keep that data in its page cache, which is part of RAM.

Why?

Because writing to RAM is much faster than writing directly to disk. The operating system can write the cached data to disk later.

But how is this related to database durability?

Let's take our PostgreSQL example.

When PostgreSQL executes an UPDATE query, it modifies the data in shared buffers and generates WAL records in WAL buffers.

Both are initially in RAM.

When PostgreSQL writes these WAL records to its WAL file, they may first go through the operating system's page cache.

So even though PostgreSQL has written the WAL to a file, the data might still be sitting in RAM.

The flow looks like this:

Now suppose the WAL has been written to the OS page cache, but it hasn't reached the disk yet.

If the machine suddenly loses power, the cached WAL data may be lost.

And if the modified data page was also only in RAM, PostgreSQL might lose that change.

This is where fsync() comes in.

fsync() is an operating system call that requests the modified data of a file to be synchronized to stable storage.

In simple terms, it helps make sure the data isn't just sitting in the OS page cache.

Under normal durability settings, PostgreSQL uses fsync() or other appropriate synchronization methods to persist the required WAL before confirming a synchronous commit.

So even if the database crashes immediately after COMMIT, PostgreSQL can use the persisted WAL to recover the changes.

The important difference is:

WAL records the changes needed for recovery, while fsync or a similar synchronization mechanism helps make those WAL records durable.

There is also a performance cost. Synchronizing data to storage takes time, so frequent synchronization can increase write latency.

PostgreSQL provides settings such as fsync and synchronous_commit to control this behavior.

3. Checkpoints

We have seen that PostgreSQL can commit a transaction before writing all the modified data pages to their final locations.

But those modified pages cannot remain only in memory forever.

They eventually need to be written to disk.

This is where Checkpoints come in.

A checkpoint is a process where PostgreSQL ensures that dirty data pages (modified in memory but not yet on disk) are written to storage and records a point from which crash recovery can proceed.

For example, suppose the database has been running for several hours and thousands of transactions have modified data.

If the database crashes, PostgreSQL needs to replay WAL records to recover changes that weren't already reflected in the database files.

Without checkpoints, the amount of WAL that needs to be processed during recovery could become very large.

By performing checkpoints periodically, PostgreSQL can reduce the amount of recovery work required.

So checkpoints mainly help with writing modified data pages to disk and controlling crash recovery time.

4. Asynchronous Snapshots

Some databases use snapshots as a persistence mechanism.

Instead of saving every change immediately, they periodically save a snapshot of their data.

For example, Redis supports RDB snapshots.

Suppose a database takes a snapshot at 10:00 AM.

After that, some new records are created at 10:05 AM.

Now imagine the server crashes at 10:06 AM, before the next snapshot is taken.

If snapshots are the only persistence mechanism, those recent changes may be lost.

So the durability guarantee depends on how frequently snapshots are taken and whether other persistence methods are enabled.

5. Append-Only Files (AOF)

Another persistence technique is an Append-Only File (AOF).

Redis supports this mechanism.

Instead of saving the entire dataset after every update, Redis records write operations in an append-only log.

When the server restarts, Redis can use the persisted AOF to rebuild its data.

However, durability depends on how frequently the AOF is synchronized to disk.

For example, Redis can be configured to synchronize after every write or approximately once per second.

So AOF provides another way to recover data, but recent changes may still be lost depending on the persistence settings.

Conclusion

When I started learning about database transactions, I thought it was mostly about BEGIN, COMMIT, and ROLLBACK.

But as I went deeper, I came across some interesting things, especially what happens when two transactions run at the same time and how PostgreSQL handles failures.

There is still a lot more to learn about how databases work internally, but understanding these concepts helped me see what actually happens behind a simple database query.