Introduction
A transaction is a sequence of one or more SQL statements executed as a single logical unit of work. Transactions let you group related INSERT, UPDATE, and DELETE statements so that they succeed or fail together. SQL provides BEGIN (or START TRANSACTION) to open a transaction, COMMIT to permanently save its changes, and ROLLBACK to discard them, along with SAVEPOINT to create partial rollback points within a longer transaction.
Cricket analogy: A DRS review bundles ball-tracking, snickometer, and umpire's call into one decision -- BEGIN starts the review, COMMIT upholds the field decision permanently, ROLLBACK overturns it entirely, and a SAVEPOINT is like keeping the soft-signal as a fallback.
Syntax
BEGIN TRANSACTION;
-- one or more DML statements
INSERT INTO orders (customer_id, total) VALUES (42, 250.00);
UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 7;
COMMIT; -- make the changes permanent
-- or
ROLLBACK; -- discard all changes since BEGINExplanation
BEGIN TRANSACTION marks the start of a transaction; statements executed after it are not permanently applied until COMMIT is issued. COMMIT ends the transaction successfully, making every change since BEGIN visible and durable to other sessions. ROLLBACK ends the transaction by undoing every change since BEGIN, restoring the database to the state it was in before the transaction started. SAVEPOINT lets you mark an intermediate point inside a transaction so you can roll back only part of the work with ROLLBACK TO SAVEPOINT, without discarding the entire transaction. Most database systems also run in autocommit mode by default, where each individual statement is automatically wrapped in its own transaction unless an explicit BEGIN is used.
Cricket analogy: Once the third umpire opens a review (BEGIN), the on-field call stays provisional until the final decision (COMMIT) makes it official for the scoreboard; ROLLBACK would restore the original call, ROLLBACK TO SAVEPOINT could revert just to the soft signal, and without a review, each ball's umpire call is final immediately, like autocommit.
Example
BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
SAVEPOINT after_debit;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
-- suppose we detect account 2 is closed and want to undo only the credit
ROLLBACK TO SAVEPOINT after_debit;
-- retry crediting a different account instead
UPDATE accounts SET balance = balance + 100 WHERE account_id = 3;
COMMIT;Output
After this transaction commits, account 1 has lost 100, account 2 is unchanged because its credit was rolled back to the savepoint, and account 3 has gained 100. If the entire transaction had been rolled back instead of committed, none of the three accounts would show any change at all, as if the transaction had never run.
Cricket analogy: After the review process, the batsman's dismissal stands, the no-ball call reverts to the savepoint and stays unchanged, and the fielding side's wicket tally increases -- but if the whole review had been overturned, the scoreboard would show no change at all.
Key Takeaways
- BEGIN TRANSACTION starts a unit of work; COMMIT saves it permanently, ROLLBACK discards it entirely.
- Without an explicit transaction, most databases autocommit each statement individually.
- SAVEPOINT creates a named point inside a transaction that you can roll back to without losing the whole transaction.
- Once COMMIT succeeds, the changes are durable and visible to all other sessions.
- A ROLLBACK returns the database to its state before BEGIN, as if the statements never executed.
Practice what you learned
1. What does the COMMIT statement do?
2. What happens to changes made after BEGIN TRANSACTION if ROLLBACK is issued before COMMIT?
3. What is the purpose of a SAVEPOINT?
4. In autocommit mode, when is each individual SQL statement treated as committed?
Was this page helpful?
You May Also Like
ACID Properties
The four guarantees—Atomicity, Consistency, Isolation, Durability—that make database transactions reliable.
Locking and Concurrency Control
How databases use locks and isolation levels to let transactions run concurrently without corrupting data.
The INSERT Statement
Learn how to add new rows to a table using single-row, multi-row, and INSERT...SELECT forms.