TopolearnMySQL's defaults differ from PostgreSQL's, and implicit commits catch people out during migrations.
InnoDB is transactional, but by default MySQL runs in autocommit mode: each statement commits on its own. To group statements, use START TRANSACTION (or BEGIN) ... COMMIT / ROLLBACK. Many drivers turn autocommit off and expect you to commit explicitly, so check yours.
Things that differ from other databases:
- DDL causes an implicit commit.
CREATE TABLE,ALTER TABLE,DROPand several other statements commit the current transaction before and after running. A migration can't be wrapped in a transaction and rolled back as a whole; MySQL 8's atomic DDL only makes each single DDL statement all-or-nothing. - The default isolation level is REPEATABLE READ. Plain
SELECTs are consistent reads from a snapshot taken at the transaction's first read, so repeated reads return the same data. - But locking reads and writes see the latest committed data, not the snapshot. An
UPDATEinside a Repeatable Read transaction can modify rows your earlierSELECTdidn't see. UseSELECT ... FOR UPDATEwhen you read in order to write. - An error in a statement rolls back only that statement, not the whole transaction (except deadlocks and lock wait timeouts, which may roll back more). Your code must decide whether to continue or roll back.
SET TRANSACTION ISOLATION LEVEL READ COMMITTED is a common choice for high-concurrency apps: fewer gap locks and less lock contention.
Example
CREATE TABLE account (id INT PRIMARY KEY, balance DECIMAL(10,2) NOT NULL);
INSERT INTO account VALUES (1, 100), (2, 50);
START TRANSACTION;
SELECT balance FROM account WHERE id = 1 FOR UPDATE; -- lock the row we'll change
UPDATE account SET balance = balance - 30 WHERE id = 1;
UPDATE account SET balance = balance + 30 WHERE id = 2;
COMMIT;
START TRANSACTION;
UPDATE account SET balance = 0 WHERE id = 1;
CREATE TABLE audit_log (id INT); -- implicit COMMIT happens here!
ROLLBACK; -- too late: the UPDATE is already committed
SELECT balance FROM account WHERE id = 1; -- 0.00
SELECT @@transaction_isolation; -- REPEATABLE-READRemember
- Autocommit is on by default; use START TRANSACTION to group statements
- DDL implicitly commits; migrations can't be rolled back as a unit
- Default isolation is REPEATABLE READ; use FOR UPDATE when reading to write
Watch out for
- Running DDL in the middle of a transaction and expecting ROLLBACK to undo earlier statements.