一 綜述
MySQL 的鎖機(jī)制相較其他的數(shù)據(jù)庫比較簡單，最顯著的特點(diǎn)是不同的存儲引擎支持不同的鎖機(jī)制落剪。主要有三種類型的鎖
- 表級鎖：開銷小睁本，加鎖快；不會出現(xiàn)死鎖忠怖；鎖定粒度大呢堰，發(fā)生沖突的概率最高，并發(fā)度最低凡泣。支持表級鎖的代表是 MyISAM 引擎枉疼。
- 行級鎖：開銷大，加鎖慢鞋拟；會出現(xiàn)死鎖骂维；鎖定粒度最小，發(fā)生鎖沖突的概率最低，并發(fā)度最高。支持行級鎖的代表是 InnoDB 引擎樊展。
- 頁面鎖：開銷和加鎖時間介于表級鎖和行級鎖之間蹬屹；會出現(xiàn)死鎖；鎖定粒度介于表級鎖和行級鎖之間。支持頁面鎖代表是 BDB 引擎。
就鎖本身的特點(diǎn)來說，各有優(yōu)勢乖杠。所以具體采用什么級別的鎖還是要結(jié)合實(shí)際的應(yīng)用場景。比如大量查詢?yōu)橹鞒纬桑倭恳运饕龡l件的更新數(shù)據(jù)應(yīng)用適合表級鎖胧洒；而又大量按索引執(zhí)行更新操作的應(yīng)用則適合行級鎖畏吓。
參考文章
InnoDB 中事務(wù)隔離級別與鎖的關(guān)系-美團(tuán)點(diǎn)評技術(shù)博客
二 MyISAM 表鎖
1 查詢表級鎖爭用情況
show status like 'table%';
如果結(jié)果字段 Table_locks_waited 的值比較高，則說明存在比較嚴(yán)重的鎖爭用卫漫。
2 表級鎖的鎖模式
- 表共享鎖(Table Read Lock)
- 表獨(dú)占寫鎖(Table Write Lock)
鎖之間是兼容情況可以用一句話闡述：共享鎖與任何鎖兼容菲饼，而獨(dú)占鎖與任何鎖都不兼容。
因此對 MyISAM 的讀操作不會阻塞其他用戶對同一張表的讀請求汛兜，但是會阻塞對同一個表的寫請求巴粪；對應(yīng)的寫操作會阻塞其他用戶對同一張表的讀/寫操作。
表的讀操作與寫操作之間粥谬，以及寫操作與寫操作之間都是串行的肛根。 當(dāng)一個線程獲得一個表的寫鎖之后，只有持有鎖的線程可以對表進(jìn)行更新操作漏策，其他線程的讀派哲、寫操作都會等待直到鎖被釋放。
2.1 應(yīng)用示例
這是一個“寫鎖阻塞讀操作”的例子
session1 中對表 test_lock 加鎖并寫入一條數(shù)據(jù)
lock table test_lock write;
insert into test_lock (id, value) VALUES (1, 'fail');
session2 在 session1 操作過程中(此時并沒有釋放鎖)執(zhí)行一次查詢操作
SELECT * FROM test_lock;
結(jié)果發(fā)現(xiàn) session2 的操作進(jìn)入阻塞狀態(tài)掺喻。此時再切換到 session1 將持有的寫鎖釋放
unlock table test_lock;
釋放鎖后 session2 的查詢操作得到結(jié)果芭届。
2.2 如何加表鎖
實(shí)際上MyISAM在執(zhí)行查詢語句的時候會 自動給涉及到的所有表加讀鎖，在執(zhí)行更新操作的時候 會自動給涉及的表加寫鎖感耙。即這個過程并不需要用戶干預(yù)褂乍，因此用戶 一般不需要使用前面示例中的語句來顯式的加鎖。
在一些特殊的情況下需要我們顯式加鎖即硼，不過這個是為了模擬事務(wù)操作逃片。比如兩張表 order 與 order_detail 都有一個金額總計(jì)字段，現(xiàn)在需要檢查匹配兩個表的金額是否一致只酥。
lock tables orders read local, order_detail read local;
select sum(total) from orders;
select sum(subtotal) from orders;
unlock tables;
在用 LOCK TABLE 給表顯式加鎖時褥实，必須同時獲取到所有涉及表的鎖，并且 MyISAM 不支持鎖升級裂允。
另外损离，鎖定一個表，還要順帶鎖定這個表的所有別名绝编，否則會報錯僻澎。
lock table test_lock as a,test_lock as b;
2.3 并發(fā)插入
MyISAM表的讀和寫操作是串行的，這是總體上來看的十饥。加上部分限制怎棱，MyISAM也支持在讀的同時并發(fā)寫。
存儲引擎有兩個系統(tǒng)變量
concurrent_insert
這個值的可選值有0,1,2三個绷跑。
- 0 設(shè)置為 0 表示 不允許并發(fā)插入
- 1 設(shè)置為 1 表示 如果表中沒有空洞，則允許在一個線程讀表的同時凡资，另一個線程從表尾插入砸捏。 這也是 MyISAM 的默認(rèn)選項(xiàng)谬运。
- 2 設(shè)置為 2 表示 不論表中有沒有空洞，都允許從表尾進(jìn)行并發(fā)插入操作垦藏。
這個特性可以用來處理應(yīng)用中對同一表查詢和插入操作的爭用梆暖。比如將這個系統(tǒng)變量設(shè)置為 2 的時候，總是允許并發(fā)插入掂骏，同時通過定期在系統(tǒng)空閑時執(zhí)行 OPTIMIZE TABLE 語句來整理空間碎片轰驳，收回空洞。
2.4 MyISAM 的鎖調(diào)度
一個進(jìn)程在請求某個 MyISAM 表的讀鎖弟灼，另一個線程此時也正在請求同一個表的寫鎖级解，這種情況下總是寫鎖請求先獲得鎖，哪怕按照排隊(duì)順序讀鎖在寫鎖之前田绑。這是 MyISAM 表不適合有大量更新操作和查詢操作原因勤哗。
不過可以通過一些設(shè)置來調(diào)節(jié)這些調(diào)度行為
- 指定啟動參數(shù) low-priority-updates，讓引擎默認(rèn)給予 讀請求優(yōu)先的權(quán)利掩驱。
- 執(zhí)行命令 SET LOW_PRIORITY_UPDATES = 1芒划，使該連接發(fā)出的更新其請求優(yōu)先級降低。
- 指定 INSERT, UPDATE, DELETE 語句的 LOW_PRIORITY 屬性欧穴，降低該語句的優(yōu)先級民逼。
這三種辦法都是要么更新優(yōu)先，要么查詢優(yōu)先的方法涮帘，但是還是可以用來解決查詢相對重要的場景中讀鎖等待時間過長的問題拼苍。
除此之外還有一種調(diào)度調(diào)節(jié)方法
max_write_lock_count
該參數(shù)也可以用來調(diào)節(jié)讀寫沖突。設(shè)置一個值后焚辅，MySQL 在一個表的讀鎖達(dá)到這個值后映屋，暫時將寫請求的優(yōu)先級降低，給讀進(jìn)程一定獲得鎖的機(jī)會同蜻。
另外棚点，一些需要長時間運(yùn)行的查詢操作應(yīng)該盡量避免，如果一定要執(zhí)行湾蔓，可以安排到數(shù)據(jù)庫壓力不那么大的時間短執(zhí)行瘫析，比如半夜。
三 InnoDB 鎖問題
首先 InnoDB 與 MyISAM 之間最大的區(qū)別是
- 支持事務(wù)( TRANSACTION )
- 采用了行級鎖
3.1 獲取 InnoDB 的行鎖爭用情況
show status like 'innoDB_row_lock%'
// output
Innodb_row_lock_current_waits 0
Innodb_row_lock_time 181621
Innodb_row_lock_time_avg 321
Innodb_row_lock_time_max 20824
Innodb_row_lock_waits 565
| 字段名 | 含義 |
|---|---|
| Innodb_row_lock_current_waits | 當(dāng)前處于等待狀態(tài)的鎖數(shù)量 |
| Innodb_row_lock_time | 啟動到現(xiàn)在鎖定的總時間長度 |
| Innodb_row_lock_time_avg | 平均每次鎖定的時長(ms) |
| Innodb_row_lock_time_max | 最長的一次鎖定時間(ms) |
| Innodb_row_lock_waits | 啟動到現(xiàn)在總計(jì)是鎖定次數(shù) |
如果鎖爭用比較嚴(yán)重默责，那么字段 Innodb_row_lock_current_waits 與 Innodb_row_lock_time_avg 值都會比較高贬循。具體的調(diào)優(yōu)手段和參數(shù)這里暫且不表，我們繼續(xù)了解InnoDB鎖與其事務(wù)間的關(guān)系桃序。
背景知識
3.2 行鎖模式以及加鎖方法
InnoDB 有以下兩種類型的鎖
- 共享鎖(S)：允許一個事務(wù)去讀一行杖虾，阻止其他事務(wù)獲得相同數(shù)據(jù)集的排他鎖。
- 排他鎖(X)：允許獲得排他鎖的事務(wù)更新數(shù)據(jù)媒熊，阻止其他事務(wù)取得相同數(shù)據(jù)集的共享讀鎖和排他寫鎖奇适。
除此之外還有兩種意向鎖
- 意向共享鎖(IS)：事務(wù)打算給數(shù)據(jù)行加行共享鎖坟比，事務(wù)在給一個數(shù)據(jù)行加共享鎖前必須先取得該表的IS鎖。
- 意向排他鎖(IX)：事務(wù)打算給數(shù)據(jù)行加行排他鎖嚷往，事務(wù)在給一個數(shù)據(jù)行加排他鎖前必須先取得該表的IX鎖葛账。
意向鎖是InnoDB自動加的，不需要用戶干預(yù)皮仁。UPDATE/DELETE/INSERT 語句 InnoDB 會自動給涉及的數(shù)據(jù)集加排他鎖籍琳。普通的 SELECT 語句 InnoDB 不加鎖。不過可以在語句中顯式的給數(shù)據(jù)集加共享鎖或者排他鎖贷祈。
在 RC（read commited）級別中趋急，數(shù)據(jù)的讀取都是不加鎖的，但是數(shù)據(jù)的寫入付燥、修改和刪除是需要加鎖的宣谈。
InnoDB 行鎖是通過給索引項(xiàng)加鎖實(shí)現(xiàn)的，如果沒有索引键科，InnoDB 將通過隱藏的聚簇索引來對記錄加鎖闻丑。 InnoDB 行鎖有三種情形：
- Record Lock：對索引項(xiàng)加鎖
- Gap Lock：對索引之間的“間隙”、第一條記錄前的“間隙”或最后一條記錄的“間隙”加鎖勋颖。
- Next-key lock：前兩種的組合嗦嗡，對記錄及其前面的間隙加鎖。
如果不通過索引條件檢索數(shù)據(jù)饭玲，那么 InnoDB 將對表中的所有記錄加鎖侥祭，實(shí)際效果就和表鎖一樣了。
如下是一些注意事項(xiàng)及說明
1. 在不通過索引條件查詢時茄厘，InnoDB 會鎖定表中的所有記錄
// session1
set autocommit = 0;
select * from test where col1 = 1 for update;
// session2
select * from test where col1 = 2 for update;
// waiting...
這是一個串行操作矮冬，在session1給表的col1字段的某條記錄加了排他鎖，在理想情況下session2的操作應(yīng)該不會受到影響次哈。
如果col1字段沒有設(shè)置索引的話胎署，這個阻塞操作就一定會發(fā)生。因?yàn)闄z索操作沒有走到索引會導(dǎo)致 InnoDB 給所有的記錄都加了行鎖窑滞。這樣 session2 的排他鎖就無法得到從而進(jìn)入阻塞狀態(tài)琼牧。
2. 由于 MySQL 的行鎖是針對索引加的鎖，不是針對記錄加的鎖哀卫，所以雖然訪問不同行的記錄巨坊，如果使用了相同的索引鍵，一樣會出現(xiàn)鎖沖突此改。
| record1 | record2 |
|---|---|
| id=1 | value = 1 |
| id=1 | value = 4 |
表中兩條記錄趾撵，兩個字段id，value中僅有id字段有索引共啃。
// session1
set autocommit = 0;
select * from table_test where id = 1 and value = '1' for update;
// session2
set autocommit = 0;
select * from table_test where id = 1 and value = '4' for update;
// waiting
雖然session2訪問的是和session1不同的記錄占调，但是因?yàn)槭褂昧讼嗤乃饕猓砸策€是需要等待鎖。
3. 當(dāng)表有多個索引的時候妈候，不同的事務(wù)可以使用不同的索引鎖定不同的行，不論是使用主鍵索引挂滓、唯一索引還是普通索引苦银，InnoDB 都會使用行鎖來對數(shù)據(jù)加鎖。
4. 即便在條件中使用了索引字段赶站，但是是否使用索引來檢索數(shù)據(jù)是由 MySQL 通過判斷不同執(zhí)行計(jì)劃的代價來決定的幔虏，如果 MySQL 認(rèn)為全表掃描效率更高，比如對一些很小的表贝椿，它就不會使用索引想括，這種情況下 InnoDB 也會對所有記錄加鎖。
3.3 Next-key 鎖
當(dāng)我們使用范圍條件而不是相等條件檢索數(shù)據(jù)烙博，請求獲得鎖瑟蜈，InnoDB 會給符合條件的數(shù)據(jù)的索引項(xiàng)加鎖。
select * from test where id > 100 for update;
這是一個范圍檢索渣窜，InnoDB 不僅會對符合條件的記錄加鎖铺根，還會對大于100的“間隙(即不存在的記錄)”加鎖。這個檢索執(zhí)行的時候會阻塞100以后id數(shù)據(jù)的插入操作乔宿。
在使用范圍條件檢索并鎖定記錄時位迂，InnoDB 的這種加鎖機(jī)制會阻塞符合條件范圍內(nèi)鍵值對的并發(fā)插入，會造成嚴(yán)重的鎖等待详瑞。因此在實(shí)際開發(fā)中掂林，尤其是并發(fā)插入較多的應(yīng)用，我們要盡量使用相等的條件來訪問和更新數(shù)據(jù)坝橡，避免使用范圍檢索泻帮。
四 樂觀鎖與悲觀鎖
- 悲觀鎖：對“數(shù)據(jù)被修改”這件事情上，持保守態(tài)度驳庭。所以在處理數(shù)據(jù)的過程中會對被涉及到的數(shù)據(jù)加鎖刑顺。這種思想能夠最大程度上保證事務(wù)的隔離性。但是這樣的思想本身也會降低設(shè)計(jì)的并發(fā)性饲常。典型的有數(shù)據(jù)庫的鎖機(jī)制蹲堂；Java代碼中的lock與synchronized關(guān)鍵字。
- 樂觀鎖：與悲觀鎖相反的認(rèn)知態(tài)度贝淤。放寬對數(shù)據(jù)的加鎖機(jī)制柒竞。這里要提一個典型的機(jī)制-MVCC(multiVersion Concurrent Control)，基于版本的并發(fā)控制播聪。
InnoDB 提供了基于 MVCC 的并發(fā)控制機(jī)制朽基。通過給數(shù)據(jù)行附加一個版本號來確保數(shù)據(jù)對更新的敏感布隔。InnoDB 會在每行數(shù)據(jù)后添加兩個額外的隱藏的值來實(shí)現(xiàn)MVCC。在實(shí)際操作中稼虎，存儲的并不是時間衅檀，而是事務(wù)的版本號，每開啟一個新事務(wù)霎俩，事務(wù)的版本號就會遞增哀军。
- SELECT時，讀取創(chuàng)建版本號<=當(dāng)前事務(wù)版本號打却，刪除版本號為空或>當(dāng)前事務(wù)版本號杉适。
- INSERT時，保存當(dāng)前事務(wù)版本號為行的創(chuàng)建版本號
- DELETE時柳击，保存當(dāng)前事務(wù)版本號為行的刪除版本號
- UPDATE時猿推，插入一條新紀(jì)錄，保存當(dāng)前事務(wù)版本號為行創(chuàng)建版本號捌肴，同時保存當(dāng)前事務(wù)版本號到原來刪除的行
