Transactions and ACID

Asha sends Ravi Rs 500. Inside the bank's database that is two changes: take 500 from Asha's balance, add 500 to Ravi's. Now imagine the server loses power after the first change and before the second. Asha is down 500, Ravi got nothing, and the money has simply left the universe.

A transaction is the database's promise that this cannot happen. A group of changes either all take effect or none of them do, even if the machine dies halfway through. It is the single most important thing a relational database gives you, and the reason banks, payments and bookings still run on one.

A transaction in SQL#

BEGIN;
UPDATE accounts SET balance = balance - 500 WHERE id = 'asha';
UPDATE accounts SET balance = balance + 500 WHERE id = 'ravi';
COMMIT;

Between BEGIN and COMMIT, nothing is final. If anything fails, a constraint, a crash, or your code calling ROLLBACK, every change since BEGIN is undone as if it never happened.

ACID, in plain words#

The four letters are the four promises a transaction makes.

Atomic. All or nothing. Both updates happen, or neither does. There is no state where only one did.

Consistent. A transaction moves the database from one valid state to another. Rules you declared, such as CHECK (balance >= 0), a foreign key, or a unique email, are never broken by a committed transaction. If one would be, the whole transaction is rejected.

Isolated. Transactions running at the same time do not see each other's half finished work. How strictly this holds is adjustable, and getting it wrong causes real bugs, so it has its own chapter.

Durable. Once the database says COMMIT succeeded, the change survives a crash, a power cut or a restart.

How the database keeps the promise#

The trick behind atomic and durable is the write ahead log, or WAL. Before the database changes the real table pages, it first appends a record of the change to a log file on disk. Appending to the end of a file is fast, and it is the log, not the tables, that has to be safe before COMMIT returns. Step through a transfer that gets interrupted.

So the answer to the question at the top: yes, the transfer survives. COMMIT does not return until the log is safely on disk, and the log is enough to rebuild everything else. The table pages are written to disk later, in the background, in big efficient batches.

Keep transactions short#

A transaction holds locks on the rows it changed until it ends, so other transactions that want those rows wait. The longer it stays open, the more everyone else queues behind it.

The most common mistake: calling something slow inside a transaction.

BEGIN
  insert order
  call payment gateway     <- 3 seconds, sometimes 30
  update order status
COMMIT

For those seconds, the order rows stay locked, a database connection is held, and if the gateway times out you are left deciding whether the payment happened. Do the external call outside the transaction, record its result in a short transaction of its own, and design the external call to be safe to retry (an idempotency key), the way Design a UPI style payment rail does.

When one database is not enough#

A transaction protects changes inside one database. The moment a business action spans two services, say the order service and the wallet service, each with its own database, there is no single BEGIN and COMMIT covering both. The usual answer is a saga: a series of local transactions, each with a compensating action that undoes it (refund the wallet if the order fails), coordinated through events. It is more work and weaker than a real transaction, which is a good reason to keep data that must change together in the same database.

Checkpoint#