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;