Understanding the Variations and Internals of the MySQL BEGIN Statement

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

1. BEGIN Statement Variations

The official MySQL documentation shows a complex syntax for starting a transaction, which can be expanded into several concrete SQL statements.

START TRANSACTION [transaction_characteristic [, transaction_characteristic] ...]
transaction_characteristic: { WITH CONSISTENT SNAPSHOT | READ WRITE | READ ONLY }
BEGIN [WORK]

Expanding the combinations yields the following statements (comments indicate the variant number):

/*  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
/*  9 */ START TRANSACTION WITH CONSISTENT SNAPSHOT, READ WRITE, READ ONLY
/* 10 */ START TRANSACTION READ WRITE, READ ONLY

Statements 1‑8 execute successfully, while 9 and 10 produce a syntax error:

(1064,
 "You have an error in your SQL syntax;
 check the manual that corresponds
 to your MySQL server version 
 for the right syntax to use 
 near '' at line 1")

The error is not because MySQL cannot parse the syntax, but because after parsing it detects mutually exclusive options (READ WRITE and READ ONLY) and deliberately throws a syntax error:

if (($3 & MYSQL_START_TRANS_OPT_READ_WRITE) &&
    ($3 & MYSQL_START_TRANS_OPT_READ_ONLY)) {
  YYTHD->syntax_error();
  MYSQL_YYABORT;
}

Thus, when both READ WRITE and READ ONLY are present, MySQL aborts with a syntax error.

2. What BEGIN Actually Does

The BEGIN statement mainly performs two actions:

Commit any existing (old) transaction.

Prepare a new transaction.

2.1 Commit Old Transaction

If a connection already has an active transaction (e.g., after an INSERT), issuing BEGIN causes that transaction to be committed because MySQL does not support nested transactions.

BEGIN first checks whether the current thread may have an uncommitted transaction by examining the option bits:

if (thd->in_multi_stmt_transaction_mode() || ...) {
  ...
}

inline bool in_multi_stmt_transaction_mode() const {
  return variables.option_bits &
    (OPTION_NOT_AUTOCOMMIT | OPTION_BEGIN);
}

If either OPTION_NOT_AUTOCOMMIT or OPTION_BEGIN is set, MySQL assumes a possible pending transaction and issues a commit before starting a new one:

if (thd->in_multi_stmt_transaction_mode() || ...) {
  ...
  res = ha_commit_trans(thd, true);
}

2.2 Prepare New Transaction

After committing the old transaction (if any), BEGIN prepares the new transaction by setting the OPTION_BEGIN flag:

thd->variables.option_bits |= OPTION_BEGIN;

This flag tells MySQL not to automatically commit after each statement, allowing the user to execute multiple statements within the same transaction until an explicit COMMIT is issued.

3. Summary

In short, the BEGIN statement first commits any existing transaction (because MySQL lacks nested transactions) and then marks the connection with OPTION_BEGIN to prepare a new transaction, without actually starting it immediately.

Discussion: For START TRANSACTION with both READ WRITE and READ ONLY, besides the syntax error, what alternative approaches could be used? Feel free to share your thoughts.

Next Topic Preview: "I am a transaction, give me an object."