mysql隔离级别参数和会话动态设置--笔记

1、参数设置
可选参数有：READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE.
如：
transaction_isolation = REPEATABLE-READ
或
transaction-isolation = READ-COMMITTED

2、动态设置方式
# 未提交读级别
set session transaction isolation level read uncommitted;
# 已提交读级别
全局
set global tx_isolation = 'READ-COMMITTED'
set global transaction isolation level read committed;
会话
set tx_isolation = 'READ-COMMITTED'
set session transaction isolation level read committed;
# 可重复读级别
全局
set global tx_isolation = 'REPEATABLE-READ'
set global transaction isolation level repeatable read;
会话
set tx_isolation = 'REPEATABLE-READ'
set session transaction isolation level repeatable read;
# 可串行化级别
set session transaction isolation level serializable;

 

[mysql@localhost ~]$ mysql.7308.login 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1533
Server version: 5.6.47-87.0-log huanglingfei make install Percona Server

Copyright (c) 2009-2020 Percona LLC and/or its affiliates
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.

root@localhost: 01:23 [7308][(none)]>help set;
Name: 'SET'
Description:
Syntax:
SET variable = expr [, variable = expr] ...

variable: {
    user_var_name
  | param_name
  | local_var_name
  | {GLOBAL | @@GLOBAL.} system_var_name
  | [SESSION | @@SESSION. | @@] system_var_name
}

SET ONE_SHOT system_var_name = expr

SET syntax for variable assignment enables you to assign values to
different types of variables that affect the operation of the server or
clients:

o User-defined variables. See
  https://dev.mysql.com/doc/refman/5.6/en/user-variables.html.

o Stored procedure and function parameters, and stored program local
  variables. See
  https://dev.mysql.com/doc/refman/5.6/en/stored-program-variables.html
  .

o System variables. See
  https://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html.
  System variables also can be set at server startup, as described in
  https://dev.mysql.com/doc/refman/5.6/en/using-system-variables.html.

URL: https://dev.mysql.com/doc/refman/5.6/en/set-variable.html

root@localhost: 01:24 [7308][(none)]>

 

root@localhost: 01:24 [7308][(none)]>show variables like '%iso%';
+---------------+----------------+
| Variable_name | Value          |
+---------------+----------------+
| tx_isolation  | READ-COMMITTED |
+---------------+----------------+
1 row in set (0.00 sec)
oot@localhost: 01:29 [7308][(none)]>show global variables like '%iso%';
+---------------+----------------+
| Variable_name | Value          |
+---------------+----------------+
| tx_isolation  | READ-COMMITTED |
+---------------+----------------+
1 row in set (0.00 sec)

root@localhost: 01:30 [7308][(none)]>

root@localhost: 01:25 [7308][(none)]>
root@localhost: 01:26 [7308][(none)]>select @@tx_isolation;    
+----------------+
| @@tx_isolation |
+----------------+
| READ-COMMITTED |
+----------------+
1 row in set (0.00 sec)

root@localhost: 01:26 [7308][(none)]>select @tx_isolation; 
+---------------+
| @tx_isolation |
+---------------+
| NULL          |
+---------------+
1 row in set (0.00 sec)

root@localhost: 01:26 [7308][(none)]>

 

root@localhost: 02:37 [7308][(none)]>set global tx_isolation = 'REPEATABLE-READ';
Query OK, 0 rows affected (0.00 sec)

root@localhost: 02:57 [7308][(none)]>show global variables like '%iso%';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+
1 row in set (0.00 sec)

root@localhost: 02:57 [7308][(none)]>show variables like '%iso%';       
+---------------+----------------+
| Variable_name | Value          |
+---------------+----------------+
| tx_isolation  | READ-COMMITTED |
+---------------+----------------+
1 row in set (0.00 sec)

root@localhost: 02:57 [7308][(none)]>set tx_isolation = 'REPEATABLE-READ';
Query OK, 0 rows affected (0.00 sec)

root@localhost: 02:58 [7308][(none)]>show variables like '%iso%';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+
1 row in set (0.00 sec)

root@localhost: 02:58 [7308][(none)]>
root@localhost: 02:58 [7308][(none)]>set session transaction isolation level serializable;
Query OK, 0 rows affected (0.00 sec)

root@localhost: 02:58 [7308][(none)]>show variables like '%iso%';
+---------------+--------------+
| Variable_name | Value        |
+---------------+--------------+
| tx_isolation  | SERIALIZABLE |
+---------------+--------------+
1 row in set (0.00 sec)

root@localhost: 02:58 [7308][(none)]>show global variables like '%iso%';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+
1 row in set (0.00 sec)

root@localhost: 02:59 [7308][(none)]>set global transaction isolation level serializable;
Query OK, 0 rows affected (0.00 sec)

root@localhost: 02:59 [7308][(none)]>show global variables like '%iso%';
+---------------+--------------+
| Variable_name | Value        |
+---------------+--------------+
| tx_isolation  | SERIALIZABLE |
+---------------+--------------+
1 row in set (0.00 sec)

root@localhost: 02:59 [7308][(none)]>