SQL Transactions: ACID, COMMIT and ROLLBACK, Isolation Levels and Anomalies
Bob transfers 50 points to Carol, and the application runs two commands: UPDATE user_points SET points = points - 50 WHERE user_id = 2; and UPDATE user_points SET points = points + 50 WHERE user_id = 3;. Right after the first one, the application server restarts. In autocommit mode the first command is already saved and the second never ran: Bob lost 50 points, Carol received nothing, and the database has no idea that the two commands were meant to be one operation.
A transaction in SQL is a group of commands that the database executes as a single unit: either all changes are saved with COMMIT, or all of them are undone with ROLLBACK, and other users never see the state in between. The reliability of transactions is described by four ACID properties: atomicity, consistency, isolation and durability. In MySQL, transactions are supported by the InnoDB engine, which is the default.
Practice Data: Threads and User Points
The examples run in order on the forum practice database (MySQL 8, InnoDB): threads get a views counter, and users get points in a user_points table that can never go below zero. Every output below is calculated from this starting data.
Recreate the database with the script from The Forum Practice Database (users Alice, Bob and Carol, threads 1 and 2), then run:
ALTER TABLE topics ADD COLUMN views INT NOT NULL DEFAULT 0;
UPDATE topics SET views = 10 WHERE id = 1;
UPDATE topics SET views = 20 WHERE id = 2;
CREATE TABLE user_points (
user_id INT NOT NULL,
points INT NOT NULL DEFAULT 0,
PRIMARY KEY (user_id),
CONSTRAINT fk_points_user FOREIGN KEY (user_id)
REFERENCES users (id) ON DELETE CASCADE,
CONSTRAINT chk_points_non_negative CHECK (points >= 0)
);
INSERT INTO user_points (user_id, points) VALUES (1, 100), (2, 80), (3, 20);
SELECT user_id, points FROM user_points; +---------+--------+
| user_id | points |
+---------+--------+
| 1 | 100 |
| 2 | 80 |
| 3 | 20 |
+---------+--------+ Recreate the database even if you have just finished SQL INSERT, UPDATE and DELETE: that lesson added a third thread and a views column of its own, so the thread counts and view numbers below would not match. The CHECK constraint is explained in the lesson on CREATE TABLE and constraints.
Two sessions, two client windows
From the anomalies section on, the examples show two concurrent transactions. Open two terminal windows, run mysql -u root -p forum in each, and execute the commands strictly in the order of the steps in the table. One window is session A, the other is session B. In MySQL Workbench, two tabs with separate connections do the same job.
ACID Properties of a Transaction
ACID is an acronym for the four guarantees of a transaction: atomicity (all or nothing), consistency (the data stays valid), isolation (concurrent transactions do not interfere with each other) and durability (committed data survives a crash). InnoDB provides each property with a separate mechanism.
| Property | What it guarantees | How InnoDB provides it |
|---|---|---|
| Atomicity | Either every command of the transaction runs or none does: a points transfer is never left half done | The undo log keeps previous versions of rows; ROLLBACK and crash recovery use it to cancel uncommitted changes |
| Consistency | A transaction moves the database from one valid state to another: no constraint is violated | Checks of PRIMARY KEY, UNIQUE, FOREIGN KEY, NOT NULL and CHECK on every command; business rules (a transfer does not change the total of points) are up to the application |
| Isolation | Concurrent transactions see each other only as far as the isolation level allows | Multi-versioning (MVCC) for a plain SELECT and row locks for writes and locking reads |
| Durability | After COMMIT, changes survive a server crash or a power failure | The redo log is written to disk on commit; with innodb_flush_log_at_trx_commit = 1 (the default) on every COMMIT, while with 0 or 2 a crash can cost the last second of transactions |
Consistency is the odd one out. The database checks only the constraints you declared; it knows nothing about the rule “whatever is taken from Bob must arrive at Carol”. The application enforces that rule, and atomicity and isolation are the tools it uses. Interviewers like this detail: “which ACID property is not guaranteed by the DBMS alone?”
START TRANSACTION, COMMIT, ROLLBACK and SAVEPOINT
START TRANSACTION (or its synonym BEGIN) opens a transaction, COMMIT saves it and ROLLBACK undoes it. Without an explicit start, MySQL works in autocommit mode (autocommit = 1): every command is a separate transaction that is committed as soon as it finishes. Savepoints (SAVEPOINT) let you undo only part of a transaction.
| Command | What it does |
|---|---|
START TRANSACTION / BEGIN | Opens a transaction; autocommit is suspended until it ends |
COMMIT | Saves all changes of the transaction and makes them visible to other sessions |
ROLLBACK | Undoes all changes of the transaction |
SAVEPOINT name | Sets a savepoint inside the transaction |
ROLLBACK TO SAVEPOINT name | Undoes the changes made after the savepoint; the transaction continues |
RELEASE SAVEPOINT name | Removes the savepoint, the changes stay |
SET autocommit = 0 | Every following command starts a transaction that must be closed with COMMIT or ROLLBACK |
To check the mode of the current session:
SELECT @@autocommit; +--------------+
| @@autocommit |
+--------------+
| 1 |
+--------------+ A Points Transfer Inside a Transaction
The same transfer as at the top of the lesson, now inside a transaction. Until COMMIT runs, other sessions still see 80 points for Bob and 20 for Carol:
START TRANSACTION;
UPDATE user_points SET points = points - 50 WHERE user_id = 2;
UPDATE user_points SET points = points + 50 WHERE user_id = 3;
COMMIT;
SELECT user_id, points FROM user_points; Query OK, 0 rows affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
Query OK, 0 rows affected (0.01 sec)
+---------+--------+
| user_id | points |
+---------+--------+
| 1 | 100 |
| 2 | 30 |
| 3 | 70 |
+---------+--------+ Had the server crashed between the two UPDATEs, InnoDB would have rolled back the uncommitted debit from the undo log on restart, and Bob would have 80 again.
ROLLBACK: Undo Everything
A command that wipes everyone’s points is harmless inside a transaction until you commit:
START TRANSACTION;
UPDATE user_points SET points = 0;
SELECT SUM(points) FROM user_points;
ROLLBACK;
SELECT SUM(points) FROM user_points; Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0
+-------------+
| SUM(points) |
+-------------+
| 0 |
+-------------+
Query OK, 0 rows affected (0.00 sec)
+-------------+
| SUM(points) |
+-------------+
| 200 |
+-------------+ Inside the transaction the session sees its own uncommitted changes (a total of 0); after the rollback it sees the original 200 again. How to use this when you run UPDATE and DELETE by hand is shown in SQL DELETE: Removing Rows.
SAVEPOINT: Rolling Back Part of a Transaction
Give Alice 10 bonus points, then by mistake give 10 to Bob and Carol as well, and undo only the second command:
START TRANSACTION;
UPDATE user_points SET points = points + 10 WHERE user_id = 1;
SAVEPOINT bonus_alice;
UPDATE user_points SET points = points + 10 WHERE user_id IN (2, 3);
ROLLBACK TO SAVEPOINT bonus_alice;
COMMIT;
SELECT user_id, points FROM user_points; +---------+--------+
| user_id | points |
+---------+--------+
| 1 | 110 |
| 2 | 30 |
| 3 | 70 |
+---------+--------+ ROLLBACK TO SAVEPOINT does not close the transaction: it still needs a COMMIT or a full ROLLBACK. Savepoints are useful in long processing jobs where the failure of one step should not throw away the work already done.
Implicit COMMIT
Some commands commit an open transaction on their own. These include all DDL commands (CREATE TABLE, ALTER TABLE, DROP, TRUNCATE) — see DDL Commands in SQL. A second START TRANSACTION also commits implicitly: MySQL has no nested transactions, so the new command first saves everything done in the previous one. SET autocommit = 1 does the same if a transaction was open.
An Error Does Not Roll Back the Transaction
When a command inside a transaction fails, MySQL cancels only that command. Everything done before it stays in the transaction. Transfer 100 points from Bob (who has 30) to Carol, starting with the credit:
START TRANSACTION;
UPDATE user_points SET points = points + 100 WHERE user_id = 3;
UPDATE user_points SET points = points - 100 WHERE user_id = 2;
SELECT user_id, points FROM user_points; Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
ERROR 3819 (HY000): Check constraint 'chk_points_non_negative' is violated.
+---------+--------+
| user_id | points |
+---------+--------+
| 1 | 110 |
| 2 | 30 |
| 3 | 170 |
+---------+--------+ The debit was rejected, but the credit stayed: Carol has 170. The transaction is still open, and a COMMIT right now would save 100 points that came from nowhere. The correct reaction to an error is ROLLBACK:
ROLLBACK; Rolling back after an error is the application’s job
MySQL rolls back the whole transaction by itself in only one common case — a deadlock (error 1213, see MySQL Deadlocks). A constraint violation, a syntax error or a lock wait timeout (1205) cancels a single statement. That is why Java code calls rollback() in the catch block — see JDBC Transactions.
Transactions work only with InnoDB tables. Changes to a MyISAM table are written immediately; ROLLBACK will not bring them back and only raises warning 1196 Some non-transactional changed tables couldn't be rolled back.
Concurrency Anomalies: Lost Update, Dirty, Non-Repeatable and Phantom Reads
Concurrency anomalies are errors that appear when two transactions read and change the same data at the same time. There are four classic kinds: the lost update, the dirty read, the non-repeatable read and the phantom read. Which of them can happen is decided by the transaction isolation level.
Current state: thread 1 has 10 views, thread 2 has 20; the points are 110, 30 and 70. The examples below run step by step in two sessions.
Lost Update
Two transactions read the same value, each calculates a new one on its side and writes it back — the second write overwrites the first. That is exactly what code like “read views into Java, add 1, write it back” does. Both sessions use the default level, REPEATABLE READ:
| Step | Session A | Session B |
|---|---|---|
| 1 | START TRANSACTION;SELECT views FROM topics WHERE id = 1; → 10 | |
| 2 | START TRANSACTION;SELECT views FROM topics WHERE id = 1; → 10 | |
| 3 | UPDATE topics SET views = 11 WHERE id = 1; → Changed: 1 | |
| 4 | UPDATE topics SET views = 11 WHERE id = 1; → waits for the row lock | |
| 5 | COMMIT; | The command of step 4 completes: Rows matched: 1 Changed: 0 |
| 6 | COMMIT; → the database holds views = 11, although there were two views |
The row lock made B wait, but did not save the day: B writes a value calculated from a stale read. Changed: 0 at step 5 means MySQL saw that the row already contains 11. In PostgreSQL, at the REPEATABLE READ level, session B would get the error could not serialize access due to concurrent update; InnoDB does not perform such a check. There are two fixes: an atomic UPDATE topics SET views = views + 1 WHERE id = 1 (see SQL UPDATE) or a locking read with SELECT ... FOR UPDATE from the section SELECT ... FOR UPDATE and FOR SHARE.
Dirty Read
A transaction reads changes of another transaction that are not committed yet and may still be rolled back. This is possible only at the READ UNCOMMITTED level, so session B switches to it first:
| Step | Session A | Session B |
|---|---|---|
| 1 | SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; | |
| 2 | START TRANSACTION;UPDATE user_points SET points = points + 50 WHERE user_id = 2; | |
| 3 | SELECT points FROM user_points WHERE user_id = 2; → 80 | |
| 4 | ROLLBACK; | |
| 5 | SELECT points FROM user_points WHERE user_id = 2; → 30 |
At step 3, B saw 80 points that Bob never had. If a purchase for 60 points were approved based on that value, the points would be spent out of thin air. At any level above READ UNCOMMITTED, step 3 would return 30.
Non-Repeatable Read
A transaction reads the same row twice and gets different values, because another transaction changed the row and committed between the two reads. Session B now works at READ COMMITTED:
| Step | Session A | Session B |
|---|---|---|
| 1 | SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;START TRANSACTION;SELECT views FROM topics WHERE id = 2; → 20 | |
| 2 | UPDATE topics SET views = views + 1 WHERE id = 2; (autocommit) | |
| 3 | SELECT views FROM topics WHERE id = 2; → 21 | |
| 4 | COMMIT; |
A report that counted one number at step 1 and another at step 3 contradicts itself within a single transaction. At REPEATABLE READ, step 3 would return 20.
Phantom Read
A transaction runs a query with the same condition twice and gets a different set of rows: another transaction inserted (or deleted) a row that matches the condition. The difference from a non-repeatable read is that the value of a known row does not change — the set of rows does. Session B is still at READ COMMITTED:
| Step | Session A | Session B |
|---|---|---|
| 1 | START TRANSACTION;SELECT COUNT(*) FROM topics WHERE author_id = 1; → 1 | |
| 2 | INSERT INTO topics (title, author_id) VALUES ('Ice fishing', 1); | |
| 3 | SELECT COUNT(*) FROM topics WHERE author_id = 1; → 2 | |
| 4 | COMMIT;SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; |
The new thread got id = 3 and appeared in B’s result as a “phantom”. In InnoDB at REPEATABLE READ, step 3 would return 1 — the next section explains why. The last command of step 4 returns session B to the default level.
Transaction Isolation Levels
The isolation level defines which changes of concurrent transactions the current transaction can see. The SQL standard defines four levels, from the weakest to the strictest: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ and SERIALIZABLE. The stricter the level, the fewer anomalies and the more lock waits. MySQL with InnoDB uses REPEATABLE READ by default; PostgreSQL, Oracle and SQL Server use READ COMMITTED.
| Level | Dirty read | Non-repeatable read | Phantom read | Lost update in InnoDB |
|---|---|---|---|---|
READ UNCOMMITTED | Possible | Possible | Possible | Possible |
READ COMMITTED | No | Possible | Possible | Possible |
REPEATABLE READ (MySQL default) | No | No | Possible by the standard; in InnoDB a plain SELECT does not see phantoms | Possible |
SERIALIZABLE | No | No | No | No: one of the transactions gets a deadlock and is rolled back |
The “lost update” column refers to the “read, calculate, write” scenario from Lost Update: in InnoDB it is prevented only by SERIALIZABLE or by an explicit FOR UPDATE lock.
How to Check and Change the Isolation Level
SELECT @@transaction_isolation; +-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ |
+-------------------------+ -- only for the next transaction of this session
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- for all following transactions of the session
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- for new sessions on the server (requires a privilege)
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED; You cannot change the level in the middle of a transaction: SET TRANSACTION inside one fails with ERROR 1568 (25001): Transaction characteristics can't be changed while a transaction is in progress. Older articles use the tx_isolation variable — it was removed in MySQL 8.0 in favour of transaction_isolation; in the server configuration the level is set with the transaction-isolation option.
MVCC and Consistent Reads in InnoDB
A plain SELECT in InnoDB sets no locks and never waits for other transactions. It reads a snapshot (a consistent read): the row versions committed at the moment the snapshot was created, with older versions taken from the undo log. This mechanism is called MVCC (multi-version concurrency control).
The levels differ in when the snapshot is created. In READ COMMITTED it is created anew for every SELECT, so the second query sees fresh changes. In REPEATABLE READ it is created at the first read of the transaction — not at START TRANSACTION, unless you use START TRANSACTION WITH CONSISTENT SNAPSHOT, which creates it immediately — and every later SELECT of the transaction reads the same snapshot. That is why in REPEATABLE READ you see neither changed nor new rows of other transactions, and a plain SELECT gets no phantoms. In SERIALIZABLE, when autocommit is off, InnoDB turns every plain SELECT into SELECT ... FOR SHARE.
Write commands do not use the snapshot: UPDATE, DELETE and locking reads work with the latest committed version of the rows. This leads to the following scenario in REPEATABLE READ. What will step 5 print?
| Step | Session A | Session B (REPEATABLE READ) |
|---|---|---|
| 1 | START TRANSACTION;SELECT COUNT(*) FROM topics WHERE author_id = 1; → 2 | |
| 2 | INSERT INTO topics (title, author_id) VALUES ('Winter fishing', 1); | |
| 3 | SELECT COUNT(*) FROM topics WHERE author_id = 1; → 2 | |
| 4 | UPDATE topics SET views = views + 1 WHERE author_id = 1; → Rows matched: 3 Changed: 3 | |
| 5 | SELECT COUNT(*) FROM topics WHERE author_id = 1; → 3 | |
| 6 | ROLLBACK; |
At step 3 the snapshot hides thread 4, “Winter fishing”, but the UPDATE at step 4 finds all three of Alice’s threads and changes them. InnoDB shows rows changed by your own transaction in the snapshot, so at step 5 the phantom appears. The phantom protection of REPEATABLE READ covers reads, not writes. Step 6 rolls back the view increments; thread 4 stays in the database.
SELECT ... FOR UPDATE and FOR SHARE
A locking read is a SELECT that not only reads rows but also locks them until the end of the transaction. SELECT ... FOR UPDATE sets an exclusive lock: other transactions can neither change these rows nor lock them. SELECT ... FOR SHARE sets a shared lock: everyone can read the rows with FOR SHARE, nobody can change them. A plain SELECT in another transaction is not blocked by either lock.
| Read | Lock | What another transaction cannot do | When to use it |
|---|---|---|---|
SELECT | None (MVCC snapshot) | Nothing is blocked | Ordinary reads |
SELECT ... FOR SHARE | Shared (S) | UPDATE, DELETE, FOR UPDATE of these rows | Make sure a row exists and will not change while you use it: for example, a thread while a message is being added to it |
SELECT ... FOR UPDATE | Exclusive (X) | UPDATE, DELETE, FOR UPDATE, FOR SHARE of these rows | Read a value in order to change it afterwards |
FOR SHARE appeared in MySQL 8.0; the old syntax LOCK IN SHARE MODE still works and shows up in older code. Let’s replay the lost update scenario with a lock. Thread 1 now has 11 views:
| Step | Session A | Session B |
|---|---|---|
| 1 | START TRANSACTION;SELECT views FROM topics WHERE id = 1 FOR UPDATE; → 11 | |
| 2 | START TRANSACTION;SELECT views FROM topics WHERE id = 1 FOR UPDATE; → waits | |
| 3 | UPDATE topics SET views = 12 WHERE id = 1;COMMIT; | The read of step 2 completes → 12 |
| 4 | UPDATE topics SET views = 13 WHERE id = 1;COMMIT; → the database holds 13 |
B waits on the read, not on the write, and gets the already committed value 12: a locking read takes the latest version of the row even in REPEATABLE READ, not the snapshot. For a simple counter the lock is overkill — UPDATE topics SET views = views + 1 does the job in one command. FOR UPDATE is needed when there is application logic between the read and the write: check the points balance, then debit it.
If waiting is not an option, MySQL 8.0 offers FOR UPDATE NOWAIT, which fails immediately with error 3572 instead of waiting, and FOR UPDATE SKIP LOCKED, which skips locked rows (this is how several workers take jobs from a queue table).
FOR UPDATE outside a transaction holds no lock
In autocommit mode, SELECT ... FOR UPDATE is a transaction of its own: the lock is released right after the read, and the following UPDATE is not protected by anything. A locking read makes sense only after START TRANSACTION or setAutoCommit(false).
Which Rows Are Actually Locked
InnoDB locks not the rows of the result but the index records the query scanned. In REPEATABLE READ it adds locks on the gaps between those records — together they are called next-key locks. After SELECT * FROM topics WHERE author_id = 1 FOR UPDATE, another session can neither change Alice’s threads nor insert a new thread with author_id = 1: its place in the fk_topics_author index falls into a locked gap. This is how InnoDB protects locking reads from phantoms. In READ COMMITTED gap locks are almost never used — fewer waits, but phantoms are possible.
Here is the main catch: if the condition of a FOR UPDATE or an UPDATE cannot use an index, InnoDB scans and locks every row of the table, and one transaction stalls all the others. Why a query does not use an index and how to check it with EXPLAIN is covered in Why MySQL Is Not Using Your Index.
MySQL Deadlocks and Lock Wait Timeouts
A deadlock is a situation where two transactions wait for each other: each one holds a lock that the other needs. InnoDB detects a deadlock immediately, rolls back one of the transactions completely with error 1213 and lets the other continue. An ordinary wait without a cycle is limited by innodb_lock_wait_timeout and ends with error 1205.
Alice (110 points) and Carol (70) send each other 10 points at the same time, and each transfer starts with its own debit:
| Step | Session A (Alice → Carol) | Session B (Carol → Alice) |
|---|---|---|
| 1 | START TRANSACTION;UPDATE user_points SET points = points - 10 WHERE user_id = 1; | |
| 2 | START TRANSACTION;UPDATE user_points SET points = points - 10 WHERE user_id = 3; | |
| 3 | UPDATE user_points SET points = points + 10 WHERE user_id = 3; → waits for B | |
| 4 | UPDATE user_points SET points = points + 10 WHERE user_id = 1; → ERROR 1213, transaction B is rolled back | |
| 5 | The command of step 3 completesCOMMIT; |
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction Transfer A went through (the points are now 100, 30 and 80), and transfer B was cancelled entirely, including the debit at step 2. InnoDB chooses the victim itself — usually the smaller transaction, the one that inserted, updated or deleted fewer rows — so in your run session A may get the error instead. SHOW ENGINE INNODB STATUS shows the details of the last deadlock in the LATEST DETECTED DEADLOCK section.
| Error | When it happens | What is rolled back | What to do |
|---|---|---|---|
ERROR 1213 (40001): Deadlock found when trying to get lock | Transactions wait for each other in a cycle | The whole transaction | Retry the transaction from the start |
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction | A lock is held longer than innodb_lock_wait_timeout (50 seconds by default) | Only the statement, unless innodb_rollback_on_timeout is enabled | Run ROLLBACK and retry; find the long transaction that holds the lock |
You can shorten the timeout for a session: SET SESSION innodb_lock_wait_timeout = 5;. Deadlocks cannot be ruled out completely, but they can be made rare. Keep transactions short: no HTTP calls and no waiting for the user between START TRANSACTION and COMMIT. Take locks in the same order everywhere — in a points transfer, always update the row with the smaller user_id first. In the example above, B would then wait at step 2 instead of running into a deadlock. And the code has to be able to retry a transaction that got 1213.
JDBC Transactions: setAutoCommit, commit and rollback
In JDBC a connection works in autocommit mode by default. To run several commands in one transaction, call connection.setAutoCommit(false), then commit() after all the commands, and rollback() in the catch block if an exception is thrown. The isolation level is set with setTransactionIsolation(), savepoints with setSavepoint().
The example assumes you know the JDBC basics: opening a connection with DriverManager and running queries through PreparedStatement. The points transfer (the balances are now 100, 30 and 80):
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class PointsTransfer {
private static final String URL = "jdbc:mysql://localhost:3306/forum";
public static void main(String[] args) throws SQLException {
try (Connection conn = DriverManager.getConnection(URL, "root", "password")) {
conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
transfer(conn, 1, 3, 30); // Alice -> Carol
transfer(conn, 2, 3, 500); // Bob has only 30 points
}
}
static void transfer(Connection conn, int fromId, int toId, int amount) throws SQLException {
conn.setAutoCommit(false);
try (PreparedStatement debit = conn.prepareStatement(
"UPDATE user_points SET points = points - ? WHERE user_id = ?");
PreparedStatement credit = conn.prepareStatement(
"UPDATE user_points SET points = points + ? WHERE user_id = ?")) {
debit.setInt(1, amount);
debit.setInt(2, fromId);
debit.executeUpdate();
credit.setInt(1, amount);
credit.setInt(2, toId);
credit.executeUpdate();
conn.commit();
System.out.println("Transferred " + amount + " points: " + fromId + " -> " + toId);
} catch (SQLException e) {
conn.rollback();
System.out.println("Rolled back: " + e.getMessage());
} finally {
conn.setAutoCommit(true);
}
}
} Transferred 30 points: 1 -> 3
Rolled back: Check constraint 'chk_points_non_negative' is violated. After the run the table holds 70, 30 and 110: the first transfer is committed, the second is rolled back — the debit from Bob violated the CHECK, and the credit was never reached. Without rollback() in catch, the open transaction would stay on the connection and be committed by the next commit() together with unrelated changes (see An Error Does Not Roll Back the Transaction). setAutoCommit(true) in finally returns the connection to its normal mode, which matters when it goes back to a pool and is handed to other code.
A JDBC savepoint works the same way as SAVEPOINT in SQL:
conn.setAutoCommit(false);
// ... bonus for Alice
Savepoint afterBonus = conn.setSavepoint("bonus_alice"); // java.sql.Savepoint
// ... bonus for everyone else
conn.rollback(afterBonus); // undo only the bonus for everyone else
conn.commit(); On a deadlock, MySQL Connector/J throws SQLTransactionRollbackException whose getSQLState() returns "40001"; the code uses it to decide whether to retry the transaction. In Spring applications the same calls are hidden behind the @Transactional annotation: the framework turns off autocommit, commits when the method returns normally and rolls back when it throws an exception.
Transaction Gotchas in MySQL and JDBC
Most transaction mistakes produce no error message: data is quietly committed at the wrong moment, a transaction holds locks longer than needed, or a “protection” does not protect anything. These are the situations developers most often trip over with their first transactions in MySQL and JDBC.
| Situation | What happens | How to do it right |
|---|---|---|
COMMIT after a failed command in the transaction | The commands before the error are saved, and the operation is stored half done | After any error — ROLLBACK; in Java — rollback() in catch |
setAutoCommit(false) without commit() | When the connection closes, MySQL rolls back the uncommitted transaction, and the changes are lost | Every code path ends with commit() or rollback() |
Read in Java, calculate, write back (views = 11) | Lost update, even in REPEATABLE READ | An atomic UPDATE ... SET views = views + 1 or SELECT ... FOR UPDATE |
SELECT ... FOR UPDATE in autocommit mode | The lock is released right after the read | Open a transaction before the locking read |
ALTER TABLE or CREATE INDEX in the middle of a transaction | Implicit COMMIT: everything done so far is saved, and ROLLBACK no longer helps | Run DDL outside data transactions |
| A long transaction: an external HTTP call or waiting for the user inside it | Other sessions get ERROR 1205, the undo log grows | Prepare the data before START TRANSACTION; inside it, only SQL |
FOR UPDATE or UPDATE with a condition on a column without an index | Every row of the table is locked | An index on the condition column, checked with EXPLAIN |
No retry on ERROR 1213 | The user gets an error because of an accidental timing overlap | Retry the transaction 2–3 times on SQLState 40001 |
| A table on the MyISAM engine | ROLLBACK undoes nothing, only warning 1196 | InnoDB (the default in MySQL 8) |
Frequently Asked Questions
Do I need a transaction for a single UPDATE or INSERT?
No. In autocommit mode every command is a transaction of its own, and InnoDB executes it atomically: a multi-row UPDATE or INSERT that fails changes no rows at all. You need an explicit transaction when several commands must succeed or fail together, or when a read and the write that follows it must see the same data.
Which isolation level should a Java application on MySQL use?
The default, REPEATABLE READ, is usually enough, and the places where concurrent writes must stay correct are protected with SELECT ... FOR UPDATE or an atomic UPDATE. Many projects switch MySQL to READ COMMITTED: without gap locks there are fewer waits and deadlocks, and the behaviour matches PostgreSQL. Keep in mind that with the binary log enabled, READ COMMITTED requires row-based logging (binlog_format = ROW), which is already the default in MySQL 8.
What happens to a transaction if the connection drops?
The MySQL server rolls back the uncommitted transaction of the broken connection and releases its locks. There is no limit on how long a transaction may stay open, so a hung connection with an open transaction can hold locks for hours. The information_schema.INNODB_TRX table (the trx_started column) helps you find such transactions.
How do transactions in PostgreSQL differ from MySQL?
PostgreSQL uses READ COMMITTED by default. DDL in PostgreSQL is transactional and can be rolled back. After an error inside a transaction, PostgreSQL rejects every following command (current transaction is aborted) until ROLLBACK, while MySQL lets the transaction continue. At REPEATABLE READ, PostgreSQL raises a serialization error instead of allowing a lost update, and the application has to retry.
How do I see fresh data inside a REPEATABLE READ transaction?
A plain SELECT reads the snapshot created at the first read and will not show newer changes. The locking reads SELECT ... FOR SHARE and FOR UPDATE return the latest committed version of the row; the other way is to finish the transaction and start a new one. How the snapshot is created is explained in MVCC and Consistent Reads.
Comments