Lesson 11 of 14 · 7 min · SQL for applications
Transactions: BEGIN, COMMIT, ROLLBACK and savepoints
What you will learn
- Why transactions exist, using a money transfer
BEGIN,COMMITandROLLBACK, and what autocommit means- Savepoints for partial rollback
- Row locks, waiting and deadlocks
- Habits for safe, short transactions
In simple words
When you pay a friend through a bank app, two things must happen: money leaves your account and money arrives in theirs. If the app crashed between the two steps, money would vanish. A transaction wraps the steps into one package that either happens completely or not at all. If anything goes wrong in the middle, the database puts everything back as it was.
Words to know
- Transaction: a group of statements treated as one all-or-nothing unit.
- Commit: make the transaction's changes permanent.
- Rollback: undo everything the transaction did so far.
- Lock: a hold on a row so that others cannot change it at the same moment.
The classic example
CREATE TABLE accounts (id INT PRIMARY KEY, owner VARCHAR(30), balance NUMERIC(12,2) CHECK (balance >= 0));
INSERT INTO accounts VALUES (1,'Asha',5000),(2,'Ravi',1000);
BEGIN;
UPDATE accounts SET balance = balance - 1500 WHERE id = 1;
UPDATE accounts SET balance = balance + 1500 WHERE id = 2;
COMMIT;
Until COMMIT, other sessions do not see the change. If the second UPDATE failed, you would run ROLLBACK; and Asha would still have 5000.00. A CHECK (balance >= 0) constraint adds a second safety net: an attempt to take more than the balance fails and, in PostgreSQL, the whole transaction becomes unusable until you roll back.
BEGIN;
UPDATE accounts SET balance = balance - 9000 WHERE id = 1; -- violates the CHECK, error
ROLLBACK;
MySQL uses START TRANSACTION (or BEGIN) and requires InnoDB tables, the default storage engine in MySQL 8; the old MyISAM engine has no transactions.
Autocommit
By default each statement outside a BEGIN is its own transaction and commits immediately (autocommit). Many tools and drivers also run in autocommit mode unless told otherwise:
- JDBC:
connection.setAutoCommit(false)thencommit()orrollback(). - Python database drivers usually start a transaction automatically and need an explicit
connection.commit(). - Spring's
@Transactionaland ORMs manage this for you (the Spring course covers it).
A common bug is running the first UPDATE successfully, hitting an error on the second, and leaving the first one committed because autocommit was on.
Savepoints
A savepoint marks a point inside a transaction you can roll back to without losing earlier work:
BEGIN;
INSERT INTO accounts VALUES (3,'Meena',200);
SAVEPOINT before_risky;
UPDATE accounts SET balance = balance - 999 WHERE id = 3; -- fails the CHECK
ROLLBACK TO SAVEPOINT before_risky; -- Meena's insert survives
COMMIT;
Use them for "try this part, otherwise carry on", for example processing a file row by row.
Locks, waiting and deadlocks
When a transaction updates a row, it holds a lock on that row until it commits or rolls back. A second transaction that tries to update the same row waits. That is correct behaviour: it prevents two people overwriting each other.
A deadlock happens when two transactions wait for each other:
- Transaction A locks account 1, then wants account 2.
- Transaction B locks account 2, then wants account 1.
- Neither can continue. The database detects this and cancels one with an error ("deadlock detected" in PostgreSQL, error 1213 in MySQL). The application should catch it and retry.
Prevent deadlocks by always touching rows in the same order (for example lowest id first), and by keeping transactions short.
To read a row and be sure nobody changes it before you update it, lock it explicitly:
BEGIN;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE; -- others wait if they update id 1
-- decide in application code, then:
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;
FOR UPDATE works in PostgreSQL and in MySQL InnoDB.
Good habits
- Keep transactions short. Long ones hold locks, block others and make the database keep old row versions around.
- Never wait for a user (a form, a payment page) inside an open transaction.
- Do not call slow external services (an email, a payment gateway) while holding locks.
- Handle failure: always have a path that rolls back, and retry on deadlocks.
- Use the constraint, not only the code:
CHECK,UNIQUEand foreign keys protect your data even if a program has a bug. - Make the update itself atomic:
SET balance = balance - 100is safe, while "read the balance into the application, subtract, write it back" can lose updates when two requests overlap.
Common mistakes
- Autocommit left on when you needed several statements to succeed together.
- Forgetting to commit or roll back, leaving a connection "idle in transaction" that holds locks.
- Read-modify-write in application code without a lock or an atomic
UPDATE. - Opening a transaction, then waiting for user input.
- Ignoring deadlock errors instead of retrying.
Try it yourself
- Create
accounts, then run a transfer insideBEGIN ... COMMIT. - Start a transfer, run
ROLLBACK, and confirm the balances are unchanged. - Open two sessions. In the first run
BEGIN; UPDATE accounts SET balance = balance + 1 WHERE id = 1;and do not commit. In the second try the same update and watch it wait. Commit the first. - Cause a
CHECKviolation inside a transaction and see how your tool reports it.
Check yourself
What does a transaction guarantee about its statements?
They all take effect together (commit) or none do (rollback).
What happens to a second session that updates a row locked by an uncommitted transaction?
It waits until the first transaction commits or rolls back.
How do you avoid most deadlocks?
Access rows in a consistent order and keep transactions short; the application should also retry a transaction that the database cancels.
Why is UPDATE accounts SET balance = balance - 100 safer than reading the balance and writing a new value from code?
The update is applied atomically inside the database, so two overlapping requests cannot overwrite each other with stale numbers.
Interview questions
Explain commit, rollback and savepoint.
Commit makes a transaction's changes permanent. Rollback undoes all of them. A savepoint is a marker inside a transaction that lets you roll back only part of it.
What is a deadlock and how is it resolved?
Two transactions each hold a lock the other needs, so neither can proceed. The database detects the cycle and aborts one transaction; the application retries it. Consistent lock ordering prevents most of them.
What does SELECT ... FOR UPDATE do?
It reads rows and locks them so no other transaction can update or lock them until yours finishes, used for read-then-write logic.
Summary
- A transaction is an all-or-nothing group of statements:
BEGIN, thenCOMMITorROLLBACK. - Autocommit makes each statement its own transaction; turn it off when statements belong together.
- Locks make others wait; deadlocks are broken by aborting one transaction, so retry.
- Keep transactions short, make updates atomic and back them with constraints.
Going deeper
- Transactions tutorial: the basic idea with a bank example.
- Explicit locking: row locks and deadlocks.
- MySQL InnoDB transaction model: locking and isolation in MySQL.
- Savepoints:
SAVEPOINTreference.
See how much of this you can explain
A free mock interview asks you new questions on SQL for analysts and developers for Data Analyst and scores every answer, with what to fix.