帶你真正理解MySQL資料庫的四種隔離級別!

　　資料庫事務隔離級別主要作用是實現事務工作期間，資料庫操作讀的隔離特性，所謂讀的操作就是將資料頁可以調取到記憶體;

　　然後可以讀取資料頁中相應資料行的能力，並且不同事務之間的資料頁讀操作相互隔離;

　　可以簡單理解為：一個事務在對資料頁中資料行做更新操作時，在沒有更新提交前，另一個事務此時是不能讀取資料頁中資料行內容的;

　　對於資料庫儲存事務隔離級別包括4種，可以透過操作命令檢視獲取當前使用的隔離級別:

　　mysql> select @@transaction_isolation;

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

　　| @@transaction_isolation |

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

　　| REPEATABLE-READ |

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

　　1 row in set (0.00 sec)

　　常用的事務隔離級別型別：

　　型別一：RU(READ-UNCOMMITTED 表示讀未提交)

　　可以讀取到事務未提交的資料，隔離性差，會出現髒讀(當前記憶體讀)，不可重複讀，幻讀問題;

　　型別二：RC(READ-COMMITTED 表示讀已提交)可用 可以讀取到事務已提交的資料，隔離性一般，不會出現髒讀問題，但是會出現不可重複讀，幻讀問題;

　　型別三：RR(REPEATABLE-READ 表示可重複讀)預設

　　可以防止髒讀(當前記憶體讀)，防止不可重複讀問題，防止會出現的幻讀問題，但是併發能力較差;

　　會使用next lock鎖進位制，來防止幻讀問題，但是引入鎖進位制後，鎖的代價會比較高，比較耗費CPU資源，佔用系統效能;

　　型別四：SR(SERIALIZABLE 可序列化)

　　隔離性比較高，可以實現序列化讀取資料，但是事務的併發度就沒有了;

　　這是事務的最*級別，在每條讀的資料上，加上鎖，使之不可能相互衝突

　　事務隔離級別官方連結：https://dev.mysql.com/doc/refman/8.0/en/innodb-transaction-isolation-levels.html

　　常用的事務隔離級別名詞：

　　在解釋分析說明相應的隔離級別名詞前，需要對資料庫事務隔離級別進行調整，以及關閉自動提交功能：

　　# 設定事務隔離級別

　　mysql> set global transaction_isolation='READ-UNCOMMITTED';

　　mysql> set global transaction_isolation='READ-COMMITTED';

　　mysql> set global transaction_isolation='REPEATABLE-READ';

　　# 檢視事務隔離級別

　　mysql> select @@transaction_isolation;

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

　　| @@transaction_isolation |

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

　　| READ-UNCOMMITTED |

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

　　mysql> select @@transaction_isolation;

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

　　| @@transaction_isolation |

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

　　| READ-COMMITTED |

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

　　mysql> select @@transaction_isolation;

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

　　| @@transaction_isolation |

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

　　| REPEATABLE-READ |

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

　　# 臨時關閉自動提交功能：

　　mysql> set global autocommit=0;

　　mysql> select @@autocommit;

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

　　| @@autocommit |

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

　　| 0 |

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

　　建立隔離級別測試資料表：

　　mysql> use oldboy

　　mysql> create table t1 (

　　id int not null primary key auto_increment,

　　a int not null,

　　b varchar(20) not null,

　　c varchar(20) not null

　　) charset=utf8mb4 engine=innodb;

　　mysql> begin;

　　mysql> insert into t1(a,b,c)

　　values

　　(5,'a','aa'),

　　(7,'c','ab'),

　　(10,'d','ae'),

　　(13,'g','ag'),

　　(14,'h','at'),

　　(16,'i','au'),

　　(20,'j','av'),

　　(22,'k','aw'),

　　(25,'l','ax'),

　　(27,'o','ay'),

　　(31,'p','az'),

　　(50,'x','aze'),

　　(60,'y','azb');

　　mysql> commit;

　　-- 確認兩個SQL會話視窗，即不同的事務檢視的資料是否一致的;

　　名詞解讀分析一：髒讀

　　髒讀主要表示在一個事務視窗中，沒有資料修改提交操作前，另一個事務就可以看到記憶體中資料頁的修改;

