How to modify the transaction isolation level in mysql

Modification method: 1. Execute the "set session transaction isolation level transaction level;" statement in the command window; 2. Open the "mysql.ini" file and add the "transaction-isolation=transaction level" statement That’s it.

The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.

mysql transaction isolation level

The isolation level of a transaction is divided into: uncommitted read (read uncommitted), committed read (read committed) , repeatable read (repeatable read), serializable (serializable).

• Read Uncommitted(Read uncommitted content)

  At this isolation level, all transactions can see the execution results of other uncommitted transactions. This isolation level is rarely used in practical applications because its performance is not much better than other levels. Reading uncommitted data is also called dirty read.

• Read Committed(read commit content)

  This is the default isolation level of most database systems (but not the MySQL default). It meets the simple definition of isolation: a transaction can only see changes made by committed transactions. This isolation level also supports so-called nonrepeatable read, because other instances of the same transaction may have new commits during the processing of the instance, so the same select may return different results.

• Repeatable Read(rereadable)

  This is the default transaction isolation level of MySQL. It ensures that multiple instances of the same transaction will read data concurrently. to the same data row. But in theory, this would lead to another thorny problem: phantom read. Simply put, phantom reading means that when the user reads a certain range of data rows, another transaction inserts a new row in the range. When the user reads the data rows in the range again, he will find that there are new " Phantom” OK. InnoDB and Falcon storage engines solve this problem through the Multiversion Concurrency Control (MVCC) mechanism.

• Serializable

  This is the highest isolation level. It solves the phantom read problem by forcing transactions to be ordered so that they cannot conflict with each other. . In short, it adds a shared lock on each data row read. At this level, a lot of timeouts and lock contention can result.

mysql modify transaction isolation level

Method 1: Execute command modification

//查看当前事物级别：
SELECT @@tx_isolation;

//设置mysql的隔离级别：
set session transaction isolation level 需要设置的事务隔离级别

Example

//设置read uncommitted级别：
set session transaction isolation level read uncommitted;

//设置read committed级别：
set session transaction isolation level read committed;

//设置repeatable read级别：
set session transaction isolation level repeatable read;

//设置serializable级别：
set session transaction isolation level serializable;

Method 2: mysql.ini configuration modification

Open mysql.ini configuration file, add

#可选参数有：READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE.
[mysqld]
transaction-isolation = REPEATABLE-READ

at the end. The global default here is REPEATABLE-READ. In fact, MySQL originally defaults to this level.

[Related recommendations: mysql video tutorial]

The above is the detailed content of How to modify the transaction isolation level in mysql. For more information, please follow other related articles on the PHP Chinese website!