1.1 定義
　　鎖是計(jì)算機(jī)協(xié)調(diào)多個(gè)進(jìn)程或線程并發(fā)訪問(wèn)某一資源的機(jī)制嫁怀。
　　在數(shù)據(jù)庫(kù)中锅铅，除了傳統(tǒng)的計(jì)算資源（如CPU刃唐、RAM羞迷、I/O等）的爭(zhēng)用以外界轩，數(shù)據(jù)也是一種供需要用戶共享的資源。如何保證數(shù)據(jù)并發(fā)訪問(wèn)的一致性衔瓮、有效性是所有數(shù)據(jù)庫(kù)必須解決的一個(gè)問(wèn)題浊猾，鎖沖突也是影響數(shù)據(jù)庫(kù)并發(fā)訪問(wèn)性能的一個(gè)重要因素。從這個(gè)角度來(lái)說(shuō)热鞍，鎖對(duì)數(shù)據(jù)庫(kù)而言顯得尤其重要葫慎，也更加復(fù)雜。

1.2 鎖的分類
從性能上分為樂(lè)觀鎖(用版本對(duì)比來(lái)實(shí)現(xiàn))和悲觀鎖
從對(duì)數(shù)據(jù)庫(kù)操作的類型分碍现，分為讀鎖和寫(xiě)鎖(都屬于悲觀鎖)
讀鎖（共享鎖）：針對(duì)同一份數(shù)據(jù)幅疼，多個(gè)讀操作可以同時(shí)進(jìn)行而不會(huì)互相影響
寫(xiě)鎖（排它鎖）：當(dāng)前寫(xiě)操作沒(méi)有完成前，它會(huì)阻斷其他寫(xiě)鎖和讀鎖
從對(duì)數(shù)據(jù)操作的粒度分昼接，分為表鎖和行鎖

2. 三鎖
   2.1 表鎖（偏讀）
   表鎖偏向MyISAM存儲(chǔ)引擎爽篷，開(kāi)銷小拳话，加鎖快吃媒，無(wú)思索，鎖定粒度大铝噩，發(fā)生鎖沖突的概率最高漂辐，并發(fā)度最低泪喊。

