Transactions and locking in depth series: 1. Transactions from the inside · 2. Harmful transactions · 3. Locks in InnoDB · 4. Optimistic, pessimistic and the booking lab
Part 1 showed that a long-open transaction hurts because it holds locks and a snapshot: InnoDB can’t purge the undo log (history list length grows, reads slow down) and PostgreSQL can’t vacuum dead tuples. This part is the practical side: recognizing, finding and fixing those transactions. Afterwards you can find each kind and know where to put timeouts.
At a glance:
flowchart LR
A["Harmful transactions"] --> B["Too large"]
A --> C["Long-running"]
A --> D["Idle mid-way"]
A --> E["Abandoned"]
B --> B2["Batch by primary key"]
C --> C2["Root cause: index, N+1, lock"]
D --> D2["Move HTTP outside"]
E --> E2["Fix connection leak"]
B2 & C2 & D2 & E2 --> F["innodb_trx, pg_stat_activity"]
F --> G["Timeouts per layer, per user"]
1. The four kinds of harmful transaction
Key idea: each kind has its own cause and its own cure, so name it correctly before fixing it.
| Kind | What it looks like | Usual cause |
|---|---|---|
| Too large | Changes too many rows | One UPDATE/DELETE over a whole table |
| Long-running | Too long from BEGIN to COMMIT |
Slow statements, too many statements, lock waits |
| Stalled | Sits idle between statements | The app calls HTTP or does heavy work inside the transaction |
| Abandoned | The client is gone, the transaction is still open | Connection leaks, half-closed connections |
The queries in this post were tested on MySQL 8.4.11 and PostgreSQL 16.14 (Docker).
1.1. Too large
Key idea: a transaction that changes millions of rows locks a lot, makes replicas lag and takes long to roll back. It can almost always be batched.
Symptom: for example UPDATE orders SET status = 'ARCHIVED' WHERE created_at < '2024-01-01'. It locks a huge number of rows (plus gap locks at RR, which lock the gaps between rows too) and produces a lot of undo and redo. Replicas lag because the whole transaction is applied in one go on the replica.
Find it: information_schema.innodb_trx has trx_rows_modified and trx_rows_locked; performance_schema.events_statements_history tells you which statement. On PostgreSQL, use pg_stat_activity together with pg_stat_user_tables.n_tup_upd.
Fix it: the slides’ first question is the right one: why does it need to touch that many rows in one transaction? Usually it doesn’t. Batch by primary key:
-- Loop from the application or a job until affected rows = 0
UPDATE orders
SET status = 'ARCHIVED'
WHERE created_at < '2024-01-01' AND status <> 'ARCHIVED'
ORDER BY id
LIMIT 5000;
UPDATE ... ORDER BY ... LIMITis MySQL syntax. On PostgreSQL writeWHERE id IN (SELECT id FROM orders WHERE ... ORDER BY id LIMIT 5000).- A few thousand rows per batch, with a short pause between batches so replicas keep up. The post on deleting old data safely covers this pattern in detail for
DELETE. - The price: you lose atomicity for the whole operation, so each batch must be safe to rerun (the
status <> 'ARCHIVED'condition takes care of that).
1.2. Long-running
Key idea: a transaction takes seconds or minutes because of slow statements (missing indexes), too many statements (an N+1 loop inside @Transactional), or lock waits. Find the root cause first, then set time limits.
Find it:
-- MySQL: the oldest open transactions and what they're doing
SELECT trx_mysql_thread_id, trx_state,
TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS age_s,
trx_rows_locked, trx_rows_modified, trx_query
FROM information_schema.innodb_trx
ORDER BY trx_started
LIMIT 5;
-- Who is blocking whom (a sys schema view)
SELECT waiting_query, blocking_pid, blocking_query, wait_age
FROM sys.innodb_lock_waits;
Fix it: the slides rightly hint that it’s often a lock wait. Add indexes, move work that doesn’t need to be atomic out of the transaction, cut the number of statements. Set limits: @Transactional(timeout = 5) in Spring, innodb_lock_wait_timeout (50 seconds by default, far too long for a web request), and lock_timeout plus statement_timeout on PostgreSQL.
1.3. Stalled
Key idea: every SQL statement is fast, but between statements the application does something else. Locks and the read view stay held the whole time; the cure is to move the outside work out of the transaction.
Symptom: the application calls HTTP, renders a PDF, waits for a user. The database sees an open transaction running nothing: MySQL shows command = 'Sleep' in the processlist, PostgreSQL shows state = 'idle in transaction'.
@Transactional
public void checkout(long orderId) {
Order order = orders.findByIdForUpdate(orderId); // locks the order row
PaymentResult r = paymentClient.charge(order); // 2-second HTTP call, lock still held
order.markPaid(r.transactionId());
}
Fix it: the slides’ question, “do these statements need to be in one transaction?”, usually has the answer no.
- Make external calls before or after the transaction; wrap the transaction only around the database reads and writes.
- When “write to the DB and publish an event” must happen together, use a transactional outbox (see message brokers).
- The slides suggest switching to READ COMMITTED to avoid gap locks: that reduces how many locks are held, but doesn’t cure the real disease, a transaction standing still.
Safety nets: PostgreSQL has idle_in_transaction_session_timeout, which cuts off idle transactions automatically; from PostgreSQL 17 there’s also transaction_timeout, which caps the total duration. MySQL has no equivalent; wait_timeout (8 hours by default) only closes idle connections and is far too long to be a safety net.
Trying idle_in_transaction_session_timeout on PostgreSQL 16
I set idle_in_transaction_session_timeout = '2s', ran BEGIN and one statement, then left the session idle for 4 seconds:
FATAL: terminating connection due to idle-in-transaction timeout
1.4. Abandoned
Key idea: the client has disappeared but the transaction is still open, usually because of a connection leak. Fix it in the application; database timeouts are only a safety net.
Symptom: two causes. A connection leak: borrow a connection from the pool, open a transaction, hit an exception on a path that never closes it. More rarely, a half-closed connection: the client died but TCP hasn’t told the server yet.
Find it: the slides suggest “generate a report to find abandoned transactions”. On MySQL 8.4: list open transactions whose connection is in Sleep, with the last statement they ran so you can trace the code.
The abandoned-transaction report query on MySQL 8.4 and a test result
SELECT t.trx_mysql_thread_id AS conn_id, p.user, p.host,
p.time AS idle_s,
TIMESTAMPDIFF(SECOND, t.trx_started, NOW()) AS trx_age_s,
t.trx_rows_locked, t.trx_rows_modified,
s.sql_text AS last_statement
FROM information_schema.innodb_trx t
JOIN information_schema.processlist p ON p.id = t.trx_mysql_thread_id
JOIN performance_schema.threads th ON th.processlist_id = t.trx_mysql_thread_id
LEFT JOIN performance_schema.events_statements_current s ON s.thread_id = th.thread_id
WHERE p.command = 'Sleep'
AND TIMESTAMPDIFF(SECOND, t.trx_started, NOW()) > 10
ORDER BY t.trx_started;
The result of a test session that ran an UPDATE and then went quiet:
conn_id | idle_s | trx_age_s | trx_rows_locked | last_statement
336 | 3 | 3 | 1 | UPDATE account_balance SET balance=balance-1 WHERE account_number=1
Fix it: use try-with-resources or let Spring manage transactions. Turn on leakDetectionThreshold (say 30 seconds) so HikariCP logs the stack trace of whoever borrowed a connection and didn’t return it. The last resort is KILL <conn_id>, but InnoDB will roll back all of its changes, which can take time.
Where to put timeouts: there’s no single knob. Application, pool, database and monitoring each catch one kind of failure; only monitoring catches every kind, and it doesn’t stop anything.
Timeouts at each layer: the exact settings and setting them per user
2. Details people get wrong
Key idea: SET TRANSACTION without SESSION applies to one transaction only, autocommit is on by default, and transactions don’t replace locks.
How far SET TRANSACTION reaches on MySQL, PostgreSQL and with a connection pool
- Autocommit is on by default in MySQL (and by default in JDBC): every statement is its own transaction.
START TRANSACTION/BEGINsuspends it untilCOMMIT/ROLLBACK. On MySQL, DDL such asALTER TABLEcauses an implicit commit of the open transaction. - Transactions and locking solve two different problems. Transactions handle atomicity and how a unit of work sees data (isolation); locks handle ordering when several units change the same thing. A transaction at RR can still sell stock below zero if it reads from the snapshot and writes back the value it read. Locking is covered in part 3 and part 4.
Exercises
Exercise 1: A report of abandoned transactions
Task: write a query that lists open transactions whose client is doing nothing, with enough information to trace the code.
Solution to Exercise 1: the PostgreSQL version and pointing at the leaking service
The query in section 1.4 covers MySQL. The PostgreSQL version:
SELECT pid, usename, application_name, client_addr,
now() - xact_start AS trx_age,
now() - state_change AS idle_for,
left(query, 80) AS last_statement
FROM pg_stat_activity
WHERE state IN ('idle in transaction', 'idle in transaction (aborted)')
AND now() - state_change > interval '10 seconds'
ORDER BY xact_start;
Give each service its own application_name (the ApplicationName JDBC property on PostgreSQL, connectionAttributes on MySQL) and the report points straight at the leaking service. Run the query periodically and export the row count as a metric to alert on.
Exercise 2: Breaking up a large transaction
Task (my own addition): an orders table with 20 million rows; every order before 2024 must become ARCHIVED without making replicas lag or locking the table.
Solution to Exercise 2: a batching loop over the primary key
A loop in the application, one short transaction per batch, walking the primary key so each batch locks only the rows it changes:
long lastId = 0;
while (true) {
long from = lastId;
List<Long> ids = jdbc.queryForList(
"SELECT id FROM orders WHERE id > ? AND created_at < '2024-01-01' AND status <> 'ARCHIVED' ORDER BY id LIMIT 5000",
Long.class, from);
if (ids.isEmpty()) break;
tx.executeWithoutResult(s -> jdbc.update(
"UPDATE orders SET status = 'ARCHIVED' WHERE id BETWEEN ? AND ? AND created_at < '2024-01-01' AND status <> 'ARCHIVED'",
ids.getFirst(), ids.getLast()));
lastId = ids.getLast();
Thread.sleep(50); // give replicas and other requests room
}
- The condition is repeated in the
UPDATEbecause rows may have changed between reading the ids and updating them. - Batching by a contiguous id range (
BETWEEN) gives each transaction a tight lock footprint. - Watch replica lag and shrink the batch size automatically when it grows.
This snippet uses Spring’s JdbcTemplate/TransactionTemplate and wasn’t run; the same batching loop was run in the post on deleting old data.
Remember
- Name the four kinds of harmful transaction and how to fix each.
- Explain why an HTTP call inside
@Transactionaldrains the pool, and how to split it out. - Know which queries find long-open transactions, who blocks whom, and abandoned transactions on both MySQL and PostgreSQL.
- Explain why a leaked connection keeps a transaction open for 8 hours, and which tool catches it.
- List the timeouts at the four layers and why they should be set per user.
Interview questions
Junior
What is autocommit, and why does it matter that it’s on by default?
With autocommit on, every statement is its own transaction and commits immediately; MySQL and JDBC enable it by default.
To make several statements one unit you need START TRANSACTION/BEGIN (or setAutoCommit(false), or @Transactional). Forgetting this leads people to believe two UPDATEs are atomic together when they aren’t.
Can a transaction with only SELECT statements do any harm?
Yes. At REPEATABLE READ it holds a snapshot, which blocks purge (InnoDB) and VACUUM (PostgreSQL).
While it’s open, InnoDB can’t purge the undo log and PostgreSQL can’t vacuum dead tuples newer than that snapshot. If it’s SELECT ... FOR UPDATE, it also holds locks. So read-only transactions must be short too, and long reports belong on a replica.
Mid
What is an “idle in transaction” session and why is it dangerous?
It’s an open transaction whose client sends nothing; it still holds locks and a snapshot.
It usually happens because the application does other work between statements (an HTTP call) or leaked the connection. It blocks other writers and blocks VACUUM (PostgreSQL) or purge (InnoDB). Find it with pg_stat_activity.state, or innodb_trx plus a Sleep processlist entry; stop it with idle_in_transaction_session_timeout and by fixing the code.
How do you fix a million-row UPDATE that is making replicas lag?
Batch it by primary key, a few thousand rows per transaction, because a million rows rarely need to be atomic.
Repeat the condition in each batch so it’s safe to rerun, pause between batches and watch replica lag. The price is losing global atomicity, so the operation must be idempotent.
How far does SET TRANSACTION ISOLATION LEVEL reach?
On MySQL, with no keyword it only affects the next transaction; SESSION affects the whole session; GLOBAL affects new sessions.
On PostgreSQL it must be set inside the transaction (or via BEGIN ISOLATION LEVEL ...), and for the whole session you use SET SESSION CHARACTERISTICS. With a connection pool, a session-level change that isn’t reverted affects later requests; Spring’s @Transactional(isolation = ...) restores it automatically.
Senior
A service occasionally slows the whole database down for a few minutes, then it recovers. What do you suspect and how do you check?
I’d suspect a periodically long-open transaction (a report, a batch, or a request calling a slow API inside a transaction) holding locks or a snapshot.
- Check during the incident:
sys.innodb_lock_waits(who blocks whom),innodb_trx(age, rows locked), and whether HLL on the dashboard shows a sawtooth at the same times; on PostgreSQL,pg_stat_activityandpg_locks. - Fix: shrink transactions to the DB work, batch the batch jobs, set per-user timeouts, run long reports on a replica.
Why might killing a large transaction on MySQL not end the incident right away?
Because InnoDB rolls back by applying every undo record in reverse, and keeps holding its locks meanwhile.
A transaction that changed millions of rows over an hour can take about as long to roll back. Restarting the server doesn’t help either: after startup InnoDB still has to finish the rollback. So the right approach is prevention: batching, capping transaction size, and thinking twice before killing (sometimes letting it finish is faster).
At which layers do you set timeouts to stop harmful transactions?
At all four: application, pool, database and monitoring; each catches a different failure and none is enough alone.
- Application:
@Transactional(timeout)and HTTP client timeouts. - Pool: HikariCP’s
leakDetectionThresholdto spot connections held too long. - Database:
innodb_lock_wait_timeout/lock_timeoutfor lock waits,statement_timeoutormax_execution_timeper statement,idle_in_transaction_session_timeouton PostgreSQL; set per user so the web app gets low limits and jobs higher ones. - Monitoring: alerts on the oldest transaction’s age and on HLL/dead tuples.
Useful phrases for interviews
- “There are four kinds of harmful transaction: too large, long-running, stalled and abandoned, and each has a different fix.”
- “The first question for a huge update is whether it really needs to be atomic; usually I batch it by primary key.”
- “I keep external calls out of the transaction, so locks are held only while we talk to the database.”
- “An idle-in-transaction session still holds its locks and its snapshot, even though it isn’t running anything.”
- “Leaked connections are invisible to the pool, so I rely on leak detection and a report of abandoned transactions.”
- “I set timeouts per database user: tight for the web application, looser for batch jobs.”
- “Killing a big transaction on MySQL triggers an equally big rollback, so prevention beats cure.”
Notes based on the Advanced Backend course (Ronin Engineer), rewritten in my own words.