mysql 事務的四種隔離等級

標籤：mysql 四種事務隔離機制

MYSQL標準定義了4種隔離等級，用來限定事務內外的哪些改變是可見的，哪些是不可見的。  

低的隔離級一般支援更高的並發處理，並擁有更低的系統開銷。

隔離等級由低到高：Read Uncommitted < Read Committed < Repeatable Read < Serializable.

 第一：READ UNCOMMITTED (讀取未提交內容)

在該隔離等級，所有事務都可以看到其他未提交（commit）事務的執行結果。

本隔離等級很少用於實際應用，因為它的效能也不比其他層級好多少。

讀取未提交的資料，也被稱為髒讀（Dirty Read）.

[視窗A]:

 

mysql> set GLOBAL tx_isolation=‘READ-UNCOMMITTED‘;

Query OK, 0 rows affected (0.00 sec)

 

mysql> quit;

Bye

 

[[email protected] ~]# mysql -uroot -pxxxx(重新登入)

 

mysql> SELECT @@tx_isolation;

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

| @@tx_isolation   |

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

| READ-UNCOMMITTED |

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

1 row in set (0.00 sec)

 

mysql> use test;

Database changed

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

 

mysql> select * from user;

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

| id | name |

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

|  1 | a    |

|  2 | b    |

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

2 rows in set (0.00 sec)

 

[視窗B]:

mysql> select @@tx_isolation;

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

| @@tx_isolation   |

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

| READ-UNCOMMITTED |

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

1 row in set (0.00 sec)

 

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

 

mysql> insert into test.user values (3, ‘c‘);

Query OK, 1 row affected (0.00 sec)

 

mysql> select * from user;

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

| id | name |

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

|  1 | a    |

|  2 | b    |

|  3 | c    |

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

3 rows in set (0.00 sec)

 

//目前為止，視窗B並未commit;

 

[視窗A]:

mysql> select * from user ;

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

| id | name |

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

|  1 | a    |

|  2 | b    |

|  3 | c    |

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

3 rows in set (0.00 sec)

第二：READ COMMITTED (讀取提交內容)

這是大多數資料庫的預設隔離等級（但不是MYSQL預設的）。

它滿足了隔離的簡單定義：一個事務只能看見已經提交事務所做的改變。

這種隔離等級也支援所謂的不可重得讀（NonrepeatableRead）,因為同一事務的其他執行個體在該執行個體處理

其間可能會有新的COMMIT，所以同一SELECT 可能返回不同結果。

[視窗A]：

 

mysql> SET GLOBAL tx_isolation=‘READ-COMMITTED‘;

Query OK, 0 rows affected (0.00 sec)

 

mysql> quit;

Bye

 

[[email protected] ~]# mysql -uroot -pxxxx(重新登入)

 

mysql> SELECT @@tx_isolation;

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

| @@tx_isolation |

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

| READ-COMMITTED |

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

1 row in set (0.00 sec)

 

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

 

mysql> select * from test.user;

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

| id | name |

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

|  1 | a    |

|  2 | b    |

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

2 rows in set (0.00 sec)

 

 

[視窗B]:

 

mysql> SELECT @@tx_isolation;

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

| @@tx_isolation |

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

| READ-COMMITTED |

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

1 row in set (0.00 sec)

 

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

 

mysql> select * from test.user;

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

| id | name |

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

|  1 | a    |

|  2 | b    |

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

2 rows in set (0.00 sec)

 

mysql> delete from test.user where id=1;

Query OK, 1 row affected (0.00 sec)

 

mysql> select * from test.user;

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

| id | name |

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

|  2 | b    |

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

1 row in set (0.00 sec)

 

[視窗A]:

 

mysql> select * from test.user;

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

| id | name |

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

|  1 | a    |

|  2 | b    |

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

2 rows in set (0.00 sec)

 

[視窗B]:

 

mysql> commit;

Query OK, 0 rows affected (0.02 sec)

 

[視窗A]:

 

mysql> select * from test.user;

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

| id | name |

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

|  2 | b    |

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

1 row in set (0.00 sec)

第三：Repeatable Read(可重讀）

這是MYSQL的預設交易隔離等級，它確保同一事務的多個執行個體在並發讀取資料時，會看到同樣的資料行。

不過理論上，這會導致另一個棘手的問題：幻讀 （Phantom Read）。

簡單的說，幻讀指當使用者讀取某一範圍的資料行時，另一個事務又在該範圍插入入了新行，當使用者再讀取該範圍的資料行時，會發現有新的“幻影”行。

INNODB和Falcon儲存引擎通過多版本並發控制（MVCC,Multiversion Concurrency Control）

機制解決了該問題。

[視窗A]:

mysql> SET GLOBAL tx_isolation=‘REPEATABLE-READ‘;

Query OK, 0 rows affected (0.00 sec)

mysql> quit;

Bye

[[email protected] ~]# mysql -uroot -pxxxx(重新登入)

mysql> SELECT @@tx_isolation;

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

| @@tx_isolation  |

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

| REPEATABLE-READ |

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

1 row in set (0.00 sec)

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

[視窗B]:

mysql> quit;

Bye

[[email protected] ~]# mysql -uroot -pxxxx(重新登入)

mysql> SELECT @@tx_isolation;

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

| @@tx_isolation  |

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

| REPEATABLE-READ |

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

1 row in set (0.00 sec)

mysql> insert into test.user values (4, ‘d‘);

Query OK, 1 row affected (0.00 sec)

mysql> select * from test.user;

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

| id | name |

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

|  2 | b    |

|  4 | d    |

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

2 rows in set (0.00 sec)

[視窗A]:

mysql> select * from test.user;

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

| id | name |

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

|  2 | b    |

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

1 rows in set (0.00 sec)

mysql> commit;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from test.user;

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

| id | name |

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

|  2 | b    |

|  4 | d    |

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

2 rows in set (0.00 sec)

第四：Serializable(序列化執行）

這是最高的隔離等級，它通過強制事務排序，使之不可能相互衝突，從而解決幻讀問題。

簡言之，它是在每個讀的資料行上加上共用鎖定。在這個層級，可能導致大量的逾時現象和鎖競爭。

[視窗A]:

mysql> SET GLOBAL tx_isolation=‘SERIALIZABLE‘;

Query OK, 0 rows affected (0.00 sec)

mysql> quit;

Bye

[[email protected] ~]# mysql -uroot -pxxxx(重新登入)

mysql> SELECT @@tx_isolation;

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

| @@tx_isolation |

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

| SERIALIZABLE   |

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

1 row in set (0.00 sec)

mysql> select * from test.user;

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

| id | name |

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

|  2 | b    |

|  4 | d    |

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

2 rows in set (0.00 sec)

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

mysql> insert into test.user values (5, ‘e‘);

Query OK, 1 row affected (0.00 sec)

[視窗B]:

mysql> quit;

Bye

[[email protected] ~]# mysql -uroot -pxxxx(重新登入)

mysql> SELECT @@tx_isolation;

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

| @@tx_isolation |

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

| SERIALIZABLE   |

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

1 row in set (0.00 sec)

mysql> select * from test.user;

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

[視窗A]:

mysql> commit;

Query OK, 0 rows affected (0.01 sec)

[視窗B]:

mysql> mysql> select * from test.user;

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

| id | name |

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

|  2 | b    |

|  4 | d    |

|  5 | e    |

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

3 rows in set (0.00 sec)

本文出自 “12024114” 部落格，請務必保留此出處http://12034114.blog.51cto.com/12024114/1857750

mysql 事務的四種隔離等級