Transactions & ACID Properties

All-or-nothing — ব্যাংকিং সিস্টেমের ভিত্তি

Read: ~35 min Advanced 14 practice problems Live SQLite runner

1. The Bank Transfer That Goes Half-Way

Imagine a bKash transfer of 5,000 BDT from Arif to Mim:

  1. Subtract 5,000 from Arif's balance.
  2. Add 5,000 to Mim's balance.

What if the server crashes between step 1 and step 2? Arif's money is gone, Mim never received it. 5,000 taka has just vanished from the universe — purely because the database executed only half a job.

যদি প্রথম update-এর পরে কিন্তু দ্বিতীয় update-এর আগে server crash করে — Arif-এর টাকা কাটল কিন্তু Mim-এর কাছে পৌঁছাল না। এই "half done" অবস্থা কোনোভাবেই গ্রহণযোগ্য নয়। সেইজন্যই database-এ transaction।

A transaction is a group of SQL statements treated as a single, indivisible unit: either all of them happen, or none. This module teaches you how to write transactions and the four formal guarantees they provide — ACID.

2. BEGIN, COMMIT, ROLLBACK

Three commands fence off a transaction:

CommandEffect
BEGIN TRANSACTION (or just BEGIN)Start a new transaction. From here onwards, changes are tentative.
COMMITMake every change since BEGIN permanent and visible to others.
ROLLBACKThrow away every change since BEGIN. The DB returns to the pre-transaction state.
basic_tx.sql
CREATE TABLE account(name TEXT PRIMARY KEY, balance INTEGER);
INSERT INTO account VALUES ('Arif', 10000), ('Mim', 2000);

-- A transfer of 5000 from Arif to Mim, atomically.
BEGIN TRANSACTION;
UPDATE account SET balance = balance - 5000 WHERE name = 'Arif';
UPDATE account SET balance = balance + 5000 WHERE name = 'Mim';
COMMIT;

SELECT * FROM account;
rollback.sql
CREATE TABLE account(name TEXT PRIMARY KEY, balance INTEGER);
INSERT INTO account VALUES ('Arif', 10000), ('Mim', 2000);

-- A buggy transfer — we change our mind and ROLLBACK.
BEGIN;
UPDATE account SET balance = balance - 5000 WHERE name = 'Arif';
-- (server crash, app exception, or the developer realizes a bug)
ROLLBACK;

SELECT * FROM account;
-- Balances unchanged: 10,000 / 2,000.
Auto-commit Most database connections start in auto-commit mode: every individual statement is its own transaction that commits immediately. Wrapping work in BEGIN ... COMMIT turns this off until COMMIT or ROLLBACK.

3. The Four ACID Guarantees

Coined by Theo Härder and Andreas Reuter (1983), ACID is the contract every classical DBMS offers for the lifetime of one transaction.

LetterPropertyPromise
AAtomicityAll statements happen, or none. No half-done state ever survives.
CConsistencyThe transaction takes the database from one valid state to another. All declared constraints hold before and after.
IIsolationConcurrent transactions appear to run as if they were serial — none can see another's tentative changes.
DDurabilityOnce COMMIT returns, the change survives crashes, power cuts, even disk reboots.
ACID-এর সহজ ব্যাখ্যা — A = সব অথবা কিছুই না; C = constraint সব সময় বজায়; I = একাধিক transaction একে অপরকে "অর্ধেক" অবস্থায় দেখবে না; D = commit-এর পরে data হারানো সম্ভব নয় — power cut হলেও না।

4. Atomicity — All or Nothing

The bKash transfer is the textbook example. Either both updates land (Arif −5,000, Mim +5,000) or neither does. There is no third state.

atomicity_check.sql
CREATE TABLE account(name TEXT PRIMARY KEY, balance INTEGER CHECK(balance >= 0));
INSERT INTO account VALUES ('Arif', 3000), ('Mim', 2000);

-- Arif tries to transfer 5,000 but only has 3,000. CHECK fails.
-- The whole transaction must roll back automatically.
BEGIN;
UPDATE account SET balance = balance - 5000 WHERE name = 'Arif';
-- The above will fail with: CHECK constraint failed: balance >= 0
UPDATE account SET balance = balance + 5000 WHERE name = 'Mim';
COMMIT;

