mysql隔離機制

事務：是一組原子性的SQL查詢語句，也可以被看做一個工作單元。

事務具有的四個特性：原子性(atomicity)、一致性(consistency)、隔離性(isolation)、永續性(durability)

1.關聯式資料庫標準中的4個事務隔離級別

#未提交讀(read uncommitted): 允許髒讀，也就是可能讀取到其他會話中未提交事務修改的資料

#提交讀(read committed): 只能讀取到已經提交的資料。oracle等多數資料庫預設都是該級別

#可重複讀(repeated read): 可重複讀。在同一個事務內的查詢都是事務開始時刻一致的，innodb的預設級別。

在SQL標準中，該隔離級別消除了不可重複讀，但是還存在幻象讀

#序列讀(serializable): 完全序列化的讀，每次讀都需要獲得表級共享鎖，讀寫相互都會阻塞。

2.事務併發導致的幾個問題

#髒讀(Drity Read)：某個事務已更新一份資料，另一個事務在此時讀取了同一份資料，由於某些原因，前一個RollBack了操作，則後一個事務所讀取的資料就會是不正確的。

#不可重複讀(Non-repeatable read):在一個事務的兩次查詢之中資料不一致，這可能是兩次查詢過程中間插入了一個事務更新的原有的資料。

#幻讀(Phantom Read):在一個事務的兩次查詢中資料筆數不一致，例如有一個事務查詢了幾列(Row)資料，而另一個事務卻在此時插入了新的幾列資料，先前的事務在接下來的查詢中，就會發現有幾列資料是它先前所沒有的。

隔離級	髒讀可能性	不可重複讀可能性	幻讀可能性	加鎖讀
Read uncommitted	是	是	是	否
Read commited	否	是	是	否
Repeatable read	否	否	是	否
serializable	否	否	否	是

測試：

1. 建測試表，並加入一條資料

CREATE TABLE `isotest` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `name` varchar(50) DEFAULT NULL,

  `score` int(11) DEFAULT NULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

mysql> insert into isotest (name,score) values('wjlcn',80);

2. 建立兩個連線，假定 A、B

* 測試隔離級為 read uncommitted 和 read committed

將A、B 分別設定隔離級為 read uncommitted

mysql> set autocommit=off;

mysql> set session transaction isolation level read uncommitted;

mysql> select @@tx_isolation;

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

| @@tx_isolation   |

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

| READ-UNCOMMITTED |

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

A：

mysql> update isotest set score=90 where name='wjlcn';

Query OK, 1 row affected (0.00 sec)

Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from isotest;

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

| id | name  | score |

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

|  1 | wjlcn |    90 |

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

1 row in set (0.00 sec)

B：

mysql> set session transaction isolation level read uncommitted;                 -----髒讀

Query OK, 0 rows affected (0.00 sec)

mysql> select * from isotest;

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

| id | name  | score |

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

|  1 | wjlcn |    90 |

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

1 row in set (0.00 sec)

mysql> set session transaction isolation level read committed;                 ----不可髒讀

Query OK, 0 rows affected (0.00 sec)

mysql> select * from isotest;

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

| id | name  | score |

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

|  1 | wjlcn |    80 |

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

1 row in set (0.00 sec)

注：在 read uncommitted 隔離級，B上可以髒讀，而在read committed上卻不可以。

A：

mysql> commit;

Query OK, 0 rows affected (0.01 sec)

B：

mysql> select * from isotest;                                      ----不可重複讀，oracle常用的機制

+----+-------+-------+，

| id | name  | score |

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

|  1 | wjlcn |    90 |

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

1 row in set (0.00 sec)

注：當A執行commit後，在read committed上讀到了修改後的資料，驗證了不可重複讀。

* 測試隔離級為 repeatable read

將A、B 隔離級設定為：repeatable read  

mysql> set session transaction isolation level repeatable read;

測試不可重複讀可能性

A：

mysql> update isotest set score=100 where name='wjlcn';

B：

mysql> select * from isotest;

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

| id | name  | score |

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

|  1 | wjlcn |    90 |

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

1 row in set (0.00 sec)

A：

mysql> commit;

Query OK, 0 rows affected (0.00 sec)

B：

mysql> select * from isotest;

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

| id | name  | score |

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

|  1 | wjlcn |    90 |

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

1 row in set (0.01 sec)

注：A事務已經commit，B事務中資料仍舊沒變，驗證了可重複讀。

    A事務已經commit，而B事務查的資料沒變，在B事務沒有提交前，對同一行資料進行update又會怎樣？（另外已經驗證，會在A事務commit後再進行update）

mysql> commit;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from isotest;

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

| id | name  | score |

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

|  1 | wjlcn |   100 |

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

1 row in set (0.00 sec)

注：只有將B事務commit，重新開始新的事務時，才可以查到更改後的資料。

測試幻讀可能性

A：

mysql> insert into isotest (name,score) values('kaka',80);

Query OK, 1 row affected (0.00 sec)

mysql> select * from isotest;

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

| id | name  | score |

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

|  1 | wjlcn |   100 |

|  2 | kaka  |    80 |

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

2 rows in set (0.00 sec)

B：

mysql> select * from isotest;

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

| id | name  | score |

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

|  1 | wjlcn |   100 |

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

1 row in set (0.00 sec)

A：

mysql> commit;

Query OK, 0 rows affected (0.00 sec)

B：

mysql> select * from isotest;

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

| id | name  | score |

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

|  1 | wjlcn |   100 |

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

1 row in set (0.00 sec)

注：此時用

//mysql> select * from isotest lock in share mode;

//mysql> select * from isotest for update;

//可以查到2條記錄，InnoDB提供了這樣的機制，在預設的可重複讀的隔離級別裡，可以使用加鎖讀去查詢最新的資料。

mysql> update isotest set score=180;

Query OK, 2 rows affected (0.00 sec)

Rows matched: 2  Changed: 2  Warnings: 0

mysql> select * from isotest;

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

| id | name  | score |

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

|  1 | wjlcn |   180 |

|  2 | kaka  |   180 |

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

2 rows in set (0.00 sec)

怎麼啦？咋多出一行？ 幻讀來了……

但innodb 在repeatable read可以避免幻讀又是怎麼回事？

MySQL InnoDB的可重複讀並不保證避免幻讀，需要應用使用加鎖next-key locks讀來保證。

* 測試隔離級為 serializable

A:

mysql> set session transaction isolation level serializable;

mysql> update isotest set score=300 where id=4;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1  Changed: 1  Warnings: 0

B:

mysql> set session transaction isolation level serializable;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from isotest;

此時，B事務會被阻塞，因為A事務要個更新id=4 這一行，因此給這行加上了排它鎖，B事務再將給 其加上共享鎖將會失敗。使用A事務commit之後，B事務才會往下執行。