BEGIN TRANSACTION starts a database transaction, COMMIT permanently saves all changes made during it, and ROLLBACK cancels those uncommitted changes. Together, they ensure that a related sequence of database operations succeeds or fails as one unit.
How the Commands Work
A transaction is a sequence of database operations treated as one logical unit. Transactions support atomicity, meaning either every required operation is completed or none of them is applied.
| SQL command | Function |
|---|---|
BEGIN TRANSACTION; | Marks the start of the transaction. Subsequent changes are provisional until committed. |
COMMIT; | Makes every change in the transaction permanent and ends the transaction. |
ROLLBACK; | Reverses changes made since the transaction began and ends the transaction. |
Consider transferring 100 units from account A to account B:
BEGIN TRANSACTION;
UPDATE Accounts
SET balance = balance - 100
WHERE account_id = 'A';
UPDATE Accounts
SET balance = balance + 100
WHERE account_id = 'B';
COMMIT;
Both UPDATE statements belong to the same transaction. If the second update fails, the program should execute ROLLBACK; instead of COMMIT;. This restores account A's original balance, preventing money from being removed without being added to account B.
This mechanism maintains database consistency and protects data integrity. It is particularly important when operations depend on one another, such as processing payments, reserving seats, or updating stock after an order.
A common misconception is that COMMIT; merely ends a transaction. It also makes its changes permanent. Similarly, ROLLBACK; cannot normally reverse changes that have already been committed.
IB Exam Technique
For an A3.3 database programming question, define each command and explain why transactions are needed. In an explain response, link ROLLBACK; to failure recovery and atomicity rather than only stating that it “undoes changes.” A transfer example clearly demonstrates how the commands prevent a partially completed update from leaving the database inconsistent.
