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

Today, I will talk to you about how to view and modify the transaction isolation level in MySQL. Many people may not know much about it. In order to make you understand better, the editor has summarized the following for you. I hope you can get something according to this article.

View transaction isolation level

In MySQL, you can view the current transaction isolation level through show variables like'% tx_isolation%' or select @ @ tx_isolation; statements.

View the SQL statement at the current transaction isolation level and run the result as follows:

Mysql > show variables like'% tx_isolation%' +-+-+ | Variable_name | Value | +-+-+ | tx_isolation | REPEATABLE-READ | +-+-+ 1 row in set 1 warning (0.17 sec) mysql > select @ @ tx_isolation +-+ | @ @ tx_isolation | +-+ | REPEATABLE-READ | +-+ 1 row in set, 1 warning (0.00 sec)

The results show that the current transaction isolation level for MySQL is REPEATABLE-READ.

In addition, you can query the global and session transaction isolation levels using the following statements:

SELECT @ @ global.tx_isolation;SELECT @ @ session.tx_isolation

Tip: in MySQL 8.0.3, the tx_isolation variable is replaced by the transaction_isolation variable. To query the transaction isolation level in MySQL version 8.0.3, simply replace the tx_isolation variable in the above query statement with the transaction_isolation variable.

Modify transaction isolation level

MySQL provides a SET TRANSACTION statement that can change the transaction isolation level for a single session or globally. The syntax format is as follows:

SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}

Where the SESSION and GLOBAL keywords are used to specify the scope of the modified transaction isolation level:

SESSION: indicates that the modified transaction isolation level will be applied to all transactions within the current session (current cmd window)

GLOBAL: indicates that the modified transaction isolation level will be applied to all transactions in all session (global), and the existing session will not be affected

If SESSION and GLOBAL are omitted, the modified transaction isolation level will be applied to the next transaction within the current session that has not yet started.

Any user can change the transaction isolation level of a session, but only a user with SUPER permission can change the global transaction isolation level.

If you modify the global transaction isolation level with a normal user, you will be prompted with an error message that super permissions are required to perform this operation. The SQL statement and the run result are as follows:

C:\ Users\ leovo > mysql-utestuser-pEnter password: * Welcome to the MySQL monitor. Commands end with; or\ g.Your MySQL connection id is 41Server version: 5.7.29-log MySQL Community Server (GPL) Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners. Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement. Mysql > SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER privilege (s) for this operationmysql > SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;Query OK, 0 rows affected (sec)

Example 1

Use the SET TRANSACTION statement to modify the session and global transaction isolation level SQL statements, respectively, and the running results are as follows:

Mysql > select @ @ session.tx_isolation;+----+ | @ @ session.tx_isolation | +-+ | SERIALIZABLE | +-+ 1 row in set, 1 warning (0.00 sec) mysql > SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ Query OK, 0 rows affected (0.00 sec) mysql > select @ @ global.tx_isolation;+---+ | @ @ global.tx_isolation | +-- + | REPEATABLE-READ | +-- + 1 row in set, 1 warning (0.00 sec)

You can also use the set tx_isolation command to directly modify the transaction isolation level of the current session. The SQL statement and run results are as follows:

Mysql > set tx_isolation='READ-COMMITTED';Query OK, 0 rows affected, 1 warning (0.00 sec) mysql > select @ @ session.tx_isolation +-+ | @ @ session.tx_isolation | +-+ | READ-COMMITTED | +-+ 1 row in set, 1 warning (0.00 sec)

Knowledge point supplement

Read commit flowchart of Mysql transaction isolation level

After reading the above, do you have any further understanding of how to view and modify transaction isolation levels in MySQL? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

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.