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)的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。