Transactions_

A transaction groups statements into a single all-or-nothing unit. Either every statement takes effect, or none do, and no other connection ever sees a half-finished state.

How a transaction works

Think of a transaction as a private draft of the database that only your connection can see:

• START TRANSACTION opens the draft. From here, your changes apply to the draft, not the shared database.
• Reads inside the transaction see the draft, so your own changes look real to you. Everyone else still sees the database as it was.
• COMMIT publishes the whole draft at once. Other connections go from seeing none of your changes to seeing all of them; there is no in-between state.
• ROLLBACK throws the draft away, and the database is as if the transaction never started.
• If your connection drops mid-transaction, the database rolls the draft back automatically.

Everything else on this page, isolation levels, locks, deadlocks, is about what happens when several connections work on their drafts at the same time.

What goes wrong without transactions

Two distinct things go wrong without them:

• Partial failure: a crash or dropped connection after the debit but before the credit leaves data in a state that was never supposed to exist.
• Interleaving: two concurrent processes both read a balance of 100, both compute a new value, both write, and one update silently vanishes.

Transactions address both: statements apply atomically, and the database isolates transactions from each other's unfinished work. InnoDB, the default storage engine, provides full transaction support.

Setup

The examples below move money between two accounts, an operation where a half-applied change must be impossible. Create and seed the table first:

CREATE TABLE accounts (
    id      BIGINT AUTO_INCREMENT PRIMARY KEY,
    owner   VARCHAR(100) NOT NULL,
    balance DECIMAL(10, 2) NOT NULL CHECK (balance >= 0)
);

INSERT INTO accounts (owner, balance) VALUES ('ada', 100.00), ('grace', 50.00);

Commit and roll back

By default MySQL runs in autocommit mode: every statement commits on its own. START TRANSACTION suspends that so several statements commit together:

START TRANSACTION;
UPDATE accounts SET balance = balance - 30 WHERE owner = 'ada';
UPDATE accounts SET balance = balance + 30 WHERE owner = 'grace';
COMMIT;

If the connection drops between the two updates, the whole transaction rolls back and the money never left Ada's account. You can also abandon a transaction deliberately:

START TRANSACTION;
DELETE FROM accounts;   -- oops
SELECT COUNT(*) FROM accounts;   -- returns 0 inside this transaction
ROLLBACK;
SELECT COUNT(*) FROM accounts;   -- returns 2, nothing happened

Inside the transaction the delete looks real, but only to that connection. ROLLBACK discards it. This makes START TRANSACTION an effective safety net for hand-run maintenance SQL.

The ACID properties describe what transactions guarantee: atomicity (all or nothing), consistency (constraints hold before and after), isolation (concurrent transactions don't see each other's partial work), and durability (committed data survives a crash).

A failed statement does not abort the transaction

Unlike some databases, an error rolls back only the statement that failed. The transaction stays open and earlier work stays intact:

START TRANSACTION;
UPDATE accounts SET balance = balance + 5 WHERE owner = 'grace';
UPDATE accounts SET balance = balance - 200 WHERE owner = 'ada';   -- fails

ERROR 3819 (HY000): Check constraint 'accounts_chk_1' is violated.

Despite the error, the transaction is still open and healthy, and committing keeps the update that succeeded:

COMMIT;   -- commits the first update; the failed one never happened

This is convenient, but it means your application must check errors per statement: blindly running a script and committing at the end can commit a half-applied change. When any statement in a unit fails and the unit must be all-or-nothing, issue ROLLBACK yourself.

Savepoints

A savepoint marks a spot you can roll back to without abandoning the whole transaction:

START TRANSACTION;
UPDATE accounts SET balance = balance - 10 WHERE owner = 'ada';

SAVEPOINT before_bonus;
UPDATE accounts SET balance = balance - 200 WHERE owner = 'ada';  -- fails
ROLLBACK TO SAVEPOINT before_bonus;

UPDATE accounts SET balance = balance + 10 WHERE owner = 'grace';
COMMIT;

Everything before the savepoint and after the rollback commits. Drivers and ORMs use savepoints to implement nested transactions.

Isolation levels

Isolation levels trade strictness for concurrency. They answer one question: what may this transaction see of other transactions' concurrent work?

MySQL's default is REPEATABLE READ, stricter than many other databases' default. Set the level for the next transaction:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
SELECT SUM(balance) FROM accounts;
COMMIT;

Under REPEATABLE READ, InnoDB serves the whole transaction from a snapshot taken at its first read, so two reads of the same data always agree even while other transactions commit changes. Plain reads never block writers; InnoDB's multi-version concurrency control keeps old row versions around for readers.

Row locking

Writers automatically lock the rows they modify until commit; a second transaction updating the same row waits. When you read a value in order to update it, that implicit protection isn't enough, because two transactions can read the same balance concurrently and both write results based on stale data. SELECT ... FOR UPDATE locks rows on read:

START TRANSACTION;
SELECT balance FROM accounts WHERE owner = 'ada' FOR UPDATE;
-- other transactions now wait to read-for-update or modify this row
UPDATE accounts SET balance = balance - 30 WHERE owner = 'ada';
COMMIT;

Locks follow the rows InnoDB scans, not just the rows that match: an UPDATE whose WHERE clause has no usable index scans the whole table and locks every row in it until commit, serializing all concurrent writers. Indexing the columns your write paths filter on is a concurrency fix, not only a speed fix. See Indexes.

For queue-like workloads where workers grab rows, add SKIP LOCKED so each worker takes the next unclaimed row instead of waiting. The claim only holds while the transaction is open, so the grab and the work's final update must share one transaction:

START TRANSACTION;
SELECT id FROM accounts ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED;
-- process the claimed row, then mark it done in the same transaction
COMMIT;

A concurrent worker running the same statement while the first transaction is open skips the claimed row and returns the next one.

Deadlocks

A deadlock occurs when two transactions each hold a lock the other needs. Session A locks Ada's row and wants Grace's; session B holds Grace's and wants Ada's. Neither can proceed, so InnoDB detects the cycle immediately and kills one:

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

The aborted transaction rolls back; the survivor continues. Two habits prevent most deadlocks:

• Lock rows in a consistent order across your codebase, for example always by ascending id.
• Keep transactions short. Don't hold one open across network calls or user input.

The error message says it directly: the application should retry the aborted transaction. SHOW ENGINE INNODB STATUS includes a LATEST DETECTED DEADLOCK section with both queries when you need to diagnose one.

Transactions and pooling

Transaction state lives on the connection, which matters when connecting through a pooler in transaction mode: session-level settings such as SET SESSION TRANSACTION ISOLATION LEVEL won't stick between statements, and every multi-statement unit must be wrapped in an explicit START TRANSACTION/COMMIT so it lands on one server connection. See Connection pooling for how this applies to your Appwrite database.