MySQL Transactions and ACID Properties

A transaction is a group of statements that succeed or fail as one unit.

ACID

Property	Meaning
Atomicity	All statements apply, or none do
Consistency	The database moves from one valid state to another; constraints hold
Isolation	Concurrent transactions do not see each other's half-finished work
Durability	Once committed, changes survive a crash or power loss

The classic example

START TRANSACTION;
  UPDATE accounts SET balance = balance - 5000 WHERE id = 1;
  UPDATE accounts SET balance = balance + 5000 WHERE id = 2;
COMMIT;

Without a transaction, a crash between the two statements would destroy 5,000 taka. With one, the money either moves completely or not at all.

Rolling back

START TRANSACTION;
  DELETE FROM students WHERE department = 'CSE';
  SELECT ROW_COUNT();       -- 412?! not what I expected
ROLLBACK;                   -- nothing happened

Autocommit

By default MySQL commits every statement immediately:

SELECT @@autocommit;     -- 1
SET autocommit = 0;      -- now you must COMMIT explicitly

START TRANSACTION temporarily suspends autocommit until you COMMIT or ROLLBACK.

Savepoints

Savepoints let you undo part of a transaction:

START TRANSACTION;
  INSERT INTO orders (customer_id, total) VALUES (1, 2500);
  SAVEPOINT order_created;

  INSERT INTO order_items (order_id, product_id, qty) VALUES (LAST_INSERT_ID(), 9, 2);
  -- the product turned out to be discontinued
  ROLLBACK TO SAVEPOINT order_created;   -- keep the order, drop the item

  INSERT INTO order_items (order_id, product_id, qty) VALUES (LAST_INSERT_ID(), 12, 2);
COMMIT;

RELEASE SAVEPOINT order_created;

Implicit commits

Some statements commit automatically and cannot be rolled back — every DDL statement (CREATE, ALTER, DROP, TRUNCATE), plus LOCK TABLES, START TRANSACTION itself, and user-management statements. Never mix DDL into a data transaction and expect to undo it.

Durability settings

SELECT @@innodb_flush_log_at_trx_commit;   -- 1 = fully ACID

1 (default) — flush and sync the log at every commit. Safest.
2 — write to the OS cache each commit, sync once per second. Survives a MySQL crash but not an OS crash.
0 — sync once per second. Fastest, up to one second of committed data can be lost.

Only relax this on machines where losing recent writes is acceptable.

Application practice

Keep transactions short. A transaction holds locks from its first write until commit, so a long one blocks other users and grows the undo log. Never leave a transaction open across a user interaction, an HTTP call to another service, or a file upload.