111
MySQL鎖係列（五）之 隔離級別
一、隔離級別
事務的隔離級別有4種: SQL-1992 ，但是我隻想介紹其中兩種，因為其他的兩個根本就用不上
1.1 什麼叫一致性鎖定讀 和 一致性非鎖定讀
- 一致性鎖定讀
1. 讀數據的時候，會去加S-lock、x-lock
2. eg：select ... for update , select ... lock in share mode
3. dml語句
- 一致性非鎖定讀
1. 讀數據的時候，不加任何的鎖，快照讀（snapshot read）
2. eg: select ... 最普通的查詢語句
1.2 什麼是幻讀(不可重複讀)
- 概念
一個事務內的同一條【一致性鎖定讀】SQL多次執行，讀到的結果不一致，我們稱之為幻讀。
- 實戰
* set global tx_isolation='READ-COMMITTED'
> 事務一:
root:test> begin;select * from lc for update;
+------+
| id |
+------+
| 1 |
| 2 |
+------+
> 事務二：
root:test>begin; insert into lc values(3);
Query OK, 1 row affected (0.00 sec)
root:test> commit ;
Query OK, 0 rows affected (0.00 sec)
> 事務一：
root:test> select * from lc for update;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
+------+
3 rows in set (0.00 sec)
* 同一個事務一中，同一條select * from lc for update (一致性鎖定讀) 執行兩次，得到的結果不一致，說明產生了幻讀
* 同一個事務一中，同一條select * from lc (一致性非鎖定讀) 執行兩次，得到的結果不一致，說明產生了幻讀
* 我們姑且認為，幻讀和不可重複讀為一個概念，實際上也差不多一個概念。
1.3 什麼是髒讀
1. 這個大家都很多好理解，就是事務一還沒有提交的事務，卻被事務二讀到了，這就是髒讀
1.4 repeatable-read（RR）
- 什麼是RR
1. 學名： 可重複讀
2. 顧名思義：一個事務內的同一條【一致性鎖定讀】SQL多次執行，讀到的結果一致，我們稱之為可重複讀。
3. 解決了幻讀的問題
1.5 read-committed （RC）
* 學名：可提交讀
* 顧名思義: 隻要其他事務提交了，我就能讀到
* 解決了髒讀的問題，沒有解決幻讀的問題
二、隔離級別是如何實現的
就拿上麵那個簡單的例子來佐證好了
環境
dba:lc_4> show create table lc;
+-------+--------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+--------------------------------------------------------------------------------------------------------+
| lc | CREATE TABLE `lc` (
`id` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+--------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
dba:lc_4> select * from lc;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
+----+
3 rows in set (0.00 sec)
2.1 RR
RR 如何解決幻讀問題？
RR 的鎖算法：next-key lock
- 解決幻讀的案例
dba:lc_4> set tx_isolation='repeatable-read';
Query OK, 0 rows affected (0.00 sec)
dba:lc_4> select * from lc for update ;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
+----+
3 rows in set (0.00 sec)
這時候，查看下鎖的情況：
------------
TRANSACTIONS
------------
Trx id counter 133588361
Purge done for trx's n:o < 133588356 undo n:o < 0 state: running but idle
History list length 892
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421565826150000, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421565826149088, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 133588360, ACTIVE 4 sec
2 lock struct(s), heap size 1136, 4 row lock(s)
MySQL thread id 135, OS thread handle 140001104295680, query id 1176 localhost dba cleaning up
TABLE LOCK table `lc_4`.`lc` trx id 133588360 lock mode IX
RECORD LOCKS space id 289 page no 3 n bits 72 index PRIMARY of table `lc_4`.`lc` trx id 133588360 lock_mode X --next key lock ， 鎖記錄和範圍
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;; --next-key lock, 鎖住正無窮大
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 80000001; asc ;; --next-key lock, 鎖住1和1之前的區間，包括記錄 (negtive,1]
1: len 6; hex 000007f6657e; asc e~;;
2: len 7; hex e5000040220110; asc @" ;;
Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 80000002; asc ;; --next-key lock, 鎖住2和1之前的區間，包括記錄 (1,2]
1: len 6; hex 000007f6657f; asc e ;;
2: len 7; hex e6000040330110; asc @3 ;;
Record lock, heap no 4 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 80000003; asc ;; --next-key lock, 鎖住3和2之間的區間，包括記錄 (2,3]
1: len 6; hex 000007f66584; asc e ;;
2: len 7; hex e9000040240110; asc @$ ;;
* 總結下來就是：
1. (negtive bounds,1] ， (1,2] ， (2,3]，(3,positive bounds) --鎖住的記錄和範圍，相當於表鎖
2. 這時候，session 2 插入任何一條記錄，會被鎖住，所以幻讀可以避免，尤其徹底解決了幻讀的問題
2.2 RC
RC 的鎖算法：record locks
幻讀對線上影響大嗎？ oracle默認就是RC隔離級別
- 不解決幻讀的案例
dba:lc_4> set tx_isolation='read-committed';
Query OK, 0 rows affected (0.00 sec)
dba:lc_4> select * from lc for update ;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
+----+
3 rows in set (0.00 sec)
* 查看鎖的信息如下
------------
TRANSACTIONS
------------
Trx id counter 133588362
Purge done for trx's n:o < 133588356 undo n:o < 0 state: running but idle
History list length 892
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421565826150000, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421565826149088, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 133588361, ACTIVE 3 sec
2 lock struct(s), heap size 1136, 3 row lock(s)
MySQL thread id 138, OS thread handle 140001238955776, query id 1192 localhost dba cleaning up
TABLE LOCK table `lc_4`.`lc` trx id 133588361 lock mode IX
RECORD LOCKS space id 289 page no 3 n bits 72 index PRIMARY of table `lc_4`.`lc` trx id 133588361 lock_mode X locks rec but not gap --記錄鎖，隻鎖記錄
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 80000001; asc ;; -- 記錄鎖，鎖住1
1: len 6; hex 000007f6657e; asc e~;;
2: len 7; hex e5000040220110; asc @" ;;
Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 80000002; asc ;; -- 記錄鎖，鎖住2
1: len 6; hex 000007f6657f; asc e ;;
2: len 7; hex e6000040330110; asc @3 ;;
Record lock, heap no 4 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 80000003; asc ;; -- 記錄鎖，鎖住3
1: len 6; hex 000007f66584; asc e ;;
2: len 7; hex e9000040240110; asc @$ ;;
* 總結下來
1. 鎖住的是哪些？ [1,2,3] 這些記錄被鎖住
2. 那麼session 2 除了1，2，3 不能插入之外，其他的記錄都能，比如； insert into lc select 4 , 那麼再次select * from lc for udpate 的時候，就是4條記錄了，由此產生幻讀
2.3 RC vs RR 安全性
- RC 和 binlog
1. RC 模式，binlog 必須使用Row 模式
- 為什麼RC的binlog必須使用Row
* session 1:
begin;
delete from tb_1 where id > 0;
* session 2:
begin;
insert into tb_1 select 100;
commit;
* session 1:
commit;
* 如果RC模式下的binlog是statement模式，結果會是怎麼樣呢？
master : 結果是 100
slave : 結果是 空
這樣就導致master和slave結果不一致了: 因為在slave上，先執行insert into tb_1 select 100; 再執行delete from tb_1 where id > 0; 當然等於空咯
* 如果RC模式下的binlog是ROW模式，結果會是怎麼樣呢？
master : 結果是 100
slave : 結果是 100
主從結果一致，因為binlog是row模式，slave並不是邏輯的執行上述sql，而記錄的都是行的變化
2.4 總結
- RC 的優點
1. 由於降低了隔離級別，那麼實現起來簡單，對鎖的開銷小，基本上不會有Gap lock，那麼導致死鎖和鎖等待的可能就小
2. 當然RC也不是完全沒有Gap lock，當purge 和 唯一性索引存在的時候會產生特殊的Gap lock，這個後麵會具體講
- RC 的缺點
1. 會有幻讀發生
2. 事務內的每條select，都會產生新的read-view，造成資源浪費
- RR 的優點
1. 一個事務，隻有再開始的時候才會產生read-view，有且隻有一個，所以這塊消耗比較小
2. 解決了幻讀的問題, 實現了真正意義上的隔離級別
- RR 的缺點
1. 由於RR的實現，是通過Gap-lock實現，經常會鎖定一個範圍，那麼導致死鎖和所等待的概率非常大
- 我們的選擇
一般我們生產環境的標配，都是RC+Row 模式，誰用誰知道哦
最後更新：2017-06-14 18:01:53