Transaction

Refer Transaction before continuing.

Only DML queries can ROLLBACK within a transaction. DDL queries are auto-committed.

Usually, each SQL statement is committed automatically. Auto commits can be disabled using the SET AUTOCOMMIT OFF statement.

-- Successful transaction example: transfer $100 from account 1 to account 2
BEGIN;
UPDATE accounts
SET balance = balance - 100
WHERE id = 1;

UPDATE accounts
SET balance = balance + 100
WHERE id = 2;

COMMIT;

-- Transaction example showing an explicit ROLLBACK on error/condition
BEGIN;
-- Lock and check balance (example; actual check may be performed by application logic)
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;

UPDATE accounts
SET balance = balance - 100
WHERE id = 1;

-- Suppose we detect the balance went negative (check performed in client or stored procedure).
-- In that case we abort the whole transaction:
ROLLBACK;

-- Otherwise, if the check passes, credit the destination and commit:
-- UPDATE accounts SET balance = balance + 100 WHERE id = 2;
-- COMMIT;