2.1.1 基本操作
?建表SQL
CREATE TABLE mylock (
id INT (11) NOT NULL AUTO_INCREMENT,
NAME VARCHAR (20) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE = MyISAM DEFAULT CHARSET = utf8;
插入數(shù)據(jù)
INSERT INTOtest.mylock (id, NAME) VALUES ('1', 'a');
INSERT INTOtest.mylock (id, NAME) VALUES ('2', 'b');
INSERT INTOtest.mylock (id, NAME) VALUES ('3', 'c');
INSERT INTOtest.mylock (id, NAME) VALUES ('4', 'd');
手動(dòng)增加表鎖
lock table 表名稱 read(write),表名稱2 read(write);
查看表上加過(guò)的鎖
show open tables;
刪除表鎖
unlock tables;

2.1.2 案例分析(加讀鎖）

image.png

當(dāng)前session和其他session都可以讀該表
當(dāng)前session中插入或者更新鎖定的表都會(huì)報(bào)錯(cuò)，其他session插入或更新則會(huì)等待
2.1.3 案例分析(加寫(xiě)鎖）

image.png

2.1.4 案例結(jié)論
MyISAM在執(zhí)行查詢語(yǔ)句(SELECT)前,會(huì)自動(dòng)給涉及的所有表加讀鎖,在執(zhí)行增刪改操作前,會(huì)自動(dòng)給涉及的表加寫(xiě)鎖袒啼。
1、對(duì)MyISAM表的讀操作(加讀鎖) ,不會(huì)阻寒其他進(jìn)程對(duì)同一表的讀請(qǐng)求,但會(huì)阻賽對(duì)同一表的寫(xiě)請(qǐng)求纬纪。只有當(dāng)讀鎖釋放后,才會(huì)執(zhí)行其它進(jìn)程的寫(xiě)操作蚓再。
2、對(duì)MylSAM表的寫(xiě)操作(加寫(xiě)鎖) ,會(huì)阻塞其他進(jìn)程對(duì)同一表的讀和寫(xiě)操作,只有當(dāng)寫(xiě)鎖釋放后,才會(huì)執(zhí)行其它進(jìn)程的讀寫(xiě)操作
總結(jié)：
簡(jiǎn)而言之包各，就是讀鎖會(huì)阻塞寫(xiě)摘仅，但是不會(huì)阻塞讀。而寫(xiě)鎖則會(huì)把讀和寫(xiě)都阻塞问畅。

2.2 行鎖（偏寫(xiě)）
行鎖偏向InnoDB存儲(chǔ)引擎娃属，開(kāi)銷大，加鎖慢护姆，會(huì)出現(xiàn)死鎖矾端，鎖定粒度最小，發(fā)生鎖沖突的概率最低卵皂，并發(fā)度也最高须床。InnoDB與MYISAM的最大不同有兩點(diǎn)：一是支持事務(wù)（TRANSACTION）；二是采用了行級(jí)鎖渐裂。

2.2.1 行鎖支持事務(wù)
?事務(wù)（Transaction）及其ACID屬性
事務(wù)是由一組SQL語(yǔ)句組成的邏輯處理單元,事務(wù)具有以下4個(gè)屬性,通常簡(jiǎn)稱為事務(wù)的ACID屬性豺旬。
原子性(Atomicity) ：事務(wù)是一個(gè)原子操作單元,其對(duì)數(shù)據(jù)的修改,要么全都執(zhí)行,要么全都不執(zhí)行钠惩。
一致性(Consistent) ：在事務(wù)開(kāi)始和完成時(shí),數(shù)據(jù)都必須保持一致?tīng)顟B(tài)。這意味著所有相關(guān)的數(shù)據(jù)規(guī)則都必須應(yīng)用于事務(wù)的修改,以保持?jǐn)?shù)據(jù)的完整性;事務(wù)結(jié)束時(shí),所有的內(nèi)部數(shù)據(jù)結(jié)構(gòu)(如B樹(shù)索引或雙向鏈表)也都必須是正確的族阅。
隔離性(Isolation) ：數(shù)據(jù)庫(kù)系統(tǒng)提供一定的隔離機(jī)制,保證事務(wù)在不受外部并發(fā)操作影響的“獨(dú)立”環(huán)境執(zhí)行篓跛。這意味著事務(wù)處理過(guò)程中的中間狀態(tài)對(duì)外部是不可見(jiàn)的,反之亦然。
持久性(Durable) ：事務(wù)完成之后,它對(duì)于數(shù)據(jù)的修改是永久性的,即使出現(xiàn)系統(tǒng)故障也能夠保持坦刀。

?并發(fā)事務(wù)處理帶來(lái)的問(wèn)題
更新丟失（Lost Update）
　　當(dāng)兩個(gè)或多個(gè)事務(wù)選擇同一行愧沟，然后基于最初選定的值更新該行時(shí)，由于每個(gè)事務(wù)都不知道其他事務(wù)的存在鲤遥，就會(huì)發(fā)生丟失更新問(wèn)題–最后的更新覆蓋了由其他事務(wù)所做的更新沐寺。
臟讀（Dirty Reads）
　　一個(gè)事務(wù)正在對(duì)一條記錄做修改，在這個(gè)事務(wù)完成并提交前盖奈，這條記錄的數(shù)據(jù)就處于不一致的狀態(tài)混坞；這時(shí)，另一個(gè)事務(wù)也來(lái)讀取同一條記錄钢坦，如果不加控制究孕，第二個(gè)事務(wù)讀取了這些“臟”數(shù)據(jù)，并據(jù)此作進(jìn)一步的處理爹凹，就會(huì)產(chǎn)生未提交的數(shù)據(jù)依賴關(guān)系厨诸。這種現(xiàn)象被形象的叫做“臟讀”。
　　一句話：事務(wù)A讀取到了事務(wù)B已經(jīng)修改但尚未提交的數(shù)據(jù)禾酱，還在這個(gè)數(shù)據(jù)基礎(chǔ)上做了操作微酬。此時(shí)，如果B事務(wù)回滾颤陶，A讀取的數(shù)據(jù)無(wú)效颗管，不符合一致性要求。
不可重讀（Non-Repeatable Reads）
　　一個(gè)事務(wù)在讀取某些數(shù)據(jù)后的某個(gè)時(shí)間指郁，再次讀取以前讀過(guò)的數(shù)據(jù)忙上，卻發(fā)現(xiàn)其讀出的數(shù)據(jù)已經(jīng)發(fā)生了改變拷呆、或某些記錄已經(jīng)被刪除了闲坎！這種現(xiàn)象就叫做“不可重復(fù)讀”。
　　一句話：事務(wù)A讀取到了事務(wù)B已經(jīng)提交的修改數(shù)據(jù)茬斧，不符合隔離性
幻讀（Phantom Reads）
　　一個(gè)事務(wù)按相同的查詢條件重新讀取以前檢索過(guò)的數(shù)據(jù)腰懂，卻發(fā)現(xiàn)其他事務(wù)插入了滿足其查詢條件的新數(shù)據(jù)，這種現(xiàn)象就稱為“幻讀”项秉。
　　一句話：事務(wù)A讀取到了事務(wù)B提交的新增數(shù)據(jù)绣溜，不符合隔離性
臟讀是事務(wù)B里面修改了數(shù)據(jù)
幻讀是事務(wù)B里面新增了數(shù)據(jù)

