MySQL鎖簡介

MySQL的鎖機(jī)制比較簡單，其最顯著的特點是不同的存儲引擎支持不同的鎖機(jī)制。比如泽篮，MyISAM和MEMORY存儲引擎采用的是表級鎖（table-level locking）；InnoDB存儲引擎既支持行級鎖（row-level locking）唐断，也支持表級鎖，但默認(rèn)情況下是采用行級鎖杭抠。

表級鎖：開銷小脸甘，加鎖快；不會出現(xiàn)死鎖偏灿；鎖定粒度大丹诀，發(fā)生鎖沖突的概率最高，并發(fā)度最低翁垂。
行級鎖：開銷大铆遭，加鎖慢；會出現(xiàn)死鎖沿猜；鎖定粒度最小枚荣，發(fā)生鎖沖突的概率最低，并發(fā)度也最高啼肩。

從上述特點可見橄妆，很難籠統(tǒng)地說哪種鎖更好，只能就具體應(yīng)用的特點來說哪種鎖更合適祈坠！僅從鎖的角度 來說：表級鎖更適合于以查詢?yōu)橹骱δ耄挥猩倭堪此饕龡l件更新數(shù)據(jù)的應(yīng)用，如Web應(yīng)用赦拘；而行級鎖則更適合于有大量按索引條件并發(fā)更新少量不同數(shù)據(jù)慌随，同時又有 并發(fā)查詢的應(yīng)用，如一些在線事務(wù)處理（OLTP）系統(tǒng)躺同。

前提準(zhǔn)備：

1. 提前建一個測試用表

