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

This article mainly introduces the example analysis of isolation level in MySQL, which is very detailed and has certain reference value. Friends who are interested must finish it!

Getting started with MySQL-transaction isolation level

1) introduction of isolation level

If one client's transaction changes data, whether other client's transactions should discover these changes or should be isolated from them, the transaction isolation level determines how simultaneous transactions interact with each other when accessing the same data. The isolation level can be achieved using the storage engine. Isolation level options vary from database server to database server, so the levels implemented by InnoDB may not exactly correspond to those implemented by other database systems. InnoDB can implement four isolation levels to control the extent to which changes made by the transaction can be noticed by other simultaneous transactions. The four levels of isolation are as follows:

L READ UNCOMMITTED: allows transactions to view uncommitted changes made by other transactions; allows "dirty" reads, non-repeatable reads, and virtual reads.

L READ COMMITTED: allows transactions to view committed changes made by other transactions; allows unrepeatable and virtual reads to occur. Uncommitted changes are still not visible.

L REPEATABLE READ: ensure that the SELECT output of each transaction is consistent, the default level of InnoDB; you will get the same result twice, regardless of whether changes made by other transactions have been committed or not. In other words, different transactions produce consistent results for the same data.

L SERIALIZABLE: completely isolates the results of one transaction from other transactions; similar to REPEATABLE READ, but more restrictive, that is, the rows selected by one transaction cannot be changed by other transactions until the first transaction is completed.

2) set the isolation level

The system default transaction level is: repeatable-read

Method 1. Set the level when the server starts.

-use the-- transaction-isolation option in the mysqld command.

-set transaction-isolation in the configuration file:

[mysqld]

Transaction-isolation =

Set the value to: in the options file or on the command line:

L READ-UNCOMMITTED

L READ-COMMITTED

L REPEATABLE-READ

L SERIALIZABLE

Method 2. Use the SET TRANSACTION ISOLATION LEVEL statement to set up the running server.

-example of syntax:

SET GLOBAL TRANSACTION ISOLATION LEVEL

SET SESSION TRANSACTION ISOLATION LEVEL

SET TRANSACTION ISOLATION LEVEL

For the SET TRANSACTION ISOLATION LEVEL statement, set the value to:

L READ UNCOMMITTED

L READ COMMITTED

L REPEATABLE READ

L SERIALIZABLE .

This transaction level can be set globally or on a per-session basis. If not explicitly specified, the transaction isolation level is set on a per-session basis. For example, the following statement sets the isolation level for the current mysql session to READ COMITTED:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

This statement is equivalent to:

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED

To set the default level for all subsequent mysql connections, use the GLOBAL keyword instead of SESSION:

SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED

Note: the global default transaction isolation level set applies to all newly established client connections from the time of setup. Existing connections are not affected.

Method 3. SET GLOBAL TX_ISOLATION

SUPER permission is required

Mysql > set global tx_isolation=' READ-COMMITTED'

Mysql > select @ @ tx_isolation

Mysql > show variables like 'tx_isolation'

Transaction_isolation MySQL 5.7.20 was introduced to replace the soon-to-be-deprecated tx_isolation (MySQL 8.0)

(root@localhost) [information_schema] > show variables like'% isolat%'

+-+ +

| | Variable_name | Value |

+-+ +

| | transaction_isolation | REPEATABLE-READ |

| | tx_isolation | REPEATABLE-READ |

+-+ +

Transaction_isolation was added in MySQL 5.7.20 as an alias 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.

The above is all the content of the article "sample Analysis of isolation levels in MySQL". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

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.