What is autocommit and how does it affect transactions?
In autocommit mode every SQL statement is its own transaction and is committed as soon as it succeeds. To group statements into one atomic unit, start an explicit transaction with BEGIN or turn autocommit off.
Explain it simply
With autocommit on, every statement is saved the moment you press Enter, like a chat app sending each message instantly. A bank transfer needs two messages, debit and credit, sent as one package or not at all.
Example
With autocommit on, a crash between these two statements loses money:
UPDATE accounts SET balance = balance - 500 WHERE id = 1; -- committed immediately
-- crash here
UPDATE accounts SET balance = balance + 500 WHERE id = 2; -- never runsWrapping them in a transaction fixes it:
BEGIN;
UPDATE accounts SET balance = balance - 500 WHERE id = 1;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;
COMMIT;If anything fails before COMMIT, nothing is saved.
Defaults and switches:
- MySQL and PostgreSQL clients, and SQL Server: autocommit is on by default.
- MySQL:
SET autocommit = 0;turns it off. - Oracle's SQL*Plus does not autocommit DML, and application drivers such as JDBC default to autocommit on, so call
setAutoCommit(false)for multi-step work.
What to say in the interview
"Autocommit treats each statement as a transaction. For multi-statement atomicity I open an explicit transaction, or disable autocommit in the driver, and commit or roll back as a unit."
Common mistakes
- Running two dependent updates in autocommit mode and expecting rollback to work.