TRANSACTION / COMMIT / ROLLBACK

-- Starts a transaction (MySQL).
START TRANSACTION;

-- Starts a transaction (standard SQL / PostgreSQL).
BEGIN;

-- Commits the changes.
COMMIT;

-- Rolls back all changes.
ROLLBACK;

-- Sets a savepoint.
SAVEPOINT savepoint_name;

-- Rolls back to a savepoint.
ROLLBACK TO SAVEPOINT savepoint_name;

Syntax	Description
START TRANSACTION	Starts a transaction (MySQL). Auto-commit is disabled for the duration of the transaction.
BEGIN	Starts a transaction (standard SQL / PostgreSQL). Also works in MySQL.
COMMIT	Permanently saves all changes made within the transaction to the database.
ROLLBACK	Discards all changes made within the transaction and restores the database to its state before the transaction began.
SAVEPOINT name	Sets an intermediate checkpoint within a transaction. You can partially roll back to this point later.
ROLLBACK TO SAVEPOINT name	Reverts changes back to the specified savepoint. The transaction itself continues.

-- Money transfer: moving $10,000 from account A to account B.
START TRANSACTION;

UPDATE accounts SET balance = balance - 10000 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 10000 WHERE account_id = 2;

-- If both UPDATEs succeed, commit the changes.
COMMIT;

-- If an error occurs, roll back (handled by the application).
-- ROLLBACK;

-- Example using a savepoint.
START TRANSACTION;

INSERT INTO orders (customer_id, total) VALUES (101, 15000);
SAVEPOINT after_order;

INSERT INTO order_products (order_id, product_id, quantity) VALUES (LAST_INSERT_ID(), 5, 2);

-- Roll back only this INSERT, keeping the order header.
ROLLBACK TO SAVEPOINT after_order;

COMMIT;

-- State of the accounts table before and after COMMIT (example)
-- Before COMMIT (as seen within the transaction):
-- +------------+---------+
-- | account_id | balance |
-- +------------+---------+
-- |          1 |   40000 |  -- 50000 - 10000
-- |          2 |   30000 |  -- 20000 + 10000
-- +------------+---------+

-- After ROLLBACK (no changes applied):
-- +------------+---------+
-- | account_id | balance |
-- +------------+---------+
-- |          1 |   50000 |
-- |          2 |   20000 |
-- +------------+---------+

In MySQL, use START TRANSACTION or BEGIN to start a transaction.

START TRANSACTION;
UPDATE accounts SET balance = balance - 10000 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 10000 WHERE account_id = 2;
COMMIT;

In PostgreSQL, use BEGIN to start a transaction. START TRANSACTION is also supported.

BEGIN;
UPDATE accounts SET balance = balance - 10000 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 10000 WHERE account_id = 2;
COMMIT;

In Oracle, transactions start implicitly — there is no auto-commit by default. A transaction begins automatically when you execute a DML statement and ends with COMMIT or ROLLBACK.

-- In Oracle, BEGIN is not needed (transaction starts automatically on DML).
UPDATE accounts SET balance = balance - 10000 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 10000 WHERE account_id = 2;
COMMIT;

In SQL Server, use BEGIN TRANSACTION to start a transaction.

BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 10000 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 10000 WHERE account_id = 2;
COMMIT;

In SQLite, use BEGIN TRANSACTION to start a transaction. The default mode is auto-commit.

BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 10000 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 10000 WHERE account_id = 2;
COMMIT;

Transactions guarantee four properties known as ACID: Atomicity, Consistency, Isolation, and Durability. Always use transactions for operations that span multiple tables — such as bank transfers or inventory deductions combined with order registration — to ensure no partial state is left behind if a failure occurs.

MySQL defaults to auto-commit mode (AUTO_COMMIT=1). Issuing START TRANSACTION temporarily disables auto-commit for that transaction. You can permanently disable auto-commit with SET AUTOCOMMIT = 0, but be careful not to forget to issue COMMIT.

For managing privileges, see GRANT / REVOKE.