Transactions and ACID Properties in Databases (Part 1)

In the realm of database management, data integrity and consistency are paramount. Database transactions and ACID properties help us achieve this. Let's first understand what a transaction is, and then we will explore ACID properties.

What is a Transaction?

A transaction is a sequence of operations or set of queries, which are treated as a single statement or unit of work. Suppose Jack has to send $100 to his friend Emmy, in this case a transaction involves :-
1. Checking if Jack has a minimum balance of $100.
2. Debiting $100 from Jack's account.
3. Crediting $100 to Emmy's account.

Let's create a sample database and table with sample data for our example.

CREATE DATABASE IF NOT EXISTS bank;
USE BANK;
CREATE TABLE IF NOT EXISTS accounts (
account_id INT PRIMARY KEY,
account_name  VARCHAR(20) NOT NULL,
balance DECIMAL(10,2) NOT NULL
);
INSERT INTO accounts (account_id, account_name, balance)
VALUES 
    (1, 'Jack', 500.00),
    (2, 'Emmy', 200.00),
    (3, 'Alice', 300.00),
    (4, 'Bob', 150.00),
    (5, 'Charlie', 400.00);

Here is the sample table :-

-- starting the transaction so below this the set of sql
-- statements will be consider as a single unit of work
START TRANSACTION;

-- 1. Select the the row of Jack from table and checks if he has minimum $100
IF(SELECT balance FROM accounts WHERE account_name = "Jack") >= 100

BEGIN 
-- 2. Debit $100 from Jack's account   
UPDATE accounts
SET balance = balance - 100
WHERE account_name = "Jack"

-- Credit $100 to Emmy's account
UPDATE accounts
SET balance = balance + 100
WHERE account_name = "Emmy"

COMMIT
END

ELSE
BEGIN

-- If jack doesn't have $100 then rollback the entire transaction 
-- which means database will back to it's prevoius state before the 
-- transaction begins

ROLLBACK;
END

Now that you have a basic understanding of what a transaction is and how to create one, let's shift our focus to an important aspect: the ACID properties of transactions. A transaction should adhere to ACID properties, which stand for Atomicity, Consistency, Isolation, and Durability.

Durability :-

If a transaction is committed, the data should persist in the database, specifically on the hard disk. Even if a power failure occurs after committing, the data should be retained upon restart. However, if a power failure occurs before the commit phase, i.e., during the transaction, the database should roll back to the previous state before the transaction started. Additionally, if any issue arises, we can also roll back to the previous state.

Note: Before the commit phase, all update queries are held in memory and have not yet been written to the database. That's why we can rollback the transaction.

Atomicity :-

A transaction should be atomic, that is it can be divided into other units. It should be inbreakable just like a atom. Also to an external user the transaction should appear as a single statement. Either all statements inside the transaction should succeed or none.

Consistency :-

The state of database before a transaction start and after a transaction stops should be in correct form. Let's run both statements concurrenty.

-- Session 1                                    -- Session 2
-- =========                                    -- =========
BEGIN TRANSACTION;                              BEGIN TRANSACTION;

-- Read balance of account 1                    -- Read balance of account 2
SELECT balance FROM accounts                    SELECT balance FROM accounts 
WHERE account_id = 1;                           WHERE account_id = 2;
-- Balance is 500                               -- Balance is 200

-- Update balance of account 1                  -- Update balance of account 2
UPDATE accounts                                 UPDATE accounts
SET balance = balance - 100                     SET balance = balance - 50
WHERE account_id = 1;                           WHERE account_id = 2; 
-- Balance is now 400 (uncommitted)             -- Balance is now 150

-- Read balance of account 2                    -- Read balance of account 3 
SELECT balance FROM accounts                    SELECT balance FROM accounts
WHERE account_id = 2                            WHERE account_id = 3
-- Balance is 200                               -- Balance is 300   

-- Update balance of account 2                  -- Update balance of account 3
UPDATE accounts                                 UPDATE accounts    
SET balance = balance + 100                     SET balance = balance + 50 
WHERE account_id = 2                            WHERE account_id = 3 
-- Balance should be 300 (uncommitted)          -- Balance is 350 (uncommitted)

-- Commit Session 2 changes                     COMMIT; 
                                                -- Commit Session 2 changes
                                                -- account_id 2 balance = 150 
                                                -- account_id 3 balance = 350

-- Simulate some delay                          
WAITFOR DELAY '00:00:10';                       

-- Commit Session 1 changes                     
COMMIT;                                         
-- account_id 1 balance = 500 - 100 = 400        
-- account_id 2 balance = 200 + 100 = 300        

-- Final database state:
-- account_id 1 balance = 400
-- account_id 2 balance = 300  
-- account_id 3 balance = 350

As Jack has send $100 to Emmy and Emmy has send $50 to Alice and generally after completing the both statements there balance should be :-

-- Correct Balance should be 
-- Jack = 400   Emmy =  250   Alice = 350

-- But the Database state is due to concurrent transactions
-- Jack = 400 Emmy =  300 ALice  =  350

-- Inconsistent Database state

In the above example we have seen both transaction have completed so Atomicity was there but database was in inconsistent state. We will resolve this issue when we see the different isolation levels. But the above example shows that transaction are Atomic not not consistent.

Note :- Atomicity and Isolation both leads to consistency, in above example we have atomic transactions but for consistency we need to set different isolation levels as per out need. Let's discuss them.

Let's discuss isolation level and different isolation levels and what could be the solution to above consistency issue in part 2. See you soon.

Thanks for reading. Follow me for more such content.