Shulou(Shulou.com)05/31 Report--

Xiaobian to share with you how to modify the transaction isolation level in mysql, I hope you have something to gain after reading this article, let's discuss it together!

Modification method: 1. In the command window, execute the statement "set session transaction isolation level;";2. Open the file "mysql.ini" and add the statement "transaction-isolation= transaction level."

Operating environment of this tutorial: Windows 7 system, MySQL8 version, Dell G3 computer.

mysql transaction isolation level

The isolation level of transactions is divided into: read uncommitted, read committed, repeatable read, serializable.

Read Uncommitted

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 known as a dirty read.

Read Submitted

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

Repeatable Read

This is MySQL's default transaction isolation level, which ensures that multiple instances of the same transaction see the same rows of data when reading data concurrently. In theory, though, this leads to another tricky problem: Phantom Read. In simple terms, phantom reading means that when a user reads a range of data rows, another transaction inserts new rows into the range, and when the user reads the range of data rows again, he will find new "phantom" rows. InnoDB and Falcon storage engines solve this problem through the Multiversion Concurrency Control (MVCC) mechanism.

Serializable

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

mysql modify transaction isolation level

Method 1: Execute Command Modifications

//View current thing level: SELECT @@tx_isolation;

//Set mysql isolation level: set session transaction isolation level

cases

//Set read uncommitted: set session transaction isolation level read uncommitted;//Set read committed: set session transaction isolation level read committed;//Set repeatable read: set session transaction isolation level repeatable read;//Set serializable: set session transaction isolation level serializable;

Method 2: mysql.ini configuration modification

Open the mysql.ini configuration file and add

#Optional parameters: READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE. [mysqld]transaction-isolation = REPEATABLE-READ

Here the global default is REPEATABLE-READ, in fact MySQL is also the default level

After reading this article, I believe you have a certain understanding of "how to modify the transaction isolation level in mysql." If you want to know more relevant knowledge, please pay attention to the industry information channel. Thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.