Mysql-InnoDB 鎖機制與測試(20200401_20200404)
1. 死鎖
1.1 死鎖概念:
死鎖是指兩個或兩個以上的進程在執(zhí)行過程中,因爭奪資源而造成的一種互相等待的現(xiàn)象,若無外力作用,它們都將無法推進下去.此時稱系統(tǒng)處于死鎖狀態(tài)或系統(tǒng)產(chǎn) 生了死鎖,這些永遠在互相等待的進程稱為死鎖進程.表級鎖不會產(chǎn)生死鎖.所以解決死鎖主要還是針對于最常用的InnoDB
1.2 mysql處理死鎖的方式
- 等待泌参，直到超時(innodb_lock_wait_timeout=50s)憔四。
- 發(fā)起死鎖檢測，主動回滾一條事務(wù)惰赋，讓其他事務(wù)繼續(xù)執(zhí)行(innodb_deadlock_detect=on)炉菲。
1.3 死鎖檢測
死鎖檢測的原理是構(gòu)建一個以事務(wù)為頂點疲陕、鎖為邊的有向圖鞭盟，判斷有向圖是否存在環(huán)，存在即有死鎖格二。
1.4 回滾機制
檢測到死鎖之后，選擇插入更新或者刪除的行數(shù)最少的事務(wù)回滾竣蹦，基于 INFORMATION_SCHEMA.INNODB_TRX 表中的 trx_weight 字段來判斷顶猜。如果插入更新或者刪除的行數(shù)一樣則回滾后面執(zhí)行的那條事務(wù)。
1.5 測試表及數(shù)據(jù)：下面不再展示
【p_transaction 表】
id int(11) NO PRI auto_increment
count int(11) YES
version int(11) YES 0
---
id count version
1 1 1
6 11 0
10 14 0
11 14 0
12 10 0
13 15 0
14 16 0
15 17 0
16 18 0
21 22 0
24 24 0
25 25 0
26 26 0
27 27 0
28 28 0
29 29 0
30 300 0
31 301 0
1.6 測試用例
-- 查看日志文件設(shè)置狀態(tài)
show variables like "%innodb_flush_log_at_trx_commit%";
-- 更改日志文件設(shè)置狀態(tài)
set @@global.innodb_flush_log_at_trx_commit = 0; -- 0痘括，1长窄，2
-- 鎖等待時間
show VARIABLES like "%innodb_lock_wait_timeout%";
-- 死鎖自動回滾
show VARIABLES like "%innodb_deadlock_detect%";
-- 死鎖測試
begin;
select * from p_transaction where id = 32 for update;
update p_transaction set count = 1 where id = 1;
insert into p_transaction (id, count) values (32, 300);
commit;
rollback;
2. MVCC 樂觀鎖
2.1 英文全稱為Multi-Version Concurrency Control,翻譯為中文即「多版本并發(fā)控制」滔吠。
MVCC使得InnoDB的事務(wù)隔離級別下執(zhí)行一致性讀操作有了保證，換言之挠日，就是 為了查詢一些正在被另一個事務(wù)更新的行疮绷， 并且可以看到它們被更新之前的值。這是一個可以用來增強并發(fā)性的強大的技術(shù)嚣潜，因為這樣一來的話查詢就不用等 待另一個事務(wù)釋放鎖冬骚。這項技術(shù)在數(shù)據(jù)庫領(lǐng)域并不是普遍使用的。一些其它的數(shù)據(jù)庫產(chǎn)品懂算， 以及mysql其它的存儲引擎并不支持它只冻。
2.2 mysql的innodb表除了實際的數(shù)據(jù)之外，還會加上3個隱藏的字段计技，如下:
實際數(shù)據(jù) | create_no(創(chuàng)建版本號或者創(chuàng)建時間) | update_no(每次修改的版本號或者修改時間) | delete_no(刪除版本號或者刪除時間)
- insert:當(dāng)我們新增一條數(shù)據(jù)時喜德，這條數(shù)據(jù)會加上創(chuàng)建的版本號
- update:修改當(dāng)前的字段，每修改一次數(shù)據(jù)垮媒，修改版本號都會依次增加一次
- delete:刪除當(dāng)前的數(shù)據(jù)舍悯，其實并不會真實的刪除，他會先在刪除版本號字段記錄下刪除的版本號睡雇，在過了一段時間后會進行清除或者刷新
2.3 MVCC是樂觀鎖的一種實現(xiàn)方式萌衬，但并不是MVCC就等于樂觀鎖。
2.4 測試用例
-- 樂觀鎖測試
select count, version from p_transaction where id = 1;
update p_transaction set count = count - 1,version = version + 1 where id = 1 and version = 0;
3. 樂觀鎖與悲觀鎖：樂觀鎖與悲觀鎖都屬于是一種思想入桂，而非實際的 mysql 鎖機制奄薇。
樂觀鎖：不使用鎖機制
悲觀鎖：只要使用了鎖機制都屬于悲觀鎖
3.1 測試用例
-- 悲觀鎖測試
begin;
select count from p_transaction where id = 1;
update p_transaction set count = count - 1 where id = 1;
commit;
rollback;
4. 間隙鎖
當(dāng)我們用范圍條件而不是相等條件檢索數(shù)據(jù)，并請求共享或排他鎖時抗愁，InnoDB會給符合條件的已有數(shù)據(jù)記錄的索引項加鎖;對于鍵值在條件范圍內(nèi)但不存在的 記錄馁蒂，叫做“間隙(GAP)”，InnoDB也會對這個“間隙”加鎖蜘腌，這種鎖機制就是所謂的間隙鎖(NEXT-KEY)鎖沫屡。間隙鎖類次與頁級鎖，但是實際是行級鎖撮珠。
4.1 測試用例
-- 悲觀鎖測試
begin;
select count from p_transaction where id = 1;
update p_transaction set count = count - 1 where id = 1;
commit;
rollback;
5. 行級鎖升級為表級鎖
InnoDB 行級鎖是通過給索引上的索引項加鎖來實現(xiàn)的沮脖，InnoDB行級鎖只有通過索引條件檢索數(shù)據(jù)，才使用行級鎖;否則芯急，InnoDB使用表鎖 在不通過索引(主 鍵)條件查詢的時候勺届，InnoDB是表鎖而不是行鎖。
5.1 測試用例
-- 間隙鎖測試
begin;
select * from p_transaction where id >=1 and id <= 10 for update;
select * from p_transaction where id between 1 and 10 for update;
-- 排它鎖
select * from p_transaction where id = 6 for update;
-- 共享鎖
select * from p_transaction where id = 6 lock in share mode;
-- 無鎖
select * from p_transaction where id = 6;
update p_transaction set count = 10 where id = 6;
update p_transaction set count = 10 where id = 12;
commit;
rollback;
6. 事務(wù)的使用建議
- 控制事務(wù)大小娶耍，減少鎖定的資源量和鎖定時間長度免姿。
- 人所有的數(shù)據(jù)檢索都通過索引來完成，從而避免因為無法通過索引加鎖而升級為表鎖榕酒。
- 減少基于范圍的數(shù)據(jù)檢索過濾條件胚膊，避免因為間隙鎖帶來的負面影響而鎖定了不該鎖定的數(shù)據(jù)故俐。
- 在業(yè)務(wù)條件允許下，盡量使用較低隔離級別的事務(wù)隔離紊婉。減少隔離級別帶來的附加成本药版。
- 合理使用索引，讓innodb在索引上面加鎖的時候更加準確喻犁。
- 在應(yīng)用中盡可能做到訪問的順序執(zhí)行
- 如果容易死鎖槽片，就可以考慮使用表鎖來減少死鎖的概率
