Understanding InnoDB Transaction Types: Start, Read, Read‑Only, Read‑Write, and Internal Transactions

Author: Cao Shengchun, technical expert at iKangsheng, author of the public account "Yi Shu Yi Xi"; focuses on MySQL and OceanBase source code analysis.

This article is based on MySQL 8.0.32 source code with the InnoDB storage engine.

1. Starting a Transaction

Eight SQL statements can start a transaction, but statements 1‑5 only set the OPTION_BEGIN flag; the transaction is actually started when the first SQL is executed.

/* 1 */ BEGIN
/* 2 */ BEGIN WORK
/* 3 */ START TRANSACTION
/* 4 */ START TRANSACTION READ WRITE
/* 5 */ START TRANSACTION READ ONLY
/* 6 */ START TRANSACTION WITH CONSISTENT SNAPSHOT
/* 7 */ START TRANSACTION WITH CONSISTENT SNAPSHOT, READ WRITE
/* 8 */ START TRANSACTION WITH CONSISTENT SNAPSHOT, READ ONLY

When the first SQL runs, InnoDB changes the transaction object's state from TRX_STATE_NOT_STARTED to TRX_STATE_ACTIVE :

trx->state.store(TRX_STATE_ACTIVE, std::memory_order_relaxed)

Running SHOW ENGINE INNODB STATUS will display the transaction as ACTIVE , reflecting the TRX_STATE_ACTIVE state.

2. Read Transaction

If the first SQL is SELECT , UPDATE or DELETE , InnoDB starts a read transaction and sets its ID to 0. The information_schema.innodb_trx table shows a large numeric trx_id derived from the transaction object's memory address plus the constant 281474976710656 (the maximum 6‑byte transaction ID + 1).

Example calculation:

memory address 0x000000013afa8fa8 → decimal 5284466600
trx_id = 5284466600 + 281474976710656 = 281480261177256

If trx_id ≥ 281474976710656, the transaction has not been assigned a real ID; otherwise it has.

3. Read‑Only Transaction

A read‑only transaction is a special case of a read transaction: it cannot modify permanent tables but may modify temporary tables. Its ID is also 0 unless it inserts into a temporary table, in which case an ID is allocated.

-- create a temporary table before the read‑only transaction
create temporary table t_tmp (
  id int unsigned auto_increment primary key,
  i1 int not null default 0,
  i2 int not null default 0
) engine=InnoDB default charset=utf8;

-- start a read‑only transaction
START TRANSACTION READ ONLY;

-- insert into the temporary table
INSERT INTO t_tmp(i1, i2) VALUES (10, 100);

Querying information_schema.innodb_trx after the insert shows a small trx_id (e.g., 206266), indicating that the read‑only transaction received an ID.

4. Read‑Write Transaction

If the first SQL is INSERT , InnoDB starts a read‑write transaction. It allocates a rollback segment, assigns a transaction ID, and adds the transaction object to the trx_sys->rw_trx_list linked list.

UT_LIST_ADD_FIRST(trx_sys->rw_trx_list, trx);

5. Internal Transaction

Internal transactions are always read‑write because they are created to modify data. They are also added to trx_sys->rw_trx_list .

6. Summary

InnoDB starts internal transactions as read‑write to modify tables. User transactions may be read, read‑only, or read‑write depending on the first SQL statement; read‑only transactions that insert into temporary tables also receive a transaction ID.

Question for readers: What are the purposes of the mysql_trx_list and rw_trx_list linked lists?

Next preview: MySQL core module deep dive – Episode 05: Variations of read and read‑only transactions.