Example of viewing and modifying MySQL transaction isolation level

Check the transaction isolation level

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

The SQL statements and running results for viewing the current transaction isolation level are 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 of MySQL is REPEATABLE-READ.

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

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

Tip: In MySQL 8.0.3, the tx_isolation variable was replaced by the transaction_isolation variable. To query the transaction isolation level in MySQL 8.0.3, just replace the tx_isolation variable in the above query with the transaction_isolation variable.

Change the transaction isolation level

MySQL provides the SET TRANSACTION statement, which 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}

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 in the current session (current cmd window);
  
• GLOBAL: Indicates that the modified transaction isolation level will be applied to all transactions in all sessions (global), and the existing sessions will not be affected;
  
• If SESSION and GLOBAL are omitted, the modified transaction isolation level will be applied to the next transaction that has not yet started in the current session.

Any user can change the transaction isolation level for a session, but only users with the SUPER privilege can change the global transaction isolation level.

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

C:\Users\leovo>mysql -utestuser -p
Enter password: ******
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 41
Server 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 its
affiliates. Other names may be trademarks of their respective
owners.
 
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 operation
mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
Query OK, 0 rows affected (0.00 sec)

Example 1

Use the SET TRANSACTION statement to modify the session and global transaction isolation levels. The SQL statements and 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 running 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 points supplement

Mysql transaction isolation level read commit flow chart

The above is the detailed content of the example of viewing and modifying the transaction isolation level of MySQL. For more information about viewing and modifying the transaction isolation level of MySQL, please pay attention to other related articles on 123WORDPRESS.COM!