SELECT * FROM account;
App-level discipline matters too Atomicity protects against crashes inside the DB. Your application code still has to actually wrap related statements in BEGIN/COMMIT and call ROLLBACK on errors. A try/except that swallows errors and forgets to roll back is itself a bug.

5. Consistency, Isolation & Durability

Consistency

Constraints declared in the schema (NOT NULL, CHECK, FOREIGN KEY, UNIQUE) hold both before and after every transaction. The DBMS will refuse to commit a transaction that would leave the database in an invalid state.

Consistency = কোনো transaction শেষে যেন কোনো declared constraint লঙ্ঘিত না হয়। লঙ্ঘিত হলে DBMS commit-ই করবে না, পুরো transaction-কে rollback করে দেবে।

Isolation

If 100 users transfer money simultaneously, no transaction should see another transaction's tentative intermediate state. Two parallel transfers from Arif must not both see his full balance and both succeed.

Modern databases offer several isolation levels (READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE) trading safety for speed — covered in Module 32.

Durability

After COMMIT returns, the data must survive a power cut. Databases achieve this with a write-ahead log (WAL): every change is appended to a log file on disk before it touches the actual data file. After a crash, the DB replays the log and arrives at the exact committed state. (Module 33 has the full story.)

Durability ≠ never crashes Servers can absolutely crash. Durability means: committed work is not lost in a crash. In-flight transactions are simply rolled back at recovery time.

6. SAVEPOINT — Partial Rollback

Sometimes you want to undo part of a transaction without abandoning all of it. SAVEPOINT marks a sub-checkpoint inside the running transaction; ROLLBACK TO SAVEPOINT name rewinds to that mark while keeping the surrounding transaction alive.

savepoint.sql
CREATE TABLE orders(id INTEGER PRIMARY KEY, item TEXT, amount INTEGER);

BEGIN;
INSERT INTO orders VALUES (1, 'Phone', 32000);
SAVEPOINT after_phone;

INSERT INTO orders VALUES (2, 'Charger', 800);
INSERT INTO orders VALUES (3, 'Cover', 300);

-- Customer cancels the accessories but keeps the phone.
ROLLBACK TO SAVEPOINT after_phone;

COMMIT;

SELECT * FROM orders;
-- Result: only the Phone row survives.
SAVEPOINT-কে চিন্তা করুন একটি sub-checkpoint হিসেবে। transaction পুরোপুরি rollback না করে শুধু একটি নির্দিষ্ট অংশ পর্যন্ত ফিরে যাওয়া যায়।

7. Pitfalls of Long-Running Transactions

Transactions are not free. While a transaction is open it holds locks (or MVCC snapshots) that can block other writers. Common mistakes:

  • Wrapping a network call in a transaction. The DB sits idle holding locks while you wait for the network.
  • Forgetting COMMIT in long-lived sessions. Every other writer queues behind you.
  • Not handling errors. An exception path that does not ROLLBACK can keep the DB session inside a failed transaction forever.
  • Massive bulk loads in a single transaction. May exhaust the WAL or undo log.
Rule of thumb Keep transactions short and local to the database. Acquire all the rows you need, do the work in code that does not touch the network, write the result, commit. If you must call an external service, do it before BEGIN or after COMMIT.

8. Glossary (শব্দকোষ)

TermMeaningবাংলায়
TransactionA group of statements treated as a single atomic unit.একগুচ্ছ statement-কে একটি অখণ্ড একক হিসেবে গণ্য করা।
BEGINStart a transaction. Subsequent changes are tentative.Transaction শুরু; এর পরের সব change tentative।
COMMITMake all changes since BEGIN permanent and visible.সব পরিবর্তন স্থায়ী ও দৃশ্যমান করা।
ROLLBACKDiscard every change since BEGIN.BEGIN-এর পরের সব পরিবর্তন বাতিল।
SAVEPOINTSub-checkpoint within a running transaction.চলমান transaction-এর ভেতরে একটি sub-checkpoint।
ACIDAtomicity, Consistency, Isolation, Durability.Transaction-এর চারটি গ্যারান্টি।
WALWrite-ahead log — disk log of changes used for durability and recovery.Disk-এ change-এর log file, যা durability নিশ্চিত করে।