l 事務(wù)隔離級(jí)別

臟讀”、“不可重復(fù)讀”和“幻讀”,其實(shí)都是數(shù)據(jù)庫(kù)讀一致性問(wèn)題,必須由數(shù)據(jù)庫(kù)提供一定的事務(wù)隔離機(jī)制來(lái)解決娄蔼。

image.png

數(shù)據(jù)庫(kù)的事務(wù)隔離越嚴(yán)格,并發(fā)副作用越小,但付出的代價(jià)也就越大,因?yàn)槭聞?wù)
隔離實(shí)質(zhì)上就是使事務(wù)在一定程度上“串行化”進(jìn)行,這顯然與“并發(fā)”是矛盾的怖喻。
同時(shí),不同的應(yīng)用對(duì)讀一致性和事務(wù)隔離程度的要求也是不同的,比如許多應(yīng)用對(duì)“不可重復(fù)讀"和“幻讀”并不敏感,可能更關(guān)心數(shù)據(jù)并發(fā)訪問(wèn)的能力底哗。
常看當(dāng)前數(shù)據(jù)庫(kù)的事務(wù)隔離級(jí)別: show variables like 'tx_isolation';
設(shè)置事務(wù)隔離級(jí)別：set tx_isolation='REPEATABLE-READ';

2.2.2 行鎖案例分析
?用下面的表演示锚沸，需要開(kāi)啟事務(wù)跋选，Session_1更新某一行，Session_2更新同一行被阻塞哗蜈，但是更新其他行正常

2.2.3 隔離級(jí)別案例分析
CREATE TABLE account (
id int(11) NOT NULL AUTO_INCREMENT,
name varchar(255) DEFAULT NULL,
balance int(11) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO test.account (name, balance) VALUES ('lilei', '450');
INSERT INTO test.account (name, balance) VALUES ('hanmei', '16000');
INSERT INTO test.account (name, balance) VALUES ('lucy', '2400');

1前标、讀未提交：
　　　　（1）打開(kāi)一個(gè)客戶端A距潘，并設(shè)置當(dāng)前事務(wù)模式為read uncommitted（未提交讀）炼列，查詢表account的初始值：
set tx_isolation='read-uncommitted';

image.png

（2）在客戶端A的事務(wù)提交之前，打開(kāi)另一個(gè)客戶端B音比，更新表account：

image.png

（3）這時(shí)俭尖，雖然客戶端B的事務(wù)還沒(méi)提交，但是客戶端A就可以查詢到B已經(jīng)更新的數(shù)據(jù)：

image.png

（4）一旦客戶端B的事務(wù)因?yàn)槟撤N原因回滾硅确，所有的操作都將會(huì)被撤銷目溉，那客戶端A查詢到的數(shù)據(jù)其實(shí)就是臟數(shù)據(jù)：

image.png

　（5）在客戶端A執(zhí)行更新語(yǔ)句update account set balance = balance - 50 where id =1
lilei的balance沒(méi)有變成350菱农，居然是400缭付，是不是很奇怪，數(shù)據(jù)不一致啊循未，如果你這么想就太天真 了陷猫，在應(yīng)用程序中，我們會(huì)用400-50=350的妖，并不知道其他會(huì)話回滾了绣檬，要想解決這個(gè)問(wèn)題可以采用讀已提交的隔離級(jí)別.

