mysql系统变量_MySQL 系统变量(system variables)

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 |

+---------------+--------------+