MySQL教程

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

本站中文解释

transaction_isolation 参数用于控制mysql事务的隔离级别。这个参数控制 MySQL 事务的隔离级别，因为可以为并发事务防止丢失修改或脏读。Transaction_isolation 有四种可选的分离级别：读未提交（READ UNCOMMITTED）、读提交（READ COMMITTED）、可重复读（REPEATABLE READ）和串行化（SERIALIZABLE）。

要设置 transaction_isolation，可以在 my.cnf 文件中直接设置：

# 默认情况下，mysql 使用 REPEATABLE-READ
transaction-isolation=REPEATABLE-READ

也可以通过执行SQL语句在会话级别设置：

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

官方英文解释

transaction_isolation

Command-Line Format --transaction-isolation=name
System Variable (≥ 5.7.20) transaction_isolation
Scope (≥ 5.7.20) Global, Session
Dynamic (≥ 5.7.20) Yes
Type Enumeration
Default Value REPEATABLE-READ
Valid Values

READ-UNCOMMITTED

READ-COMMITTED

REPEATABLE-READ

SERIALIZABLE

The transaction isolation level. The default is
REPEATABLE-READ.

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

To set the global transaction isolation level at startup, use
the --transaction-isolation
server option.

At runtime, the isolation level can be set directly using the
SET
statement to assign a value to the
transaction_isolation system
variable, or indirectly using the SET
TRANSACTION statement. If you set
transaction_isolation
directly to an isolation level name that contains a space, the
name should be enclosed within quotation marks, with the space
replaced by a dash. For example, use this
SET
statement to set the global value: 

SET GLOBAL transaction_isolation = 'READ-COMMITTED';

Setting the global
transaction_isolation value
sets the isolation level for all subsequent sessions. Existing
sessions are unaffected.

To set the session or next-level
transaction_isolation 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 isolation level 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_isolation:

  • To set the session isolation level, use any of these
    syntaxes: 

    SET @@SESSION.transaction_isolation = value;
SET SESSION transaction_isolation = value;
SET transaction_isolation = value;

    For each of those syntaxes, these semantics apply:

    • Sets the isolation level 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
      isolation level.

    • Corresponds to
      SET
      SESSION TRANSACTION ISOLATION LEVEL       (with
      the SESSION keyword).

  • To set the next-transaction isolation level, use this
    syntax: 

    SET @@transaction_isolation = value;

    For that syntax, these semantics apply:

    • Sets the isolation level only for the next single
      transaction performed within the session.

    • Subsequent transactions revert to the session
      isolation level.

    • Not permitted within transactions.

    • Corresponds to
      SET
      TRANSACTION ISOLATION LEVEL       (without the
      SESSION keyword).

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

Note

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


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

相关推荐