Hiprup

What are ACID properties in MySQL?

ACID defines the four properties a relational database must guarantee for transactions to be reliable. MySQL with the InnoDB engine is fully ACID-compliant.

  • Atomicity — all statements in a transaction succeed or none do; ROLLBACK undoes partial work.

  • Consistency — transactions move the DB from one valid state to another; constraints, foreign keys, triggers all enforced.

  • Isolation — concurrent transactions don't interfere; controlled by isolation levels (READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE).

  • Durability — once committed, data survives crashes; InnoDB uses redo logs and fsync.

-- Transaction example: bank transfer
START TRANSACTION;

UPDATE accounts SET balance = balance - 500 WHERE id = 1;  -- Debit
UPDATE accounts SET balance = balance + 500 WHERE id = 2;  -- Credit

-- Check: if something went wrong
-- ROLLBACK;  -- Undo both changes

COMMIT;  -- Make both changes permanent

-- SAVEPOINT for partial rollback
START TRANSACTION;
INSERT INTO orders (user_id, total) VALUES (1, 99.99);
SAVEPOINT after_order;
INSERT INTO order_items (order_id, product_id) VALUES (LAST_INSERT_ID(), 42);
-- Oops, wrong product!
ROLLBACK TO after_order;  -- Undo only the order_item insert
INSERT INTO order_items (order_id, product_id) VALUES (LAST_INSERT_ID(), 43);
COMMIT;

-- Set isolation level
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- Check current isolation level
SELECT @@transaction_isolation;  -- REPEATABLE-READ (default)

START TRANSACTION begins a transaction. Both UPDATEs succeed or both roll back (atomicity). COMMIT makes changes permanent (durability).

ROLLBACK undoes all changes. SAVEPOINT allows partial rollback. MySQL's default isolation is REPEATABLE READ — no dirty reads or phantom reads within InnoDB.

Know all four: Atomicity (all or nothing), Consistency (valid state), Isolation (no interference), Durability (permanent after commit). The bank transfer example demonstrates atomicity.

Know the four isolation levels and MySQL's default (REPEATABLE READ). SAVEPOINT for partial rollback is an advanced detail.

What are ACID properties in MySQL? | Hiprup