How to use transactions in SQL

Short answer

A transaction is a sequence of operations performed as a single logical unit of work. Use BEGIN, COMMIT, and ROLLBACK to control transactions.

Steps

1. Start a transaction: BEGIN; or START TRANSACTION;
2. Execute DML statements: UPDATE accounts SET balance = balance - 100 WHERE id = 1;
3. Commit if all operations succeed: COMMIT;
4. Rollback on failure: ROLLBACK;
5. Use savepoints for partial rollbacks: SAVEPOINT sp1; ROLLBACK TO sp1;

Tips

• Keep transactions as short as possible to reduce locking contention.
• Choose the appropriate isolation level based on consistency and concurrency requirements.

Common issues

• Long-running transactions hold locks and can block other users or cause deadlocks.
• Auto-commit mode can silently commit partial changes if an error occurs between statements.r