　　簡單理解：在一個事務視窗中，可以讀取到別人沒有提交的資料資訊;

　　利用隔離級別RU解讀：

　　# 資料庫A會話視窗操作

　　mysql> begin;

　　mysql> update t1 set a=10 where id=1;

　　-- 只是在記憶體層面進行資料頁中資料修改

　　mysql> rollback;

　　-- 進行事務回滾操作

　　# 資料庫B會話視窗操作

　　mysql> begin;

　　mysql> select * from t1 where id=1;

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

　　| id | a | b | c |

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

　　| 1 | 10 | a | aa |

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

　　1 row in set (0.01 sec)

　　-- 在A會話視窗沒提交的事務修改，被B會話視窗查詢到了

　　mysql> select * from t1 where id=1;

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

　　| id | a | b | c |

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

　　| 1 | 5 | a | aa |

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

　　1 row in set (0.01 sec)

　　-- 在A會話視窗進行回滾後，在B視窗查詢的資料又恢復了

　　名詞解讀分析二：不可重複讀

　　不可重複讀表示在一個事務中，利用相同的語句多次查詢，獲取的資料資訊是不同的;

　　利用隔離級別RU解讀：

　　# 資料庫B會話視窗操作

　　mysql> begin;

　　mysql> select * from t1 where id=1;

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

　　| id | a | b | c |

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

　　| 1 | 10 | a | aa |

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

　　1 row in set (0.01 sec)

　　-- 在B會話事務視窗進行資料第一次查詢看到資料資訊：a=10

　　mysql> select * from t1 where id=1;

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

　　| id | a | b | c |

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

　　| 1 | 5 | a | aa |

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

　　1 row in set (0.01 sec)

　　-- 在B會話事務視窗進行資料第二次查詢看到資料資訊：a=5

　　利用隔離級別RC解讀：

　　# 資料庫A會話視窗操作

　　mysql> use oldboy;

　　mysql> begin;

　　mysql> select * from t1 where id=1;

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

　　| id | a | b | c |

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

　　| 1 | 5 | a | aa |

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

　　1 row in set (0.00 sec)

　　-- A視窗事務查詢資訊 = B視窗事務查詢資訊

　　mysql> update t1 set a=10 where id=1;

　　-- A視窗事務進行修改

　　mysql> commit;

　　-- A視窗事務進行提交

　　# 資料庫B會話視窗操作

　　mysql> use oldboy;

　　mysql> begin;

　　mysql> select * from t1 where id=1;

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

　　| id | a | b | c |

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

　　| 1 | 5 | a | aa |

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

　　1 row in set (0.00 sec)

　　-- A視窗事務查詢資訊 = B視窗事務查詢資訊

　　mysql> select * from t1 where id=1;

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

　　| id | a | b | c |

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

　　| 1 | 5 | a | aa |

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

　　1 row in set (0.00 sec)

　　-- B視窗事務查詢資訊，不能看到A視窗事務未提交的資料變化，避免了髒資料問題;

　　mysql> select * from t1 where id=1;

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

　　| id | a | b | c |

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

　　| 1 | 10 | a | aa |

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

　　1 row in set (0.00 sec)

　　-- A視窗事務提交之後，B視窗事務查詢資訊和之前不同了

　　利用隔離級別RR解讀：

　　# 資料庫A會話視窗操作

　　mysql> use oldboy;

　　mysql> begin;

　　mysql> select * from t1;

　　-- 確認初始資料資訊

　　mysql> update t1 set a=10 where id=1;

　　-- A視窗事務進行修改

　　mysql> commit;

　　-- A視窗事務進行提交

　　# 資料庫B會話視窗操作

　　mysql> use oldboy;

　　mysql> begin;

　　mysql> select * from t1;

　　-- 確認初始資料資訊

　　mysql> select * from t1 where id=1;

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

　　| id | a | b | c |

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

　　| 1 | 5 | a | aa |

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

　　1 row in set (0.00 sec)

　　-- B視窗事務查詢資訊，不能看到A視窗事務未提交的資料變化，避免了髒資料問題;

　　mysql> select * from t1 where id=1;

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

　　| id | a | b | c |

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

　　| 1 | 5 | a | aa |

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

　　1 row in set (0.00 sec)