9. Practice Problems

  1. Wrap a 1,000 BDT transfer from Arif to Mim in a transaction. Show the final balances.
    1,000 BDT transfer-কে transaction-এ মুড়িয়ে চালান।
    ✨ Show Answer
    ans1.sql
    CREATE TABLE account(name TEXT PRIMARY KEY, balance INTEGER);
INSERT INTO account VALUES ('Arif', 5000), ('Mim', 3000);

BEGIN;
UPDATE account SET balance = balance - 1000 WHERE name='Arif';
UPDATE account SET balance = balance + 1000 WHERE name='Mim';
COMMIT;

SELECT * FROM account;
  2. Start a transaction, debit Arif by 1,000, then call ROLLBACK. Verify balances are unchanged.
    Rollback-এর পরে balance অপরিবর্তিত আছে কি না যাচাই করুন।
    ✨ Show Answer
    ans2.sql
    CREATE TABLE account(name TEXT PRIMARY KEY, balance INTEGER);
INSERT INTO account VALUES ('Arif', 5000), ('Mim', 3000);

BEGIN;
UPDATE account SET balance = balance - 1000 WHERE name='Arif';
ROLLBACK;

SELECT * FROM account;
  3. Use a savepoint to insert 3 items, then rewind so only the first survives.
    Savepoint দিয়ে শুধু প্রথম item রাখুন।
    ✨ Show Answer
    ans3.sql
    CREATE TABLE cart(item TEXT);

BEGIN;
INSERT INTO cart VALUES ('Phone');
SAVEPOINT kept;
INSERT INTO cart VALUES ('Charger');
INSERT INTO cart VALUES ('Cover');
ROLLBACK TO SAVEPOINT kept;
COMMIT;

SELECT * FROM cart;
  4. Which letter of ACID does a CHECK(balance >= 0) constraint help enforce, and why?
    CHECK constraint মূলত ACID-এর কোন বৈশিষ্ট্য রক্ষা করে?
    ✨ Show Answer

    Answer: C — Consistency. The constraint encodes a database-level rule (no negative balances). The DBMS will refuse to commit any transaction that would violate it, ensuring the database moves only between valid states.

  5. Why is wrapping a network call to an external API inside a transaction a bad idea?
    Transaction-এর ভেতরে network call কেন খারাপ?
    ✨ Show Answer

    Answer: The transaction holds locks (or an MVCC snapshot) for the entire duration of the network round-trip. Network calls are slow and unreliable, so other writers queue up behind you, latency tail explodes, and a network hang turns into a database-wide outage.

  6. After COMMIT, the power cuts. Will the change survive? Which ACID letter does this answer?
    Commit-এর পরে power cut। Data বেঁচে থাকবে কেন?
    ✨ Show Answer

    Answer: Yes. D — Durability. COMMIT is not allowed to return until the change (or at least an entry in the write-ahead log describing it) has been forced to non-volatile storage. After the power comes back, the DB replays the log to reach the same committed state.

Summary — Module 30

A transaction is the unit of trust. BEGIN, COMMIT, ROLLBACK and SAVEPOINT let you fence a group of statements as one indivisible operation. The four ACID properties — Atomicity, Consistency, Isolation, Durability — are the contract every classical DBMS keeps, and they are the reason banking, payments, ticketing and every other money-touching system on Earth runs on a database. Keep transactions short, never wrap external calls in them, and always handle errors with an explicit ROLLBACK.

Transaction = সব অথবা কিছুই না। ACID = সেই প্রতিশ্রুতির আনুষ্ঠানিক রূপ। Transaction যত ছোট, system তত স্বাস্থ্যকর।

Next Module → Concurrency Control — locks, 2PL and MVCC: how the DB makes many transactions feel like one at a time.

Prev: Logical → Physical Next: Concurrency Control