Transactions, ACID, and what happens on a crash

What a transaction really guarantees, and how the database survives losing power mid-write.

12 min read · Lesson 6 of 6 · Pro

The problem in one example

Transfer 500 rupees from account A to account B.

SQL

UPDATE accounts SET balance = balance - 500 WHERE acc_id = 'A';
UPDATE accounts SET balance = balance + 500 WHERE acc_id = 'B';

Two statements. The power goes off between them. A has lost 500 rupees and B never received it. The money is gone from the universe.

A transaction is the fix. It wraps several statements so that either all of them happen or none of them do.

SQL

START TRANSACTION;

UPDATE accounts SET balance = balance - 500 WHERE acc_id = 'A';
UPDATE accounts SET balance = balance + 500 WHERE acc_id = 'B';

COMMIT;

The rest of this lesson is Pro

The free lessons of DBMS and SQL finish what they start — read those first if you have not. This one goes further, and it is part of the paid half.

A pass opens the paid lessons of every course, the mock test papers and the company-wise series. It ends on its own date; nothing renews by itself.

Get a pass — from ₹29 for 7 days

Already bought one? Log in.