1.鎖定義
鎖是計算機(jī)協(xié)調(diào)多個進(jìn)程或線程并發(fā)訪問某一資源的機(jī)制。 在數(shù)據(jù)庫中，除了傳統(tǒng)的計算資源(如CPU止邮、RAM谨履、I/O等)的爭用以外欢摄， 數(shù)據(jù)也是一種供需要用戶共享的資源。如何保證數(shù)據(jù)并發(fā)訪問的一致性笋粟、有效性 是所有數(shù)據(jù)庫必須解決的一個問題剧浸，鎖沖突也是影響數(shù)據(jù)庫并發(fā)訪問性能的一個 重要因素锹引。
2.鎖分類
-從性能上分為樂觀鎖(用版本對比來實現(xiàn))和悲觀鎖
-從對數(shù)據(jù)庫操作的類型分，分為讀鎖和寫鎖(都屬于悲觀鎖) 讀鎖(共享鎖):針對同一份數(shù)據(jù)唆香，多個讀操作可以同時進(jìn)行而不會互相影響 寫鎖(排它鎖):當(dāng)前寫操作沒有完成前嫌变，它會阻斷其他寫鎖和讀鎖
-從對數(shù)據(jù)操作的粒度分，分為表鎖和行鎖
2.1表鎖
每次操作鎖住整張表躬它。開銷小腾啥，加鎖快;不會出現(xiàn)死鎖;鎖定粒度大，發(fā)生鎖沖 突的概率最高冯吓，并發(fā)度最低;
2.1.1 基本操作
--建表sql
create table 'muylock'(
'id' int(11) not null auto_increment,
'name' varchar(default) null,
primary key ('id')
) engine = myisam default charset =utf8;
INSERTINTO`test`.`mylock`(`id`,`NAME`)VALUES('2','b'); INSERTINTO`test`.`mylock`(`id`,`NAME`)VALUES('3','c'); INSERTINTO`test`.`mylock`(`id`,`NAME`)VALUES('4','d');
-手動增加表鎖
lock table 表名稱 read(write),表名稱2 read(write);
-查看表上加過的鎖 show open tables;
-刪除表鎖 unlock tables;
2.1.2 案例分析(加讀鎖)
當(dāng)前session和其他session都可以讀該表 當(dāng)前session中插入或者更新鎖定的表都會報錯倘待，其他session插入或更新則會等待
2.1.3 案例分析(加寫鎖)
當(dāng)前session對該表的增刪改查都沒有問題，其他session對該表的所有操作被阻 塞
2.1.4 案例結(jié)論
MyISAM在執(zhí)行查詢語句(SELECT)前,會自動給涉及的所有表加讀鎖,在執(zhí)行增刪改 操作前,會自動給涉及的表加寫鎖组贺。
1凸舵、對MyISAM表的讀操作(加讀鎖) ,不會阻寒其他進(jìn)程對同一表的讀請求,但會阻 賽對同一表的寫請求。只有當(dāng)讀鎖釋放后,才會執(zhí)行其它進(jìn)程的寫操作失尖。
2啊奄、對MylSAM表的寫操作(加寫鎖) ,會阻塞其他進(jìn)程對同一表的讀和寫操作,只有 當(dāng)寫鎖釋放后,才會執(zhí)行其它進(jìn)程的讀寫操作
總結(jié): 簡而言之，就是讀鎖會阻塞寫掀潮，但是不會阻塞讀菇夸。而寫鎖則會把讀和寫都阻塞。
2.1行鎖
每次操作鎖住一行數(shù)據(jù)仪吧。開銷大庄新，加鎖慢;會出現(xiàn)死鎖;鎖定粒度最小，發(fā)生鎖
沖突的概率最低薯鼠，并發(fā)度最高择诈。 InnoDB與MYISAM的最大不同有兩點(diǎn):
-支持事務(wù)(TRANSACTION)
-支持行級鎖
2.2.1 行鎖支持事務(wù)
-事務(wù)(Transaction)及其ACID屬性
事務(wù)是由一組SQL語句組成的邏輯處理單元,事務(wù)具有以下4個屬性,通常簡稱為 事務(wù)的ACID屬性。
原子性(Atomicity):事務(wù)是一個原子操作單元,其對數(shù)據(jù)的修改,要么全都執(zhí) 行,要么全都不執(zhí)行出皇。
一致性(Consistent) :在事務(wù)開始和完成時,數(shù)據(jù)都必須保持一致狀態(tài)吭从。這意 味著所有相關(guān)的數(shù)據(jù)規(guī)則都必須應(yīng)用于事務(wù)的修改,以保持?jǐn)?shù)據(jù)的完整性;事務(wù)結(jié)束 時,所有的內(nèi)部數(shù)據(jù)結(jié)構(gòu)(如B樹索引或雙向鏈表)也都必須是正確的。
隔離性(Isolation):數(shù)據(jù)庫系統(tǒng)提供一定的隔離機(jī)制,保證事務(wù)在不受外部并 發(fā)操作影響的“獨(dú)立”環(huán)境執(zhí)行恶迈。這意味著事務(wù)處理過程中的中間狀態(tài)對外部是 不可見的,反之亦然涩金。
持久性(Durable):事務(wù)完成之后,它對于數(shù)據(jù)的修改是永久性的,即使出現(xiàn)系 統(tǒng)故障也能夠保持。
-并發(fā)事務(wù)處理帶來的問題
更新丟失(Lost Update)
當(dāng)兩個或多個事務(wù)選擇同一行暇仲，然后基于最初選定的值更新該行時步做，由于每 個事務(wù)都不知道其他事務(wù)的存在，就會發(fā)生丟失更新問題–最后的更新覆蓋了由其 他事務(wù)所做的更新奈附。
臟讀(Dirty Reads)
一個事務(wù)正在對一條記錄做修改全度，在這個事務(wù)完成并提交前，這條記錄的數(shù) 據(jù)就處于不一致的狀態(tài);這時斥滤，另一個事務(wù)也來讀取同一條記錄将鸵，如果不加控 制勉盅，第二個事務(wù)讀取了這些“臟”數(shù)據(jù)，并據(jù)此作進(jìn)一步的處理顶掉，就會產(chǎn)生未提 交的數(shù)據(jù)依賴關(guān)系草娜。這種現(xiàn)象被形象的叫做“臟讀”。 一句話:事務(wù)A讀取到了事務(wù)B已經(jīng)修改但尚未提交的數(shù)據(jù)痒筒，還在這個數(shù)據(jù)基 礎(chǔ)上做了操作宰闰。此時，如果B事務(wù)回滾簿透，A讀取的數(shù)據(jù)無效移袍，不符合一致性要求。
不可重讀(Non-Repeatable Reads)
一個事務(wù)在讀取某些數(shù)據(jù)后的某個時間老充，再次讀取以前讀過的數(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)
一個事務(wù)按相同的查詢條件重新讀取以前檢索過的數(shù)據(jù)，卻發(fā)現(xiàn)其他事務(wù)插 入了滿足其查詢條件的新數(shù)據(jù)虫啥，這種現(xiàn)象就稱為“幻讀”蔚约。 一句話:事務(wù)A讀取到了事務(wù)B提交的新增數(shù)據(jù)奄妨，不符合隔離性
-事務(wù)隔離級別
臟讀”涂籽、“不可重復(fù)讀”和“幻讀”,其實都是數(shù)據(jù)庫讀一致性問題,必須由數(shù) 據(jù)庫提供一定的事務(wù)隔離機(jī)制來解決。
數(shù)據(jù)庫的事務(wù)隔離越嚴(yán)格,并發(fā)副作用越小,但付出的代價也就越大,因為事務(wù)隔 離實質(zhì)上就是使事務(wù)在一定程度上“串行化”進(jìn)行,這顯然與“并發(fā)”是矛盾的砸抛。
同時,不同的應(yīng)用對讀一致性和事務(wù)隔離程度的要求也是不同的,比如許多應(yīng)用 對“不可重復(fù)讀"和“幻讀”并不敏感,可能更關(guān)心數(shù)據(jù)并發(fā)訪問的能力评雌。
常看當(dāng)前數(shù)據(jù)庫的事務(wù)隔離級別: show variables like 'tx_isolation';
設(shè)置事務(wù)隔離級別:set tx_isolation='REPEATABLE-READ';
2.2.2 行鎖與隔離級別案例分析
CREATETABLE`account`(
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`balance` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
)ENGINE=InnoDBDEFAULTCHARSET=utf8;
INSERTINTO`test`.`account`(`name`,`balance`)VALUES('lilei','450');
INSERTINTO`test`.`account`(`name`,`balance`)VALUES('hanmei', '16000');
INSERTINTO`test`.`account`(`name`,`balance`)VALUES('lucy','2400');
1直焙、行鎖演示
一個session開啟事務(wù)更新不提交景东，另一個session更新同一條記錄會阻塞，更 新不同記錄不會阻塞
2奔誓、讀未提交:
(1)打開一個客戶端A斤吐，并設(shè)置當(dāng)前事務(wù)模式為read uncommitted(未提交 讀)，查詢表account的初始值:
set tx_isolation='read-uncommitted';
(2)在客戶端A的事務(wù)提交之前厨喂，打開另一個客戶端B和措，更新表account:
(3)這時，雖然客戶端B的事務(wù)還沒提交蜕煌，但是客戶端A就可以查詢到B已經(jīng)更 新的數(shù)據(jù):
(4)一旦客戶端B的事務(wù)因為某種原因回滾派阱，所有的操作都將會被撤銷，那 客戶端A查詢到的數(shù)據(jù)其實就是臟數(shù)據(jù):
(5)在客戶端A執(zhí)行更新語句update account set balance = balance - 50 where id =1斜纪，lilei的balance沒有變成350贫母，居然是400文兑，是不是很奇怪，數(shù)據(jù)不 一致啊腺劣，如果你這么想就太天真 了绿贞，在應(yīng)用程序中，我們會用400-50=350誓酒，并 不知道其他會話回滾了樟蠕，要想解決這個問題可以采用讀已提交的隔離級別
3、讀已提交
(1)打開一個客戶端A靠柑，并設(shè)置當(dāng)前事務(wù)模式為read committed(未提交 讀)寨辩，查詢表account的所有記錄:
set tx_isolation='read-committed';
(2)在客戶端A的事務(wù)提交之前，打開另一個客戶端B歼冰，更新表account:
(3)這時靡狞，客戶端B的事務(wù)還沒提交，客戶端A不能查詢到B已經(jīng)更新的數(shù) 據(jù)隔嫡，解決了臟讀問題:
(4)客戶端B的事務(wù)
(5)客戶端A執(zhí)行與上一步相同的查詢甸怕，結(jié)果 與上一步不一致，即產(chǎn)生了不 可重復(fù)讀的問題
4腮恩、可重復(fù)讀
(1)打開一個客戶端A梢杭，并設(shè)置當(dāng)前事務(wù)模式為repeatable read，查詢表 account的所有記錄
set tx_isolation='repeatable-read';
(2)在客戶端A的事務(wù)提交之前秸滴，打開另一個客戶端B武契，更新表account并提 交
(3)在客戶端A查詢表account的所有記錄，與步驟(1)查詢結(jié)果一致荡含，沒 有出現(xiàn)不可重復(fù)讀的問題
(4)在客戶端A咒唆，接著執(zhí)行update account set balance = balance - 50 where id = 1，balance沒有變成400-50=350释液，lilei的balance值用的是步驟 (2)中的350來算的全释，所以是300，數(shù)據(jù)的一致性倒是沒有被破壞误债〗可重復(fù)讀的 隔離級別下使用了MVCC(multi-version concurrency control)機(jī)制，select操作不會更新版本號寝蹈，是快照讀(歷史版本);insert李命、update和delete會更新版本 號，是當(dāng)前讀(當(dāng)前版本)躺盛。
MVCC機(jī)制詳解
1 id name balance 創(chuàng)建事務(wù)id 刪除事務(wù)id 2 1lilei4501013
3 2hanmei1600011空
4 2han6661600013空
對于事務(wù)id為12的操作项戴，先查詢select * from account(創(chuàng)建了查詢快照，記錄執(zhí) 行sql這一刻最大的已提交事務(wù)id(快照點(diǎn)已提交最大事務(wù)id))槽惫，對于事務(wù)id為13的 操作周叮，先刪除id=1的記錄辩撑，然后更新id=2的記錄，再提交 對于刪除操作仿耽，mysql底層會記錄好被刪除的數(shù)據(jù)行的刪除事務(wù)id合冀，對于更新操作 mysql底層會新增一行相同數(shù)據(jù)并記錄好對應(yīng)的創(chuàng)建事務(wù)id 在id為12的事務(wù)里執(zhí)行查詢操作mysql底層會帶上過濾條件，創(chuàng)建事務(wù)id <= max(當(dāng)前事務(wù)id(12)项贺，快照點(diǎn)已提交最大事務(wù)id)君躺，刪除事務(wù)id> max(當(dāng)前事 務(wù)id(12)，快照點(diǎn)已提交最大事務(wù)id)
注意:begin/start transaction 命令并不是一個事務(wù)的起點(diǎn)开缎，在執(zhí)行到它們之后 的第一個操作InnoDB 表的語句棕叫，事務(wù)才真正啟動，才會向mysql申請事務(wù)id奕删，mysql內(nèi)部是嚴(yán)格按照事務(wù)的啟動順序來分配事務(wù)id的
(5)重新打開客戶端B俺泣，插入一條新數(shù)據(jù)后提交
(6)在客戶端A查詢表account的所有記錄，沒有查出新增數(shù)據(jù)完残，所以沒有出 現(xiàn)幻讀
(7)驗證幻讀
在客戶端A執(zhí)行update account set balance=888 where id = 4;能更新成
功伏钠，再次查詢能查到客戶端B新增的數(shù)據(jù)
5、串行化
(1)打開一個客戶端A谨设，并設(shè)置當(dāng)前事務(wù)模式為serializable熟掂，查詢表account 的初始值:
set tx_isolation='serializable';
1 mysql>set session transaction isolation level serializable;
2 Query OK,0 rows affected(0.00sec)
3
4 mysql>starttransaction;
5 QueryOK,0rowsaffected(0.00sec)
6
7 mysql>select*fromaccount;
8 +‐‐‐‐‐‐+‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐+
9 |id|name|balance|
10 +‐‐‐‐‐‐+‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐+ 11 |1|lilei|10000|
12 |2|hanmei|10000|
13 |3|lucy|10000|
14 |4|lily|10000|
15 +‐‐‐‐‐‐+‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐+ 16 4rowsinset(0.00sec)
(2)打開一個客戶端B，并設(shè)置當(dāng)前事務(wù)模式為serializable扎拣，插入一條記錄 報錯赴肚，表被鎖了插入失敗，mysql中事務(wù)隔離級別為serializable時會鎖表鹏秋，因此 不會出現(xiàn)幻讀的情況尊蚁，這種隔離級別并發(fā)性極低亡笑，開發(fā)中很少會用到侣夷。
mysql>set session transaction isolation level serializable;
2 QueryOK,0rowsaffected(0.00sec) 3
4 mysql>starttransaction;
5 QueryOK,0rowsaffected(0.00sec)
6
7 mysql>insertintoaccountvalues(5,'tom',0);
8 ERROR1205(HY000):Lockwaittimeoutexceeded;tryrestartingtransaction
Mysql默認(rèn)級別是repeatable-read，有辦法解決幻讀問題嗎? 間隙鎖在某些情況下可以解決幻讀問題
要避免幻讀可以用間隙鎖在Session_1下面執(zhí)行update account set name = 'zhuge' where id > 10 and id <=20;仑乌，則其他Session沒法在這個范圍所包含的 間隙里插入或修改任何數(shù)據(jù)
無索引行鎖會升級為表鎖:鎖主要是加在索引上百拓，如果對非索引字 段更新, 行鎖可能會變表鎖
session1執(zhí)行:
update account set balance = 800 where name = 'lilei'; session2對該表任一行操作都會阻塞住 InnoDB的行鎖是針對索引加的鎖，不是針對記錄加的鎖晰甚。并且該索引不能失
效衙传，否則都會從行鎖升級為表鎖。
鎖定某一行還可以用lock in share mode(共享鎖) 和for update(排 它鎖)厕九，例如:select * from test_innodb_lock where a = 2 for update; 這樣其他session只能讀這行數(shù)據(jù)蓖捶，修改則會被阻塞，直到鎖定行的session 提交
2.2.3 案例結(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)不僅不能比MYISAM高溜徙，甚至可能會更差。
2.2.4 行鎖分析
通過檢查InnoDB_row_lock狀態(tài)變量來分析系統(tǒng)上的行鎖的爭奪情況 show status like'innodb_row_lock%';
對各個狀態(tài)量的說明如下:
Innodb_row_lock_current_waits: 當(dāng)前正在等待鎖定的數(shù)量 Innodb_row_lock_time: 從系統(tǒng)啟動到現(xiàn)在鎖定總時間長度 Innodb_row_lock_time_avg: 每次等待所花平均時間 Innodb_row_lock_time_max:從系統(tǒng)啟動到現(xiàn)在等待最長的一次所花時間 Innodb_row_lock_waits:系統(tǒng)啟動后到現(xiàn)在總共等待的次數(shù)
對于這5個狀態(tài)變量犀填，比較重要的主要是:
Innodb_row_lock_time_avg (等待平均時長) Innodb_row_lock_waits (等待總次數(shù)) Innodb_row_lock_time(等待總時長)
尤其是當(dāng)?shù)却螖?shù)很高蠢壹，而且每次等待時長也不小的時候，我們就需要分析系統(tǒng) 中為什么會有如此多的等待九巡，然后根據(jù)分析結(jié)果著手制定優(yōu)化計劃知残。
2.2.5 死鎖
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\G; 大多數(shù)情況mysql可以自動檢測死鎖并回滾產(chǎn)生死鎖的那個事務(wù)，但是有些情況 mysql沒法自動檢測死鎖
2.2.6 優(yōu)化建議
-盡可能讓所有數(shù)據(jù)檢索都通過索引來完成比庄，避免無索引行鎖升級為表鎖
-合理設(shè)計索引求妹，盡量縮小鎖的范圍
-盡可能減少檢索條件范圍，避免間隙鎖 盡量控制事務(wù)大小佳窑，減少鎖定資源量和時間長度制恍，涉及事務(wù)加鎖的sql盡量放在事務(wù)最后執(zhí)行
-盡可能低級別事務(wù)隔離
