MySQL鎖

前言

鎖是計算機(jī)協(xié)調(diào)多個進(jìn)程或線程并發(fā)訪問某一資源的機(jī)制，為了解決資源競爭問題。
在數(shù)據(jù)庫中，除了傳統(tǒng)的計算資源(如CPU、RAM、I/O等)的爭用之外，數(shù)據(jù)也是一種可供多用戶共享的資源，必須解決訪問的一致性和有效性，鎖沖突也是影響數(shù)據(jù)庫并發(fā)訪問性能的一個重要因素，鎖對數(shù)據(jù)庫而言顯得尤為重要，也更加復(fù)雜。

基本鎖類型

讀鎖（共享鎖）

針對同一個資源，可以同時進(jìn)行多個讀操作。

寫鎖（互斥鎖）

針對同一個資源，一旦加上寫鎖后，會阻塞其他的讀操作和寫操作。

MySQl的鎖

顯示加鎖

可以顯示的針對表添加讀鎖或?qū)戞i，命令格式：

lock table tableName read,tableName2 write;

查看鎖狀態(tài)

show open tables;

mysql> show open tables;
+--------------------+---------------------------+--------+-------------+
| Database           | Table                     | In_use | Name_locked |
+--------------------+---------------------------+--------+-------------+
| mysql              | table_stats               |      0 |           0 |
| mysql              | collations                |      0 |           0 | 
| test               | myisam_lock               |      1 |           0 |
+--------------------+---------------------------+--------+-------------+

In_use 0是為加鎖，1是加鎖了。

分析鎖狀態(tài)

mysql> show status like 'table_locks%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Table_locks_immediate | 22    |
| Table_locks_waited    | 0     |
+-----------------------+-------+
2 rows in set (0.17 sec)

table_locks_immediate: 表示立即釋放表鎖數(shù)。
table_locks_waited: 表示需要等待的表鎖數(shù)。此值越高則說明存在著越嚴(yán)重的表級鎖爭用情況。

表鎖

顧名思義，對整張表進(jìn)行加鎖，MyIsam引擎采用的就是表鎖。

表鎖的優(yōu)勢：開銷??；加鎖快；無死鎖

表鎖的劣勢：鎖粒度大，發(fā)生鎖沖突的概率高，并發(fā)處理能力低

• 應(yīng)用場景

  InnoDB默認(rèn)采用行鎖，在未使用索引字段查詢時升級為表鎖。MySQL這樣設(shè)計并不是給你挖坑。它有自己的設(shè)計目的。

  即便你在條件中使用了索引字段，MySQL會根據(jù)自身的執(zhí)行計劃，考慮是否使用索引(所以explain命令中會有possible_key 和 key)。如果MySQL認(rèn)為全表掃描效率更高，它就不會使用索引，這種情況下InnoDB將使用表鎖，而不是行鎖。因此，在分析鎖沖突時，別忘了檢查SQL的執(zhí)行計劃，以確認(rèn)是否真正使用了索引。

  第一種情況：全表更新。事務(wù)需要更新大部分或全部數(shù)據(jù)，且表又比較大。若使用行鎖，會導(dǎo)致事務(wù)執(zhí)行效率低，從而可能造成其他事務(wù)長時間鎖等待和更多的鎖沖突。

  第二種情況：多表查詢。事務(wù)涉及多個表，比較復(fù)雜的關(guān)聯(lián)查詢，很可能引起死鎖，造成大量事務(wù)回滾。這種情況若能一次性鎖定事務(wù)涉及的表，從而可以避免死鎖、減少數(shù)據(jù)庫因事務(wù)回滾帶來的開銷。

共享讀鎖

對MyISAM表的讀操作（加讀鎖），不會阻塞其他進(jìn)程對同一表的讀操作，但會阻塞對同一表的寫操作。只有當(dāng)讀鎖釋放后，才能執(zhí)行其他進(jìn)程的寫操作。在鎖釋放前不能取其他表。

表鎖-讀鎖

獨占寫鎖

對MyISAM表的寫操作（加寫鎖），會阻塞其他進(jìn)程對同一表的讀和寫操作，只有當(dāng)寫鎖釋放后，才會執(zhí)行其他進(jìn)程的讀寫操作。在鎖釋放前不能寫其他表。

表鎖-獨占寫鎖

行鎖

對某個記錄行進(jìn)行加速?？梢葬槍Ρ硖砑幼x鎖或?qū)戞i；innodb引擎默認(rèn)用的就是行鎖。

行鎖的劣勢：開銷大；加鎖慢；會出現(xiàn)死鎖

行鎖的優(yōu)勢：鎖的粒度小，發(fā)生鎖沖突的概率低；處理并發(fā)的能力強(qiáng)

注：這里通過修改autocommit來實現(xiàn)展示模擬鎖

共享鎖

共享鎖，也稱讀鎖，多用于判斷數(shù)據(jù)是否存在，多個讀操作可以同時進(jìn)行而不會互相影響。當(dāng)如果事務(wù)對讀鎖進(jìn)行修改操作，很可能會造成死鎖。

行鎖-共享鎖

排他鎖

排他鎖，也稱寫鎖，獨占鎖，當(dāng)前寫操作沒有完成前，它會阻斷其他寫鎖和讀鎖。

行鎖

間隙鎖

當(dāng)我們用范圍條件檢索數(shù)據(jù)，并請求共享或排他鎖時，InnoDB會給符合條件的已有數(shù)據(jù)記錄的索引項加鎖；對于鍵值在條件范圍內(nèi)但并不存在的記錄，叫做”間隙(GAP)”。InnoDB也會對這個”間隙”加鎖，這種鎖機(jī)制就是所謂的間隙鎖(Next-Key鎖)。

間隙鎖

危害：若執(zhí)行的條件是范圍過大，則InnoDB會將整個范圍內(nèi)所有的索引鍵值全部鎖定，很容易對性能造成影響

行鎖變表鎖

如果索引失效的話會導(dǎo)致行鎖變表鎖。

行鎖變表鎖

頁鎖

開銷和加鎖時間介于表鎖和行鎖之間；會出現(xiàn)死鎖；鎖定粒度介于表鎖和行鎖之間，并發(fā)處理能力一般。

結(jié)論

innodb 存儲引擎由于東現(xiàn)了行級鎖定，雖然在鎖定機(jī)制的實現(xiàn)方面所帶來的性能損耗可能比表級鎖定會要更高一些，但是在整體并發(fā)處理能力方面要遠(yuǎn)遠(yuǎn)優(yōu)于 MyISAM 的表級鎖定的。當(dāng)系統(tǒng)并發(fā)里較高的時候， Innodb 的整體性能和 MyISAM 相比就會有比較明顯的優(yōu)勢了。但是， Innodb 的行級鎖定同樣也有其脆弱的一面，當(dāng)我們使用不當(dāng)?shù)臅r候，可能會讓 Innodb 的整體性能表現(xiàn)不僅不能比 MylsAM 高甚至可能會更差。

參考文檔

MySQL鎖