CREATE TABLE `lock_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `NAME` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `NAME` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `lock_table` (`id`, `NAME`) VALUES ('1', 'a');
INSERT INTO `lock_table` (`id`, `NAME`) VALUES ('2', 'b');
INSERT INTO `lock_table` (`id`, `NAME`) VALUES ('3', 'c');
INSERT INTO `lock_table` (`id`, `NAME`) VALUES ('4', 'd');

INSERT INTO `user` (`id`, `NAME`) VALUES ('1', 'zhangsan');

2.開啟兩個mysql回話儒陨，并關(guān)閉autocommit

set @@autocommit=OFF;

MyISAM表鎖

MySQL的表級鎖有兩種模式：表共享讀鎖（Table Read Lock）和表獨占寫鎖（Table Write Lock）。

對MyISAM表的讀操作笋籽，不會阻塞其他用戶對同一表的讀請求蹦漠，但會阻塞對同一表的寫請求；對 MyISAM表的寫操作车海，則會阻塞其他用戶對同一表的讀和寫操作笛园；MyISAM表的讀操作與寫操作之間，以及寫操作之間是串行的侍芝！

寫鎖阻塞讀的案例

讀鎖阻塞寫的案例

總結(jié)

MyISAM在執(zhí)行查詢語句之前州叠，會自動給涉及的所有表加讀鎖棵红，在執(zhí)行更新操作前，會自動給涉及的表加寫鎖咧栗，這個過程并不需要用戶干預(yù)逆甜，因此用戶一般不需要使用命令來顯式加鎖虱肄，上例中的加鎖時為了演示效果。

InnoDB鎖

1. 事務(wù)

事務(wù)是由一組SQL語句組成的邏輯處理單元交煞，事務(wù)具有4屬性咏窿，通常稱為事務(wù)的ACID屬性。

原子性（Actomicity）：事務(wù)是一個原子操作單元素征，其對數(shù)據(jù)的修改集嵌，要么全都執(zhí)行，要么全都不執(zhí)行御毅。
一致性（Consistent）：在事務(wù)開始和完成時根欧，數(shù)據(jù)都必須保持一致狀態(tài)。
隔離性（Isolation）：數(shù)據(jù)庫系統(tǒng)提供一定的隔離機(jī)制端蛆，保證事務(wù)在不受外部并發(fā)操作影響的“獨立”環(huán)境執(zhí)行凤粗。
持久性（Durable）：事務(wù)完成之后，它對于數(shù)據(jù)的修改是永久性的欺税，即使出現(xiàn)系統(tǒng)故障也能夠保持侈沪。

2.并發(fā)情況下事務(wù)帶來的問題

相對于串行處理來說揭璃，并發(fā)事務(wù)處理能大大增加數(shù)據(jù)庫資源的利用率晚凿，提高數(shù)據(jù)庫系統(tǒng)的事務(wù)吞吐量，從而可以支持更多用戶的并發(fā)操作瘦馍，但與此同時歼秽，會帶來一下問題：

臟讀： 一個事務(wù)在更新一條記錄，未提交前情组，第二個事務(wù)讀到了第一個事務(wù)更新后的記錄燥筷，那么第二個事務(wù)就讀到了臟數(shù)據(jù)，會產(chǎn)生對第一個未提交數(shù)據(jù)的依賴院崇。一旦第一個事務(wù)回滾肆氓，那么第二個事務(wù)讀到的數(shù)據(jù)，將是錯誤的臟數(shù)據(jù)底瓣。

不可重復(fù)讀：一個事務(wù)在讀取某些數(shù)據(jù)后的一段時間后谢揪，再次讀取這個數(shù)據(jù)，發(fā)現(xiàn)其讀取出來的數(shù)據(jù)內(nèi)容已經(jīng)發(fā)生了改變捐凭，就是不可重復(fù)讀拨扶。

幻讀： 一個事務(wù)按相同的查詢條件查詢之前檢索過的數(shù)據(jù)，確發(fā)現(xiàn)檢索出來的結(jié)果集條數(shù)變多或者減少（由其他事務(wù)插入茁肠、刪除的）患民，類似產(chǎn)生幻覺。

上述出現(xiàn)的問題都是數(shù)據(jù)庫讀一致性的問題垦梆，可以通過事務(wù)的隔離機(jī)制來進(jìn)行保證匹颤。

3.InnoDB行鎖及加鎖方法

共享鎖（S）：允許一個事務(wù)去讀一行仅孩，阻止其他事務(wù)獲得相同的數(shù)據(jù)集的排他鎖。
排他鎖（X）：允許獲得排他鎖的事務(wù)更新數(shù)據(jù)惋嚎，但是組織其他事務(wù)獲得相同數(shù)據(jù)集的共享鎖和排他鎖杠氢。
意向共享鎖（IS）：表示事務(wù)準(zhǔn)備給數(shù)據(jù)行加入共享鎖，也就是說一個數(shù)據(jù)行加共享鎖前必須先取得該表的IS鎖
意向排他鎖（IX）：類似上面另伍，表示事務(wù)準(zhǔn)備給數(shù)據(jù)行加入排他鎖鼻百，說明事務(wù)在一個數(shù)據(jù)行加排他鎖前必須先取得該表的IX鎖。

MySQL InnoDB引擎默認(rèn)的修改數(shù)據(jù)語句：update,delete,insert都會自動給涉及到的數(shù)據(jù)加上排他鎖摆尝，select語句默認(rèn)不會加任何鎖類型温艇，如果加排他鎖可以使用select …for update語句，加共享鎖可以使用select … lock in share mode語句堕汞。所以加過排他鎖的數(shù)據(jù)行在其他事務(wù)種是不能修改數(shù)據(jù)的勺爱，也不能通過for update和lock in share mode鎖的方式查詢數(shù)據(jù)，但可以直接通過select …from…查詢數(shù)據(jù)讯检，因為普通查詢沒有任何鎖機(jī)制琐鲁。

InnoDB行鎖實現(xiàn)方式

InnoDB行鎖是通過給索引上的索引項加鎖來實現(xiàn)的，這一點MySQL與Oracle不同人灼，后者是通過在數(shù)據(jù)塊中對相應(yīng)數(shù)據(jù)行加鎖來實現(xiàn)的围段。InnoDB這種行鎖實現(xiàn)特點意味著：只有通過索引條件檢索數(shù)據(jù)，InnoDB才使用行級鎖投放，否則奈泪，InnoDB將使用表鎖！

1.在不通過索引查詢的時候灸芳，innodb使用的是表鎖而不是行鎖
創(chuàng)建表

CREATE TABLE `person_no_index` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(30) DEFAULT NULL,
  `age` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

insert into person_no_index (id,name,age) values (1,"張三",25);
insert into person_no_index (id,name,age) values (2,"李四",28);
insert into person_no_index (id,name,age) values (3,"趙六",59);

會話1只給一行加了排他鎖涝桅，但是會話2在請求其他行的排他鎖的時候，會出現(xiàn)鎖等待烙样。原因是在沒有索引的情況下冯遂，innodb只能使用表鎖。

2.通過索引查詢的時候谒获，innodb使用的是行鎖
創(chuàng)建表

CREATE TABLE `person_index` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(30) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  KEY `name_idx` (`name`),
  KEY `age_idx` (`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