　　-- A視窗事務提交之後，B視窗事務查詢資訊和之前是相同的;

　　-- 在RR級別狀態下，同一視窗的事務生命週期下，每次讀取相同資料資訊是一樣，避免了不可重複讀問題

　　mysql> commit;

　　mysql> select * from t1 where id=1;

　　-- 在RR級別狀態下，同一視窗的事務生命週期結束後，看到的資料資訊就是修改的了

　　名詞解讀分析三：幻讀

　　利用隔離級別RC解讀：

　　# 資料庫A會話視窗操作(重新進入)

　　mysql> use oldboy;

　　mysql> select * from t1;

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

　　| id | a | b | c |

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

　　| 1 | 10 | a | aa |

　　| 2 | 7 | c | ab |

　　| 3 | 10 | d | ae |

　　| 4 | 13 | g | ag |

　　| 5 | 14 | h | at |

　　| 6 | 16 | i | au |

　　| 7 | 20 | j | av |

　　| 8 | 22 | k | aw |

　　| 9 | 25 | l | ax |

　　| 10 | 27 | o | ay |

　　| 11 | 31 | p | az |

　　| 12 | 50 | x | aze |

　　| 13 | 60 | y | azb |

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

　　13 rows in set (0.00 sec)

　　-- 檢視獲取A視窗表中資料

　　mysql> alter table t1 add index idx(a);

　　-- 在A視窗中，新增t1表的a列為索引資訊

　　mysql> begin;

　　mysql> update t1 set a=20 where a<20;

　　-- 在A視窗中，將a<20的資訊均調整為20

　　mysql> commit;

　　-- 在A視窗中，進行事務提交操作，是在B視窗事務沒有提交前

　　mysql> mysql> select * from t1;

　　-- 在A視窗中，檢視資料資訊，希望看到的a是沒有小於20的，但是結果看到了a存在等於10的(即出現了幻讀)

　　# 資料庫B會話視窗操作(重新進入)

　　mysql> use oldboy;

　　mysql> select * from t1;

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

　　| id | a | b | c |

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

　　| 1 | 10 | a | aa |

　　| 2 | 7 | c | ab |

　　| 3 | 10 | d | ae |

　　| 4 | 13 | g | ag |

　　| 5 | 14 | h | at |

　　| 6 | 16 | i | au |

　　| 7 | 20 | j | av |

　　| 8 | 22 | k | aw |

　　| 9 | 25 | l | ax |

　　| 10 | 27 | o | ay |

　　| 11 | 31 | p | az |

　　| 12 | 50 | x | aze |

　　| 13 | 60 | y | azb |

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

　　13 rows in set (0.00 sec)

　　-- 檢視獲取B視窗表中資料

　　mysql> begin;

　　mysql> insert into t1(a,b,c) values(10,'A','B')

　　-- 在B視窗中，插入一條新的資料資訊 a=10

　　mysql> commit;

　　-- 在B視窗中，進行事務提交操作

　　利用隔離級別RR解讀：

　　# 資料庫A會話視窗操作

　　mysql> use oldboy;

　　mysql> select * from t1;

　　-- 檢視獲取A視窗表中資料

　　mysql> alter table t1 add index idx(a);

　　-- 在A視窗中，新增t1表的a列為索引資訊

　　mysql> begin;

　　mysql> update t1 set a=20 where a>20;

　　-- 在A視窗中，將a>20的資訊均調整為20

　　# 資料庫B會話視窗操作

　　mysql> use oldboy;

　　mysql> select * from t1;

　　-- 檢視獲取B視窗表中資料

　　mysql> begin;

　　mysql> insert into t1(a,b,c) values(30,'sss','bbb');

　　-- 在B視窗中，插入一條新的資料資訊 a=30，但是語句執行時會被阻塞，沒有反應;

　　mysql> show processlist;

　　-- 在C視窗中，檢視資料庫連線會話資訊，insert語句在執行，等待語句超時(預設超時時間是50s)

　　-- 因為此時在RR機制下，建立了行級鎖(阻塞修改)+間隙鎖(阻塞區域間資訊插入)=next lock

　　-- 區域間隙鎖 < 左閉右開(可用臨界值) ; 區域間隙鎖 > 左開右閉(不可用臨界值)