數(shù)據(jù)庫并發(fā)的對同一批數(shù)據(jù)進行增刪改,就可能會出現(xiàn)我們所說的臟寫、臟讀、不可重復讀、幻讀等一系列問題。MySQL提供了一系列機制來解決事務并發(fā)問題,比如事務隔離、鎖機制、MVCC多版本并發(fā)控制機制。今天來探究一下事務隔離機制。
隔離級別 | 臟讀(Dirty Read) | 不可重復讀(NonRepeatable Read) | 幻讀(Phantom Read) |
讀未提交(Read uncommitted) | 可能 | 可能 | 可能 |
讀已提交(Read committed) | 不可能 | 可能 | 可能 |
可重復讀(Repeatable Read) | 不可能 | 不可能 | 可能 |
串行化(Serializable) | 不可能 | 不可能 | 不可能 |
MySQL提供了上面四種隔離級別,隔離越嚴格,可能出現(xiàn)的問題就越少,但付出的性能代價就越大,默認的隔離級別是可重復讀。下面使用客戶端進行操作進行驗證。
先加創(chuàng)建一張表和數(shù)據(jù)
CREATE TABLE `account` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `balance` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; INSERT INTO `account` (`id`, `balance`) VALUES (1, 500), (2, 600), (3, 200);
連接客戶端,查看隔離級別,可以看到是可重復讀:
MySQL [test]> show variables like 'tx_isolation'; +---------------+-----------------+ | Variable_name | Value | +---------------+-----------------+ | tx_isolation | REPEATABLE-READ | +---------------+-----------------+
AB客戶端都執(zhí)行set tx_isolation='read-uncommitted';設置隔離級別為讀未提交。
A客戶端開啟事務:start transaction;查詢數(shù)據(jù):select * from account;
B客戶端開啟事務:start transaction;更新數(shù)據(jù):update account set balance = balance - 100 where id = 1;此時事務未提交
A客戶端再次查詢數(shù)據(jù):select * from account; 此時看到兩次查詢的數(shù)據(jù)已經(jīng)不一樣了
在B沒提交前A就讀到了B更新的數(shù)據(jù),此時如果B回滾,那么A那邊就是臟數(shù)據(jù)。這種情況就是讀未提交造成的臟讀。用讀已提交隔離級別可以解決。
使用commit命令把AB客戶端的事務提交。
AB客戶端都執(zhí)行 set tx_isolation='read-committed'; 設置隔離級別為讀已提交。
A客戶端開啟事務:start transaction;查詢數(shù)據(jù):select * from account;
B客戶端開啟事務:start transaction;更新數(shù)據(jù):update account set balance = balance - 100 where id = 1;此時事務未提交
A客戶端再次查詢數(shù)據(jù):select * from account; 此時看到A客戶端兩次查詢數(shù)據(jù)一致,未出現(xiàn)臟讀情況
此時B客戶端事務提交:commit;
A客戶端再次查詢數(shù)據(jù):select * from account; 此時看到A客戶端查詢數(shù)據(jù)已經(jīng)發(fā)生了變化,這就是不可重復讀。
可重復讀測試:
AB客戶端都執(zhí)行 set tx_isolation='repeatable-read'; 設置隔離級別為可重復讀。
A客戶端開啟事務:start transaction;查詢數(shù)據(jù):select * from account;
B客戶端開啟事務:start transaction;更新數(shù)據(jù):update account set balance = balance - 100 where id = 1; commit提交事務
A客戶端再次查詢數(shù)據(jù):select * from account; 此時看到A客戶端兩次查詢數(shù)據(jù)一致,重復讀取數(shù)據(jù)一致。
A客戶端執(zhí)行更新語句:update account set balance = balance - 50 where id = 1;
A客戶端再次查詢數(shù)據(jù):select * from account; 此時看到id=1的這條數(shù)據(jù)是B客戶端更新之后的數(shù)據(jù)-50,數(shù)據(jù)的一致性沒有被破壞
B客戶端重新開啟事務,插入一條數(shù)據(jù):insert into account(id,balance) values (4,1000); commit提交事務;
A客戶端查詢,和上次結果一致
A客戶端執(zhí)行:update account set balance = balance - 100 where id = 4; 更新B客戶端新插入的數(shù)據(jù),能執(zhí)行成功,再次查詢所有數(shù)據(jù),能插到id=4的數(shù)據(jù),出現(xiàn)幻讀。
# A客戶端執(zhí)行過程:# 設置隔離級別可重復度MySQL [test]> set tx_isolation='repeatable-read'; Query OK, 0 rows affected, 1 warning (0.00 sec) # 開啟事務 MySQL [test]> start transaction; Query OK, 0 rows affected (0.00 sec) # 查詢所有數(shù)據(jù) MySQL [test]> select * from account; +----+---------+ | id | balance | +----+---------+ | 1 | 300 | | 2 | 600 | | 3 | 200 | +----+---------+ 3 rows in set (0.00 sec) # 再次查詢驗證兩次結果是否一致 MySQL [test]> select * from account; +----+---------+ | id | balance | +----+---------+ | 1 | 300 | | 2 | 600 | | 3 | 200 | +----+---------+ 3 rows in set (0.00 sec) # 在B客戶端插入數(shù)據(jù)之后,此次A客戶端不能查詢到 MySQL [test]> select * from account; +----+---------+ | id | balance | +----+---------+ | 1 | 150 | | 2 | 600 | | 3 | 200 | +----+---------+ 3 rows in set (0.00 sec) # A客戶端更新B客戶端插入的數(shù)據(jù),發(fā)現(xiàn)可以更新成功 MySQL [test]> update account set balance = balance + 1000 where id = 4; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 # 再次查詢,能查詢到數(shù)據(jù),出現(xiàn)幻讀 MySQL [test]> select * from account; +----+---------+ | id | balance | +----+---------+ | 1 | 400 | | 2 | 600 | | 3 | 200 | | 4 | 2000 | +----+---------+ 4 rows in set (0.00 sec) # 提交事務 MySQL [test]> commit; Query OK, 0 rows affected (0.01 sec)
# B客戶端執(zhí)行過程:設置隔離級別可重復讀 MySQL [test]> set tx_isolation='repeatable-read'; Query OK, 0 rows affected, 1 warning (0.00 sec) # 開啟事務 MySQL [test]> start transaction; Query OK, 0 rows affected (0.00 sec) # 更新數(shù)據(jù),直接提交 MySQL [test]> update account set balance = balance - 100 where id = 1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 MySQL [test]> commit; Query OK, 0 rows affected (0.01 sec) # 再次開啟事務 MySQL [test]> start transaction; Query OK, 0 rows affected (0.00 sec) # 插入一條數(shù)據(jù) MySQL [test]> insert into account(id,balance) values (4,1000); Query OK, 1 row affected (0.01 sec) MySQL [test]> commit; Query OK, 0 rows affected (0.00 sec)
最后一種串行化:set tx_isolation='serializable';可自行驗證,能解決上面所有問題,但是一般不會用到的,保證一致性的同時帶來的是性能大幅度下降,并發(fā)性極低,默認是可重復讀。
通過隔離級別在一定程度上能處理事務并發(fā)的問題,除此之外還有其他的手段,后續(xù)會再次探究。
以上就是全面解析MySQL中的隔離級別的詳細內(nèi)容,更多關于MySQL 隔離級別的資料請關注腳本之家其它相關文章!