MySQL教程

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.


数据运维技术 » MySQL Variables transaction_read_only 数据库 参数变量解释及正确配置使用
分享到：

相关推荐