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

This article is about how to change the isolation level of mysql. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

The method to modify the mysql isolation level: 1, find "skip-external-locking"; 2, add "transaction-isolation = READ-COMMITTED" content; 3, restart the mysql service.

This article operating environment: Windows7 system, mysql5.5 version, Dell G3 computer.

How to change the isolation level of mysql?

Isolation level of MySQL transactions and how to modify them

Modify the transaction isolation level of Mysql:

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

At the location:

Lc-messages-dir = / usr/share/mysqlskip-external-locking

Add (read the submission) later:

Transaction-isolation = READ-COMMITTED

Restart the mysql service after saving:

Sudo service mysql restart

Four isolation levels of SQL

Read Uncommitted (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 Dirty Read.

Read Committed (read submission)

This is the default isolation level for most database systems (but not the default for MySQL). It satisfies the simple definition of isolation: a transaction can only see changes that have been committed to the transaction. This isolation level also supports so-called non-repeatable reads (Nonrepeatable Read), because other instances of the same transaction may have a new commit during the instance processing, so the same select may return different results.

Repeatable Read (rereadable) (Mysql defaults to this item: REPEATABLE-READ)

This is the default transaction isolation level for MySQL, which ensures that multiple instances of the same transaction see the same rows of data when reading data concurrently. But in theory, this leads to another thorny problem: Phantom Read. To put it simply, phantom reading means that when the user reads a range of data rows, another transaction inserts a new row in that range, and when the user reads the range of data rows, they will find a new "phantom" row. InnoDB and Falcon storage engines solve this problem through multi-version concurrency control (MVCC,Multiversion Concurrency Control) mechanisms.

Serializable (serializable)

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

When reading the same data, the problems that can easily occur are:

Dirty reading (Drity Read): one transaction has updated a piece of data, and another transaction has read the same data at this time. For some reason, if the previous RollBack operates, the data read by the latter transaction will be incorrect.

Non-repeatable read (Non-repeatable read): data inconsistencies between two queries of a transaction, which may be due to the insertion of original data updated by a transaction between the two queries.

Phantom Read: the number of data pens is inconsistent in two queries of a transaction, for example, one transaction queries several columns of data (Row), while another transaction inserts new columns of data at this time, the previous transaction will find several columns of data that it did not have before in the next query.

#!

After django2.X, the transaction level of the autonomous change framework and mysql session is READ-COMMITTED (read commit). If the required level is so, it does not need to be changed.

Thank you for reading! This is the end of the article on "how to modify the isolation level of mysql". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it for more people to see!

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.