image.png

image.png

　2、讀已提交
　　　∩┧凇（1）打開(kāi)一個(gè)客戶端A娇未，并設(shè)置當(dāng)前事務(wù)模式為read committed（未提交讀），查詢表account的所有記錄：
set tx_isolation='read-committed';

image.png

image.png

（2）在客戶端A的事務(wù)提交之前星虹，打開(kāi)另一個(gè)客戶端B零抬，更新表account：

image.png

（3）這時(shí)，客戶端B的事務(wù)還沒(méi)提交宽涌，客戶端A不能查詢到B已經(jīng)更新的數(shù)據(jù)平夜，解決了臟讀問(wèn)題：

image.png

（4）客戶端B的事務(wù)提交

image.png

（5）客戶端A執(zhí)行與上一步相同的查詢，結(jié)果 與上一步不一致卸亮，即產(chǎn)生了不可重復(fù)讀的問(wèn)題

image.png

image.png

（2）在客戶端A的事務(wù)提交之前吃溅，打開(kāi)另一個(gè)客戶端B，更新表account并提交

image.png

（3）在客戶端A查詢表account的所有記錄鸯檬，與步驟（1）查詢結(jié)果一致罕偎，沒(méi)有出現(xiàn)不可重復(fù)讀的問(wèn)題

image.png

image.png

（4）在客戶端A，接著執(zhí)行update balance = balance - 50 where id = 1京闰，balance沒(méi)有變成400-50=350颜及，lilei的balance值用的是步驟（2）中的350來(lái)算的，所以是300蹂楣，數(shù)據(jù)的一致性倒是沒(méi)有被破壞俏站。可重復(fù)讀的隔離級(jí)別下使用了MVCC機(jī)制痊土，select操作不會(huì)更新版本號(hào)肄扎，是快照讀（歷史版本）；insert赁酝、update和delete會(huì)更新版本號(hào)犯祠，是當(dāng)前讀（當(dāng)前版本）。

image.png

（5）重新打開(kāi)客戶端B酌呆，插入一條新數(shù)據(jù)后提交

image.png

（6）在客戶端A查詢表account的所有記錄衡载，沒(méi)有 查出 新增數(shù)據(jù)，所以沒(méi)有出現(xiàn)幻讀

image.png

(7)驗(yàn)證幻讀
在客戶端A執(zhí)行update account set balance=888 where id = 4;能更新成功隙袁，再次查詢能查到客戶端B新增的數(shù)據(jù)

image.png

4.串行化

（1）打開(kāi)一個(gè)客戶端A痰娱，并設(shè)置當(dāng)前事務(wù)模式為serializable，查詢表account的初始值：

set tx_isolation='serializable';

mysql> setsession transaction isolation level serializable; Query OK, 0 rows affected (0.00 sec) mysql>start transaction; Query OK, 0 rows affected (0.00 sec) mysql> select * fromaccount; +------+--------+---------+| id | name |balance |+------+--------+---------+| 1 | lilei | 10000 || 2 |hanmei | 10000 || 3 | lucy | 10000 || 4 | lily | 10000 |+------+--------+---------+4rows in set (0.00 sec)

[圖片上傳失敗...(image-4ced5a-1581781741466)]

（2）打開(kāi)一個(gè)客戶端B菩收，并設(shè)置當(dāng)前事務(wù)模式為serializable梨睁，插入一條記錄報(bào)錯(cuò)，表被鎖了插入失敗娜饵，mysql中事務(wù)隔離級(jí)別為serializable時(shí)會(huì)鎖表坡贺，因此不會(huì)出現(xiàn)幻讀的情況，這種隔離級(jí)別并發(fā)性極低箱舞，開(kāi)發(fā)中很少會(huì)用到遍坟。

mysql> setsession transaction isolation level serializable; Query OK, 0 rows affected (0.00 sec) mysql>start transaction; Query OK, 0 rows affected (0.00 sec) mysql> insert intoaccount values(5,'tom',0); ERROR 1205(HY000): Lock wait timeout exceeded; try restarting transaction

