Transactions

So we have covered all the things, from the side of how modern database use multiple techniques to ensure faster read/writes of data. And now we will move towards one of the most important aspect of any reliable database system, which is transaction.

Transactions ensure that a series of database operations happen safely, consistently, and predictably, even in the face of failures, crashes, or concurrent access. In short, while indexing makes our queries fast and storage strategies keep your data organised, compression keep the size of data relatively small, transactions make sure our data stays correct.

A transaction is a fundamental concept in database management system that ensures a series of operations on the database are executed as a single, indivisible unit. This means that either all operations within the transaction are successfully executed, or none are, maintaining the integrity and consistency of the database. Transactions ensure that the database remains consistent even in the event of system failures, power loss, or errors. For example,

BEGIN;  -- Start of Transaction
UPDATE accounts SET balance = balance - 500 WHERE account_id = 'A';
UPDATE accounts SET balance = balance + 500 WHERE account_id = 'B';
COMMIT;  -- Commit the transaction

If any statement fails (like insufficient balance), a ROLLBACK; will occur to revert all operations.

A transaction is a sequence of operations performed as a single logical unit of work in a database and ACID properties (discussed in next blog) are the foundational principles that guarantee the reliability and integrity of these transactions.

Theoretically, a database can be a transactional without ACID. As, transactional database means it supports transactions. However, if it does not follow ACID, it cannot guarantee the integrity, consistency, and durability of data.

Nested transactions allow a transaction to contain other transactions, enabling modularity and finer control over complex operations. If a sub-transaction fails, only that part can be rolled back without affecting the parent transaction. However, if the parent transaction fails, all its sub-transactions are also rolled back. This hierarchical approach enhances error recovery and consistency in complex operations.

In later blogs, we will learn about distributed databases, but till then when we talk about a database system we are referring to a single database server/system. But i don't want to wait for you to tell you that, there also exists distributed transactions, in which transaction span multiple networked databases or systems. And these very complex and tough to handle. These multiple databases need to coordinate, using protocols like the Two-Phase Commit Protocol (2PC) to ensure all participating systems agree on the transaction's outcome, maintaining atomicity and consistency across distributed environments. For now know this is enough!

Transaction in itself requires transaction management, which is the process of ensuring that each transaction are executed in a way that maintains the integrity, consistency, and reliability of the data. Also at any point of time there could be multiple transaction going on (maybe with the same data), and for that purpose we also need concurrency control, which is the mechanism used to ensure that multiple transactions can execute concurrently without violating the consistency and isolation of the database.

So Transaction Management (for each and every transaction) and Concurrency Control (for transactions happening concurrently) both work together to ensure concurrent transactions are executed properly ensuring integrity, reliability.

We are growing deeper into understanding of DBMS, so lets revise what we had learned earlier, DBMS maintains buffer pool, and recently used data is stored buffer pool, and all operations are logged in WAL records. Now why i'm telling you this!? The reason is, a transaction is marked committed immediately after its WAL records are safely persisted and not necessarily after its data pages are written to disk.

If we don't do Transaction Management and Concurrency Control, it could lead to Read Inconsistencies.

Read Inconsistency

1. Dirty Reads, occurs when a transaction reads data that has been modified by another transaction but not yet committed. If this second transaction is later rolled back, the data read by the first transaction becomes invalid. For example, if transaction T1 starts and updates a data item (such as, a bank balance) but does not commit, meanwhile, transaction T2 reads this uncommitted data. And in case, if T1 rolls back, T2 has already used incorrect data, leading to data in-consistency.

BEGIN TRANSACTION; -- Transaction T1
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1; -- Not committed yet

SELECT balance FROM accounts WHERE account_id = 1; -- Reads the uncommitted balance

If transaction T1 rolls back, transaction T2 has already read incorrect data.

2. Non-repeatable Reads, occurs when a transaction reads the same data multiple times but gets different results because another concurrent transaction has modified or deleted that data and committed the change in between the reads. For example, if transaction T1 reads a data item (such as, a bank balance), meanwhile, transaction T2 updates or delete the same data and commits. And if T2 reads the same data again, it will now gets a different result.

BEGIN TRANSACTION; -- Transaction T1
SELECT balance FROM accounts WHERE account_id = 1; -- Reads balance = 1000
-- Some processing...

BEGIN TRANSACTION;  -- Transaction T2
UPDATE accounts SET balance = balance - 500 WHERE account_id = 1; COMMIT;

Now if transaction T1, continues to read the same data again,

SELECT balance FROM accounts WHERE account_id = 1; -- Now balance = 500

The same SELECT query gave different results within the same transaction.

3. Phantom Reads, occurs when a transaction reads a set of rows matching a condition, but another transaction inserts, updates, or deletes rows that affect the result set. When the first transaction re-executes the same query, it sees additional (or missing) rows—these are called phantoms.

BEGIN TRANSACTION; -- Transaction T1
SELECT * FROM orders WHERE price > 100; -- Returns 5 rows

BEGIN TRANSACTION; -- Transaction T2
INSERT INTO orders (id, price) VALUES (6, 150); COMMIT;

Now if transaction T1 again read the data with the same matching condition, it will get the different result.

SELECT * FROM orders WHERE price > 100; -- Returns 6 rows now (phantom row)

A phantom row (new order with price > 100) appeared during the transaction.

4. Lost Updates, occurs when two or more concurrent transactions read the same data, make changes, and write back the updates. The final value overwrites the earlier update, causing the first update to be lost. For example, transaction T1 reads a data value, transaction T2 reads the same data value. Both transactions modify the data independently, whichever transaction commits last will overwrite the first transaction's update, causing the first update to be lost. The final data does not reflect all the changes.

5. Write Skew, is a type of anomaly that occurs in concurrent transactions when two transactions read overlapping data and make decisions based on that data, but then both update different parts of the database in a way that violates consistency constraints. It occurs when both transactions read data, make decisions assuming that the other transaction hasn’t yet modified the data, and then write conflicting updates.