MySQL教程 2023-03-27

MySQL Variables transaction_read_only 数据库参数变量解释及正确配置使用

本站中文解释

MySQL参数变量transaction_read_only控制MySQL支持写及只读事务，以及在支持写事务时能否修改当前事务变量。该参数仅影响用户会话而不影响读模式的表；支持的取值为on和off，默认值为off，表示支持读写事务，只读事务不受影响。

设置MySQL参数变量transaction_read_only的方法：

1、在my.cnf中设置：打开my.cnf文件，在[mysqld]的下方加入如下代码：

transaction_read_only=on/off

然后保存文件并让配置文件生效；

2、在运行时动态设置：在登录MySQLshell，运行以下命令：

set GLOBAL transaction_read_only = on/off;

（注：此时设置仅对当前会话生效，只有将配置永久地添加到my.cnf文件，才能实现长期有效）。

官方英文解释

transaction_read_only

Command-Line Format	`--transaction-read-only[={OFF\|ON}]`
System Variable (≥ 5.7.20)	`transaction_read_only`
Scope (≥ 5.7.20)	Global, Session
Dynamic (≥ 5.7.20)	Yes
Type	Boolean
Default Value	`OFF`

The transaction access mode. The value can be
OFF (read/write; the default) or
ON (read only).

The transaction access mode has three scopes: global, session,
and next transaction. This three-scope implementation leads to
some nonstandard access-mode assignment semantics, as
described later.

To set the global transaction access mode at startup, use the
--transaction-read-only server
option.

At runtime, the access mode can be set directly using the
SET
statement to assign a value to the
transaction_read_only system
variable, or indirectly using the SET TRANSACTION statement. For example, use this
SET
statement to set the global value:

SET GLOBAL transaction_read_only = ON;

Setting the global
transaction_read_only value
sets the access mode for all subsequent sessions. Existing
sessions are unaffected.

To set the session or next-level
transaction_read_only value,
use the
SET
statement. For most session system variables, these statements
are equivalent ways to set the value:

SET @@SESSION.var_name = value;
SET SESSION var_name = value;
SET var_name = value;
SET @@var_name = value;

As mentioned previously, the transaction access mode has a
next-transaction scope, in addition to the global and session
scopes. To enable the next-transaction scope to be set,
SET
syntax for assigning session system variable values has
nonstandard semantics for
transaction_read_only,

To set the session access mode, use any of these syntaxes:
```
SET @@SESSION.transaction_read_only = value;
SET SESSION transaction_read_only = value;
SET transaction_read_only = value;
```
For each of those syntaxes, these semantics apply:
- Sets the access mode for all subsequent transactions
  performed within the session.
- Permitted within transactions, but does not affect the
  current ongoing transaction.
- If executed between transactions, overrides any
  preceding statement that sets the next-transaction
  access mode.
- Corresponds to
  SET SESSION TRANSACTION {READ WRITE | READ ONLY}
  (with the SESSION keyword).
To set the next-transaction access mode, use this syntax:
```
SET @@transaction_read_only = value;
```
For that syntax, these semantics apply:
- Sets the access mode only for the next single
  transaction performed within the session.
- Subsequent transactions revert to the session access
  mode.
- Not permitted within transactions.
- Corresponds to
  SET TRANSACTION {READ WRITE | READ ONLY}
  (without the SESSION keyword).

For more information about SET TRANSACTION and its relationship to the
transaction_read_only system
variable, see Section 13.3.6, “SET TRANSACTION Statement”.

Note

transaction_read_only was
added in MySQL 5.7.20 as a synonym for
tx_read_only, which is now
deprecated and is removed in MySQL 8.0.
Applications should be adjusted to use
transaction_read_only in
preference to tx_read_only.