insert into person_index (id,name,age) values (1,"張三",25);
insert into person_index (id,name,age) values (2,"李四",28);
insert into person_index (id,name,age) values (3,"趙六",59);

會話1只給 name=張三 的行加了排他鎖，會話2在請求其他行的排他鎖的時候究反，可以正常查詢寻定，在查詢 name=張三 時出現(xiàn)鎖阻塞，所以此時使用的是行鎖精耐。

2.由于MySQL的行鎖是針對索引加的鎖狼速，不是針對記錄加的鎖，所以雖然是訪問不同行的記錄卦停，但是如果是使用相同的索引鍵向胡，是會出現(xiàn)沖突的恼蓬。

插入一條name=張三 id不同的數(shù)據(jù)
insert into person_index (id,name,age) values (4,"張三",88);
此時表中數(shù)據(jù)為：
+------+--------+------+
| id   | name   | age  |
+------+--------+------+
|    1 | 張三   |   25 |
|    2 | 李四   |   28 |
|    3 | 趙六   |   59 |
|    4 | 張三   |   88 |
+------+--------+------+

總結(jié)

對于MyISAM的表鎖，主要討論了以下幾點：
（1）共享讀鎖（S）之間是兼容的拇派，但共享讀鎖（S）與排他寫鎖（X）之間荷辕，以及排他寫鎖（X）之間是互斥的，也就是說讀和寫是串行的件豌。
（2）在一定條件下疮方，MyISAM允許查詢和插入并發(fā)執(zhí)行，我們可以利用這一點來解決應(yīng)用中對同一表查詢和插入的鎖爭用問題茧彤。
（3）MyISAM默認(rèn)的鎖調(diào)度機(jī)制是寫優(yōu)先骡显，這并不一定適合所有應(yīng)用，用戶可以通過設(shè)置LOW_PRIORITY_UPDATES參數(shù)曾掂，或在INSERT惫谤、UPDATE、DELETE語句中指定LOW_PRIORITY選項來調(diào)節(jié)讀寫鎖的爭用珠洗。
（4）由于表鎖的鎖定粒度大溜歪，讀寫之間又是串行的，因此险污，如果更新操作較多痹愚，MyISAM表可能會出現(xiàn)嚴(yán)重的鎖等待富岳，可以考慮采用InnoDB表來減少鎖沖突蛔糯。

對于InnoDB表，本文主要討論了以下幾項內(nèi)容：
（1）InnoDB的行鎖是基于索引實現(xiàn)的窖式，如果不通過索引訪問數(shù)據(jù)蚁飒，InnoDB會使用表鎖。
（2）在不同的隔離級別下萝喘，InnoDB的鎖機(jī)制和一致性讀策略不同淮逻。

在了解InnoDB鎖特性后，用戶可以通過設(shè)計和SQL調(diào)整等措施減少鎖沖突和死鎖阁簸，包括：

• 盡量使用較低的隔離級別爬早； 精心設(shè)計索引，并盡量使用索引訪問數(shù)據(jù)启妹，使加鎖更精確筛严，從而減少鎖沖突的機(jī)會；
• 選擇合理的事務(wù)大小饶米，小事務(wù)發(fā)生鎖沖突的幾率也更薪翱小车胡；
• 給記錄集顯式加鎖時，最好一次性請求足夠級別的鎖照瘾。比如要修改數(shù)據(jù)的話匈棘，最好直接申請排他鎖，而不是先申請共享鎖析命，修改時再請求排他鎖渐溶，這樣容易產(chǎn)生死鎖；
• 不同的程序訪問一組表時讲竿，應(yīng)盡量約定以相同的順序訪問各表翅娶，對一個表而言，盡可能以固定的順序存取表中的行昼浦。這樣可以大大減少死鎖的機(jī)會馍资；
• 盡量用相等條件訪問數(shù)據(jù)，這樣可以避免間隙鎖對并發(fā)插入的影響关噪； 不要申請超過實際需要的鎖級別鸟蟹；除非必須，查詢時不要顯示加鎖使兔；
• 對于一些特定的事務(wù)建钥，可以使用表鎖來提高處理速度或減少死鎖的可能。