How to modify the isolation level of mysql

Methods to modify the mysql isolation level: 1. Find "skip-external-locking"; 2. Add the content of "transaction-isolation = READ-COMMITTED"; 3. Restart the mysql service.

The operating environment of this article: Windows 7 system, mysql version 5.5, Dell G3 computer.

How to modify the isolation level of mysql?

MySQL transaction isolation level and modification method

Modify MySQL transaction isolation level:

sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf

Add (read submission content) after the position:

lc-messages-dir = /usr/share/mysql
skip-external-locking

:

transaction-isolation = READ-COMMITTED

Save and restart the mysql service:

sudo service mysql restart

SQL 4 isolation levels

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 for 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 (Mysql defaults to this item: REPEATABLE-READ)

This is the default transaction isolation level of MySQL, which ensures that multiple instances of the same transaction can read concurrently data, you will see the same rows of data. However, in theory, this will lead to another thorny problem: phantom read (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, Multiversion Concurrency Control) 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.

​

When reading the same data, problems that are likely to occur include:

Dirty Read: A transaction has updated one piece of data, and another piece of data has been updated. A transaction reads the same data at this time. For some reasons, the previous RollBack operation is performed, and the data read by the latter transaction will be incorrect.

Non-repeatable read: The data is inconsistent between two queries of a transaction. This may be due to the original data updated by a transaction inserted between the two query processes.

Phantom Read: The number of data items in two queries of a transaction is inconsistent. For example, one transaction queries several rows of data, while another transaction inserts new data at this time. Several columns of data, in the subsequent query of the previous transaction, you will find that there are several columns of data that it did not have before.

# !

After django2.

## Recommended learning: "

mysql video tutorial"

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