l Mysql默認(rèn)級(jí)別是repeatable-read，有辦法解決幻讀問(wèn)題嗎褐缠？

間隙鎖在某些情況下可以解決幻讀問(wèn)題

要避免幻讀可以用間隙鎖在Session_1下面執(zhí)行update account set name = 'zhuge' where id > 10 and id <=20;政鼠，則其他Session沒(méi)法插入這個(gè)范圍內(nèi)的數(shù)據(jù)

2.2.4 案例結(jié)論
　　Innodb存儲(chǔ)引擎由于實(shí)現(xiàn)了行級(jí)鎖定风瘦，雖然在鎖定機(jī)制的實(shí)現(xiàn)方面所帶來(lái)的性能損耗可能比表級(jí)鎖定會(huì)要更高一下队魏，但是在整體并發(fā)處理能力方面要遠(yuǎn)遠(yuǎn)優(yōu)于MYISAM的表級(jí)鎖定的。當(dāng)系統(tǒng)并發(fā)量高的時(shí)候，Innodb的整體性能和MYISAM相比就會(huì)有比較明顯的優(yōu)勢(shì)了胡桨。
　　但是官帘，Innodb的行級(jí)鎖定同樣也有其脆弱的一面，當(dāng)我們使用不當(dāng)?shù)臅r(shí)候昧谊，可能會(huì)讓Innodb的整體性能表現(xiàn)不僅不能比MYISAM高刽虹，甚至可能會(huì)更差。
2.2.5 行鎖分析
通過(guò)檢查InnoDB_row_lock狀態(tài)變量來(lái)分析系統(tǒng)上的行鎖的爭(zhēng)奪情況
show status like'innodb_row_lock%';

對(duì)各個(gè)狀態(tài)量的說(shuō)明如下：
Innodb_row_lock_current_waits: 當(dāng)前正在等待鎖定的數(shù)量
Innodb_row_lock_time: 從系統(tǒng)啟動(dòng)到現(xiàn)在鎖定總時(shí)間長(zhǎng)度
Innodb_row_lock_time_avg: 每次等待所花平均時(shí)間
Innodb_row_lock_time_max：從系統(tǒng)啟動(dòng)到現(xiàn)在等待最長(zhǎng)的一次所花時(shí)間
Innodb_row_lock_waits:系統(tǒng)啟動(dòng)后到現(xiàn)在總共等待的次數(shù)

對(duì)于這5個(gè)狀態(tài)變量呢诬，比較重要的主要是：
Innodb_row_lock_time_avg （等待平均時(shí)長(zhǎng)）
Innodb_row_lock_waits （等待總次數(shù)）
Innodb_row_lock_time（等待總時(shí)長(zhǎng)）

尤其是當(dāng)?shù)却螖?shù)很高涌哲，而且每次等待時(shí)長(zhǎng)也不小的時(shí)候，我們就需要分析系統(tǒng)中為什么會(huì)有如此多的等待尚镰，然后根據(jù)分析結(jié)果著手制定優(yōu)化計(jì)劃阀圾。

2.2.6 死鎖
set tx_isolation='repeatable-read';
Session_1執(zhí)行：select * from account where id=1 for update;
Session_2執(zhí)行：select * from account where id=2 for update;
Session_1執(zhí)行：select * from account where id=2 for update;
Session_2執(zhí)行：select * from account where id=1 for update;
查看近期死鎖日志信息：show engine innodb status;
大多數(shù)情況mysql可以自動(dòng)檢測(cè)死鎖并回滾產(chǎn)生死鎖的那個(gè)事務(wù)，但是有些情況mysql沒(méi)法自動(dòng)檢測(cè)死鎖

2.2.7 優(yōu)化建議
?盡可能讓所有數(shù)據(jù)檢索都通過(guò)索引來(lái)完成狗唉，避免無(wú)索引行鎖升級(jí)為表鎖
?合理設(shè)計(jì)索引初烘，盡量縮小鎖的范圍
?盡可能減少檢索條件，避免間隙鎖
?盡量控制事務(wù)大小分俯，減少鎖定資源量和時(shí)間長(zhǎng)度
?盡可能低級(jí)別事務(wù)隔離