ACID

ACID is the set of four promises a transactional storage engine makes about a unit of work. In MySQL 8.4 the default engine, InnoDB, makes all four. MyISAM makes none of them: it has no transactions, so START TRANSACTION and ROLLBACK are accepted there but change nothing — the rollback reports only a warning, Some non-transactional changed tables couldn't be rolled back.

The four letters

• Atomicity — the statements between START TRANSACTION and COMMIT all take effect or none do. Backed by the undo log, and by ROLLBACK.
• Consistency — the manual maps this to the InnoDB doublewrite buffer and crash recovery: the data on disk is never left as a half-written page.
• Isolation — a transaction does not see another transaction's unfinished work. Controlled by the isolation level and by locking.
• Durability — once COMMIT returns, the change survives a crash. Depends on innodb_flush_log_at_trx_commit, sync_binlog, and on the disk actually honouring fsync().

What it does not cover

The C in ACID is not "my business rules hold". Nothing in a transaction stops you committing a negative balance — that is the job of NOT NULL, CHECK, UNIQUE and FOREIGN KEY.

Atomicity also stops at DDL. And a statement error does not roll the transaction back for you: MySQL undoes the failed statement and leaves the transaction open, so an application that ignores the error can commit a half-finished unit of work.

-- Atomicity in practice: both balances move, or neither does.
-- ACID only applies on a transactional engine, so check first.
SELECT TABLE_NAME, ENGINE
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME IN ('accounts', 'ledger');   -- both must report InnoDB

START TRANSACTION;

UPDATE accounts SET balance = balance - 250.00 WHERE id = 7;
UPDATE accounts SET balance = balance + 250.00 WHERE id = 12;

INSERT INTO ledger (from_account, to_account, amount, created_at)
VALUES (7, 12, 250.00, NOW());

-- Isolation: no other session sees any of the three changes until here.
-- Durability: once this returns, a power cut cannot lose them.
COMMIT;

-- If the application had raised an error after the first UPDATE, it would
-- issue ROLLBACK instead, and the accounts table would be untouched.

START TRANSACTION;

UPDATE accounts SET balance = balance - 250.00 WHERE id = 7;

-- Suppose this violates a CHECK constraint and returns an error.
UPDATE accounts SET balance = balance + 250.00 WHERE id = 99999;
-- ERROR 3819 (HY000): Check constraint 'chk_balance_non_negative' is violated.

-- MySQL has rolled back ONLY the failing statement. The first UPDATE
-- is still pending. Committing now would destroy 250.00.
ROLLBACK;   -- this is the correct response, not COMMIT

SELECT @@GLOBAL.innodb_flush_log_at_trx_commit AS flush_at_commit,
       @@GLOBAL.sync_binlog                    AS sync_binlog;

-- innodb_flush_log_at_trx_commit = 1
--   log buffer written and flushed to disk at each commit: full ACID.
-- innodb_flush_log_at_trx_commit = 0 or 2
--   faster, but a crash can lose the last second or so of committed
--   transactions. This is a deliberate trade, not a bug.

-- A transaction will happily commit nonsense. Constraints will not.
ALTER TABLE accounts
  ADD CONSTRAINT chk_balance_non_negative CHECK (balance >= 0);
-- CHECK constraints are enforced from MySQL 8.0.16 onward.

START TRANSACTION;
UPDATE accounts SET balance = balance - 1000000 WHERE id = 7;
-- ERROR 3819 (HY000): Check constraint 'chk_balance_non_negative' is violated.
ROLLBACK;