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

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:

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.

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

transaction_isolation system
variable, see Section 13.3.6, “SET TRANSACTION Statement”.

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、ORACLE、SQLSERVER、MongoDB、PostgreSQL 、程序问题
服务方式：远程服务、电话支持、现场服务，沟通指定方式服务
技术标签：数据恢复、安装配置、数据迁移、集群容灾、异常处理、其它问题

本站部分文章参考或来源于网络，如有侵权请联系站长。
数据库远程运维 MySQL Variables transaction_isolation 数据库 参数变量解释及正确配置使用