Understanding Read and Read‑Only Transactions in MySQL InnoDB

1. update, delete

The following sections relate to UPDATE and DELETE statements; we first give a brief overview of their execution flow.

For an UPDATE of a single row, the simplified steps are:

Server asks InnoDB to read a record from the table.

InnoDB returns the record; the server checks the WHERE condition.

If it matches, the server replaces the fields with the values from the SET clause.

After the new record is assembled, the server triggers InnoDB to update the row.

For a DELETE of a single row, the simplified steps are:

Server asks InnoDB to read a record from the table.

InnoDB returns the record; the server checks the WHERE condition.

If it matches, the server triggers InnoDB to delete the row.

2. Read transaction

A transaction starts as a read transaction when the first SQL statement is SELECT, UPDATE, or DELETE. No transaction ID or rollback segment is allocated, and the transaction object is not added to trx_sys->rw_trx_list .

Even without an ID, a read transaction can still execute data‑changing statements and supports MVCC and rollback.

The transition from read to read‑write depends on the first SQL statement:

First type: The first statement is UPDATE or DELETE. During execution, the read transaction becomes read‑write. The exact moment depends on the type of the first table involved.

If the first table is a regular user table, InnoDB acquires an intention‑exclusive (IX) lock before reading. When three conditions are met—no rollback segment yet, transaction ID is 0, and trx->read_only = false —InnoDB converts the transaction to read‑write by: Assigning a transaction ID. Allocating a rollback segment for the regular table. Adding the transaction object to trx_sys->rw_trx_list .

If the first table is a temporary user table, no IX lock is needed; the conversion is delayed until the first Undo log for that temporary table is written.

Second type: The first statement is SELECT. During SELECT execution the transaction remains read‑only; only after the SELECT finishes and the first INSERT/UPDATE/DELETE is issued does the transaction become read‑write.

When the first data‑changing statement is INSERT, the server prepares the record fields and then triggers InnoDB to insert. If the same three conditions (transaction started, ID is 0, trx->read_only = false ) hold, InnoDB converts the transaction to read‑write.

3. Read‑only transaction

A read‑only transaction is started with START TRANSACTION READ ONLY :

start transaction read only

Such a transaction cannot modify system tables or regular user tables, but it may modify temporary user tables.

The allocation of a transaction ID and a rollback segment for a temporary table also depends on the first data‑changing statement:

First type: First statement is UPDATE or DELETE. When InnoDB is about to update or delete a temporary table row, if the transaction is started, ID is 0, and trx->read_only = true , InnoDB assigns a transaction ID and a rollback segment for the temporary table.

Second type: First statement is SELECT. No ID or rollback segment is allocated during SELECT; they are allocated only when the first INSERT/UPDATE/DELETE after the SELECT is executed, under the same three conditions.

4. Summary

When a transaction begins as a read or read‑only transaction:

If the first SQL is UPDATE or DELETE, the transaction becomes read‑write during execution, and a read‑only transaction will receive a transaction ID and a rollback segment for temporary tables.

If the first SQL is SELECT, the transaction stays read until the first subsequent INSERT, UPDATE, or DELETE, at which point it becomes read‑write and the same resources are allocated.

InnoDB performs three main actions when converting to read‑write:

Assign a transaction ID.

Allocate a rollback segment for the involved user table.

Add the transaction object to trx_sys->rw_trx_list .

For further discussion, readers are invited to leave comments, and the next issue will explore where binlog is written before transaction commit.