3、全局与会话级别设置示例
--如何设置隔离级别
mysql> help isolation
Name: 'ISOLATION'
Description:
Syntax:
SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL
{
REPEATABLE READ
| READ COMMITTED
| READ UNCOMMITTED
| SERIALIZABLE
}
--下面我们通过演示隔离级别来设置全局与session级别变量
--查看当前session级别的隔离方式
root@localhost[(none)]> show variables like '%isolation%';
+---------------+-----------------+
| Variable_name | Value|
+---------------+-----------------+
| tx_isolation | REPEATABLE-READ |
+---------------+-----------------+
--修改当前session级别的隔离方式为READ-COMMITTED
root@localhost[(none)]> set session transaction isolation level read committed;
root@localhost[(none)]> show variables like '%isolation%';
+---------------+----------------+
| Variable_name | Value|
+---------------+----------------+
| tx_isolation | READ-COMMITTED |
+---------------+----------------+
--另外的一个session ， 登录用户为fred
--当前sessioin级别继承全局隔离级别为REPEATABLE-READ
fred@localhost[(none)]> show variables like '%isolation%';
+---------------+-----------------+
| Variable_name | Value|
+---------------+-----------------+
| tx_isolation | REPEATABLE-READ |
+---------------+-----------------+
--在root会话中设置全局隔离级别为serializable
root@localhost[(none)]> set global transaction isolation level serializable;
--注意，在root会话中 session级别还是为READ-COMMITTED
root@localhost[(none)]> show variables like '%isolation%';
+---------------+----------------+
| Variable_name | Value|
+---------------+----------------+
| tx_isolation | READ-COMMITTED |
+---------------+----------------+
--在root会话中我可以看到全局的值已经变为SERIALIZABLE
root@localhost[(none)]> show global variables like '%isolation%';
+---------------+--------------+
| Variable_name | Value |
+---------------+--------------+
| tx_isolation | SERIALIZABLE |
+---------------+--------------+
--在fred中全局的也变成了SERIALIZABLE
fred@localhost[(none)]> show global variables like '%isolation%';
+---------------+--------------+
| Variable_name | Value |
+---------------+--------------+
| tx_isolation | SERIALIZABLE |
+---------------+--------------+
--从上面的演示来说，无论global级别如何设置，不会影响到当前session级别的设置
--下面我们使用一个新用户登录来看看全局设置是否影响新会话
robin@SZDB:~> mysql -urobin
--如下查询新会话的隔离级别等于全局的隔离级别
robin@localhost[(none)]> show variables like '%isolation%';
+---------------+--------------+
| Variable_name | Value |
+---------------+--------------+
| tx_isolation | SERIALIZABLE |
+---------------+--------------+
如果觉得《mysql系统变量_MySQL 系统变量(system variables)》对你有帮助，请点赞、收藏，并留下你的观点哦！
