當一個系統(tǒng)訪問量上來的時候励两，不只是數(shù)據庫性能瓶頸問題了，數(shù)據庫數(shù)據安全也會浮現(xiàn)囊颅，這時候合理使用數(shù)據庫鎖機制就顯得異常重要了当悔。
（一）MySQL優(yōu)化筆記（一）–庫與表基本操作以及數(shù)據增刪改
（二）MySQL優(yōu)化筆記（二）–查找優(yōu)化（1）（非索引設計）
（三）MySQL優(yōu)化筆記（二）–查找優(yōu)化（2）（外連接、多表聯(lián)合查詢以及查詢注意點）
（四）MySQL優(yōu)化筆記（三）–索引的使用踢代、原理和設計優(yōu)化
（五）MySQL優(yōu)化筆記（四）–表的設計與優(yōu)化（單表盲憎、多表）
（六）MySQL優(yōu)化筆記（五）–數(shù)據庫存儲引擎
（七）MySQL優(yōu)化筆記（六）–存儲過程和存儲函數(shù)
文章結構：（1）鎖機制概述；（2）MySQL各種鎖詳解（并針對MyISAM和InnoDB引擎）胳挎；（3）MySQL鎖對比饼疙；（4）項目中鎖的設計方式。
什么是鎖串远，以及為什么使用鎖和鎖的運作宏多？
鎖定機制分類儿惫？
按封鎖類型分類
按封鎖的數(shù)據粒度分類
行級鎖定
表級鎖定
頁級鎖定
數(shù)據庫事務機制澡罚。
什么叫事務？簡稱ACID肾请。
事務引起的并發(fā)調度問題
理論上的事務的三級封鎖協(xié)議
事務隔離級別
活鎖與死鎖的探究
活鎖
死鎖
（2）MySQL各種鎖詳解（并針對MyISAM和InnoDB引擎）
在這之前先講述下MySQL在共享鎖留搔、排他鎖基礎上的一個鎖拓展–**意向鎖
MySQL鎖機制總述
MyISAM引擎的鎖機制
測試MyISAM表共享讀鎖
測試MyISAM表獨占寫鎖
行級鎖定
表級鎖定
頁級鎖定
InnoDB引擎的鎖機制
與MyISAM不同，InnoDB有兩大不同點
查看InnoDB行鎖爭用情況
Innodb行鎖模式以及加鎖方法
InnoDB查看鎖語句
InnoDB行鎖實現(xiàn)方式與驗證
補充：基于InnoDB對索引加鎖的間隙鎖
補充：InnoDB引擎什么時候使用表鎖铛铁？隔显？
事務引擎導致的死鎖
鎖是計算機協(xié)調多個進程或純線程并發(fā)訪問某一資源的機制彪标。在數(shù)據庫中，除傳統(tǒng)的計算資源（CPU掷豺、RAM捞烟、I/O）的爭用以外，數(shù)據也是一種供許多用戶共享的資源当船。如何保證數(shù)據并發(fā)訪問的一致性题画、有效性是所在有數(shù)據庫必須解決的一個問題，鎖沖突也是影響數(shù)據庫并發(fā)訪問性能的一個重要因素德频。從這個角度來說苍息，鎖對數(shù)據庫而言顯得尤其重要，也更加復雜壹置。
防止更新丟失竞思，并不能單靠數(shù)據庫事務控制器來解決，需要應用程序對要更新的數(shù)據加必要的鎖來解決蒸绩。
鎖定機制就是數(shù)據庫為了保證數(shù)據的一致性而使各種共享資源在被并發(fā)訪問訪問變得有序所設計的一種規(guī)則拙毫。MySQL數(shù)據庫由于其自身架構的特點铜异，存在多種數(shù)據存儲引擎，每種存儲引擎所針對的應用場景特點都不太一樣，為了滿足各自特定應用場景的需求借尿，每種存儲引擎的鎖定機制都是為各自所面對的特定場景而優(yōu)化設計，所以各存儲引擎的鎖定機制也有較大區(qū)別庙曙。
若事務T對數(shù)據對象A加上X鎖桑腮，則只允許T讀取和修改A泉哈，其他任何事務都不能再對加任何類型的鎖，知道T釋放A上的鎖。這就保證了其他事務在T釋放A上的鎖之前不能再讀取和修改A丛晦。
若事務T對數(shù)據對象A加上S鎖烫沙，則其他事務只能再對A加S鎖匹层，而不能X鎖，直到T釋放A上的鎖锌蓄。這就保證了其他事務可以讀A又固，但在T釋放A上的S鎖之前不能對A做任何修改。
X鎖和S鎖都是加載某一個數(shù)據對象上的煤率。也就是數(shù)據的粒度仰冠。
一句總結：行級鎖：開銷大，加鎖慢蝶糯；會出現(xiàn)死鎖洋只；鎖定粒度最小，發(fā)生鎖沖突的概率最低昼捍，并發(fā)度也最高识虚。
詳細：行級鎖定最大的特點就是鎖定對象的顆粒度很小，也是目前各大數(shù)據庫管理軟件所實現(xiàn)的鎖定顆粒度最小的妒茬。由于鎖定顆粒度很小担锤，所以發(fā)生鎖定資源爭用的概率也最小，能夠給予應用程序盡可能大的并發(fā)處理能力而提高一些需要高并發(fā)應用系統(tǒng)的整體性能乍钻。
缺陷：由于鎖定資源的顆粒度很小肛循，所以每次獲取鎖和釋放鎖需要做的事情也更多，帶來的消耗自然也就更大了银择。此外多糠，行級鎖定也最容易發(fā)生死鎖。
一句總結：表級鎖：開銷小浩考，加鎖快夹孔；不會出現(xiàn)死鎖；鎖定粒度大析孽，發(fā)生鎖沖突的概率最高搭伤，并發(fā)度最低。
詳細：和行級鎖定相反袜瞬，表級別的鎖定是MySQL各存儲引擎中最大顆粒度的鎖定機制怜俐。該鎖定機制最大的特點是實現(xiàn)邏輯非常簡單，帶來的系統(tǒng)負面影響最小吞滞。所以獲取鎖和釋放鎖的速度很快佑菩。由于表級鎖一次會將整個表鎖定，所以可以很好的避免困擾我們的死鎖問題裁赠。
缺陷：鎖定顆粒度大所帶來最大的負面影響就是出現(xiàn)鎖定資源爭用的概率也會最高殿漠，致使并發(fā)度大打折扣。
一句總結：頁級鎖：開銷和加鎖時間界于表鎖和行鎖之間佩捞；會出現(xiàn)死鎖绞幌；鎖定粒度界于表鎖和行鎖之間，并發(fā)度一般一忱。
詳細：頁級鎖定是MySQL中比較獨特的一種鎖定級別莲蜘，在其他數(shù)據庫管理軟件中也并不是太常見。頁級鎖定的特點是鎖定顆粒度介于行級鎖定與表級鎖之間帘营，所以獲取鎖定所需要的資源開銷票渠，以及所能提供的并發(fā)處理能力也同樣是介于上面二者之間。
（三）數(shù)據庫事務機制（這個是數(shù)據庫核心，本系列多篇文章提到并重復）（為什么提及事務禀梳？因為事務中有封鎖機制）
1）什么叫事務杜窄？簡稱ACID。是恢復和并發(fā)控制的基本單位算途。
A 事務的原子性(Atomicity)：指一個事務要么全部執(zhí)行,要么不執(zhí)行.也就是說一個事務不可能只執(zhí)行了一半就停止了.比如你從取款機取錢,這個事務可以分成兩個步驟:1劃卡,2出錢.不可能劃了卡,而錢卻沒出來.這兩步必須同時完成.要么就不完成.
C 事務的一致性(Consistency)：指事務的運行并不改變數(shù)據庫中數(shù)據的一致性.例如,完整性約束了a+b=10,一個事務改變了a,那么b也應該隨之改變.
I 獨立性(Isolation）:事務的獨立性也有稱作隔離性,是指兩個以上的事務不會出現(xiàn)交錯執(zhí)行的狀態(tài).因為這樣可能會導致數(shù)據不一致.
D 持久性(Durability）:事務的持久性是指事務執(zhí)行成功以后,該事務所對數(shù)據庫所作的更改便是持久的保存在數(shù)據庫之中塞耕，不會無緣無故的回滾.
2）事務引起的并發(fā)調度問題：（這些會另開一篇給予實例）首先明確讀數(shù)據要以最新的為準。
例子根結點：原本有16張票扫外，甲售貨員讀取票數(shù)為事務一，乙售貨員讀取票數(shù)為事務二廓脆。甲售貨員賣出一張票為事務三畏浆。乙售貨員賣出一張票為事務四。
例子：事務三中售出一張票，修改了數(shù)據庫16變15便监，可是事務還沒提交扎谎，事務一讀了修改了的數(shù)據（15）碳想，但是事務三被中斷撤銷了，比如存錢修改數(shù)據庫后毁靶，在返回數(shù)據給客戶時出現(xiàn)異常胧奔，那么事務三就是不成功的，數(shù)據會變回16预吆×睿可是事務一讀了一個不正確的數(shù)據。
例子：事務一讀取后，事務三對16張票修改蔬芥，可是事務一中窘拯，有再次讀這張票的SQL語句，那么事務一得到的跟第一次不同的值（16張票就可能變成15張了）坝茎。
3.幻讀（phantom read）：A事務讀取了B事務已經提交的新增數(shù)據涤姊。注意和不可重復讀的區(qū)別，這里是新增嗤放，不可重復讀是更改（或刪除）思喊。這兩種情況對策是不一樣的，對于不可重復讀次酌，只需要采取行級鎖防止該記錄數(shù)據被更改或刪除恨课，然而對于幻讀必須加表級鎖，防止在這個表中新增一條數(shù)據岳服。
4.丟失更新：A事務撤銷時这吻，把已提交的B事務的數(shù)據覆蓋掉吊档。
5.覆蓋更新：A事務提交時，把已提交的B事務的數(shù)據覆蓋掉唾糯。
2.二級封鎖協(xié)議：一級封鎖協(xié)議加上事務T在讀取數(shù)據R之前必須先對其加S鎖，讀完后方可釋放S鎖堵腹。
3.三級封鎖協(xié)議 ：一級封鎖協(xié)議加上事務T在讀取數(shù)據R之前必須先對其加S鎖炸站，直到事務結束才釋放。
三級鎖操作一個比一個厲害（滿足高級鎖則一定滿足低級鎖）疚顷。但有個非常致命的地方旱易，一級鎖協(xié)議就要在第一次讀加x鎖，直到事務結束腿堤。幾乎就要在整個事務加寫鎖了阀坏，效率非常低。三級封鎖協(xié)議只是一個理論上的東西笆檀，實際數(shù)據庫常用另一套方法來解決事務并發(fā)問題忌堂。
這個是mysql用意向鎖（另一種機制，一會講解）來解決事務并發(fā)問題酗洒，為了區(qū)別封鎖協(xié)議士修，弄了一個新概念隔離性級別：包括Read Uncommitted、Read Committed樱衷、Repeatable Read棋嘲、Serializable。mysql 一般默認Repeatable Read矩桂。
一句總結：讀取數(shù)據一致性在最低級別，只能保證不讀物理上損壞的數(shù)據侄榴，會臟讀阔籽，會不可重復讀，會幻讀牲蜀。
這種隔離級別下笆制，事務間完全不隔離，會產生臟讀涣达，可以讀取未提交的記錄在辆，實際情況下不會使用证薇。
一句總結：讀取數(shù)據一致性在語句級別匆篓，不會臟讀浑度，會不可重復讀，會幻讀鸦概。
僅能讀取到已提交的記錄箩张，這種隔離級別下，會存在幻讀現(xiàn)象窗市，所謂幻讀是指在同一個事務中先慷，多次執(zhí)行同一個查詢，返回的記錄不完全相同的現(xiàn)象咨察÷畚酰幻讀產生的根本原因是，在RC隔離級別下摄狱，每條語句都會讀取已提交事務的更新脓诡，若兩次查詢之間有其他事務提交，則會導致兩次查詢結果不一致媒役。雖然如此祝谚，讀提交隔離級別在生產環(huán)境中使用很廣泛。
一句總結：讀取數(shù)據一致性在事務級別酣衷，不會臟讀踊跟，不會不可重復讀，會幻讀鸥诽。
可重復讀隔離級別解決了不可重復讀的問題商玫，但依然沒有解決幻讀的問題。不可重復讀重點在修改牡借，即讀取過的數(shù)據拳昌，兩次讀的值不一樣；而幻讀則側重于記錄數(shù)目變化【插入和刪除】钠龙。
一句總結：讀取數(shù)據一致性在最高級別炬藤，事務級別，不會臟讀碴里，不會不可重復讀沈矿，不會幻讀。
采用封鎖的方法可以有效防止數(shù)據的不一致性逼侦，單封鎖本身會引起麻煩，就是死鎖和活鎖腰耙。
如果事務T1封鎖了數(shù)據對象R后榛丢，事務T2也請求封鎖R，于是T2等待沟优，接著T3也請求封鎖R涕滋。當T1釋放了加載R上的鎖后睬辐，系統(tǒng)首先批準T3的請求挠阁，T2只能繼續(xù)等待。接著T4也請求封鎖R溯饵，T3釋放R上的鎖后侵俗，系統(tǒng)又批轉了T4的請求。這樣的一直循環(huán)下去丰刊，事務T2就只能永遠等待了隘谣，這樣情況叫活鎖。
當兩個事務分別鎖定了兩個單獨的對象，這時每一個事務都要求在另一個事務鎖定的對象上獲得一個鎖秩仆，因此每一個事務都必須等待另一個事務釋放占有的鎖码泛。這就發(fā)生了死鎖了。
例子：兩個事務澄耍，事務都有兩個操作噪珊。當同時發(fā)生時，事務A鎖定first表齐莲，事務B鎖定second表痢站，導致了死鎖。
一次封鎖法要求每個事務必須一次將所有要使用的數(shù)據全部加鎖，否則就不能繼續(xù)執(zhí)行芒填。
此方法存在的問題：（一）一次將以后要用到的全部數(shù)據加鎖多望，加大封鎖范圍嫩舟，降低系統(tǒng)的并發(fā)度。（二）數(shù)據庫中數(shù)據是不斷變化的怀偷，原來不要求封鎖的數(shù)據家厌，在執(zhí)行過程中可能會變成封鎖對象，所以很難事先精確確定每個事務要封鎖的數(shù)據對象椎工，為此只能擴大封鎖范圍饭于，將事務在執(zhí)行過程中可能要封鎖的數(shù)據對象全部加鎖，這就更降低了并發(fā)度维蒙。
預先對數(shù)據對象規(guī)定一個封鎖熟悉怒掰吕，所有事務都按這個順序實行封鎖。如：在B樹結構的索引中颅痊，規(guī)定封鎖的順序必須從根結點開始殖熟，然后是下一級的子女結點，逐級封鎖斑响。
此方法存在的問題：（一）數(shù)據庫系統(tǒng)中封鎖的數(shù)據對象極多菱属，隨著數(shù)據的插入、刪除等操作而不斷變化舰罚，要維護這樣的資源的封鎖順序很難纽门，成本高。（二）事務的封鎖請求可隨著事務的執(zhí)行而動態(tài)地決定营罢，很難事先確定每一個事務要封鎖哪些對象赏陵，因此很難按規(guī)定的順序去加鎖。比如：規(guī)定數(shù)據對象的封鎖順序：A饲漾、B蝙搔、C、D考传、E吃型。事務T3起初要求封鎖數(shù)據對象B、C伙菊、E败玉，但當它封鎖了B、C后镜硕，才發(fā)現(xiàn)需要封鎖A运翼。
以上就是策略就是操作系統(tǒng)中廣為采用的預防死鎖的策略，但并不適合數(shù)據庫兴枯。所以數(shù)據庫系統(tǒng)一般采用診斷并解除死鎖的方法血淌。
數(shù)據庫系統(tǒng)中診斷死鎖的方法與操作系統(tǒng)類似，一般是用超時法或事務等待圖法。
指的是如果一個事務的等待時間超過了規(guī)定的時限悠夯，就認為發(fā)送死鎖癌淮。
不足：（一）有可能誤判死鎖，事務因為其他原因使等待時機超過時限沦补。（二）時限若設置得太長乳蓄，死鎖發(fā)生后不能及時發(fā)現(xiàn)。
指的是用事務等待圖動態(tài)反應所有事務的等待情況夕膀。
事務等待圖是一個有向圖G=(T,U)虚倒，其中T為結點的集合，每個結點表示正在運行的事務产舞。U為邊的集合魂奥，每條邊表示事務等待的情況。若T1等待T2易猫，則T1耻煤、T2之間劃一條有向邊，從T1指向T2准颓。事務等待圖動態(tài)地反映了所有事務的等待情況哈蝇。并發(fā)控制子系統(tǒng)周期性地檢測事務等待圖，如果發(fā)現(xiàn)圖中存在回路瞬场，則表示系統(tǒng)中出現(xiàn)了死鎖买鸽。
以上就是死鎖的診斷與解除了**涧郊。而且DBMS并發(fā)控制子系統(tǒng)一旦檢測到系統(tǒng)中存在死鎖贯被，就會設法解除。通常是選擇一個處理死鎖代價最小的事務妆艘，將其撤銷彤灶，釋放此事務持有的所有的鎖，使其他事務能繼續(xù)運行下去批旺。（而且要對撤銷的事務所執(zhí)行的數(shù)據修改操作進行恢復）
//測試表CREATETABLE`tb3`(`id`SMALLINT(5) UNSIGNEDNOTNULLAUTO_INCREMENT,`username`VARCHAR(30)NOTNULL,PRIMARYKEY(`id`))COLLATE='latin1_swedish_ci'ENGINE=InnoDBAUTO_INCREMENT=5;//測試數(shù)據幌陕，就這樣插幾條insertintotb3(username)values('fuzhu');
1
2
3
4
5
6
7
8
9
10
11
12
mysql> set autocommit=0;Query OK, 0 rows affected (0.00 sec)mysql> select * from tb3 where id=3 for update-> ;
+----+----------+| id | username |
+----+----------+|? 3 | Rose? ? |
+----+----------+1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
//再用一線程去訪問（wins、Linux再開個窗口）mysql>deletefromtb3 where id =2;//行鎖沒鎖住第二行Query OK,1row affected (0.00sec)mysql>deletefromtb3 where id =3;//因為前面有查詢修改事務鎖住了這一行//然后出現(xiàn)死鎖汽煮，最終出現(xiàn)以下信息：ERROR1205(HY000): Lockwaittimeout exceeded;tryrestarting transaction
1
2
3
4
5
6
mysql> show engine innodb status\G;***************************1. row ***************************Type: InnoDBName:Status:
=====================================170618 20:55:42 INNODB MONITOR OUTPUT? //INNODB引擎監(jiān)控
=====================================Per second averages calculated from the last 46 seconds
-----------------BACKGROUND THREAD
-----------------srv_master_thread loops: 19 1_second, 19 sleeps, 1 10_second, 10 background, 10flushsrv_master_thread log flush and writes: 19
----------SEMAPHORES
----------OS WAIT ARRAY INFO: reservation count 4, signal count 4Mutex spin waits 1, rounds 19, OS waits 0RW-shared spins 4, rounds 120, OS waits 4RW-excl spins 0, rounds 0, OS waits 0Spin rounds per wait: 19.00 mutex, 30.00 RW-shared, 0.00 RW-excl
------------TRANSACTIONS? ? ? ? ? ? //事務信息
------------Trx id counter 8450CPurge done for trx's n:o < 8432A undo n:o < 0History list length 1942LIST OF TRANSACTIONS FOR EACH SESSION:---TRANSACTION 8450A, not startedMySQL thread id 2, OS thread handle 0x15e4, query id 272 localhost 127.0.0.1 root---TRANSACTION 8450B, ACTIVE 35 sec starting index read? //事務ID=8450E搏熄，活躍了35s? mysql tables in use 1, locked 1? ? ? ? //表有一個在使用LOCK WAIT 3 lock struct(s), heap size 376, 2 row lock(s), undo log entries 1? ? ? ? //3個鎖，2個行鎖暇赤，1個undo log? MySQL thread id 3, OS thread handle 0x2130, query id 278 localhost 127.0.0.1 roo? ? ? ? ? ? //該事務的線程ID=3t updatingdelete from tb3 where id =3? ? ? ? ? ? //這是當前事務執(zhí)行的SQL心例，死鎖的sql-------TRX HAS BEEN WAITING 32 SEC FOR THIS LOCK TO BE GRANTED:RECORD LOCKS space id 0 page no 312 n bits 72 index`PRIMARY`of table`test`.`t? ? ? ? ? ? //上面SQL等待的鎖信息b3` trx id 8450B lock_mode X locks rec but not gap waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 00: len 2; hex 0003; asc? ;;1: len 6; hex 000000000908; asc? ? ? ;;2: len 7; hex 890000013b0110; asc? ? ;? ;;3: len 4; hex 526f7365; asc Rose;;------------------
---TRANSACTION 84509, ACTIVE 101 sec? ? ? ? //事務ID=84509荔烧，活躍了101s
2 lock struct(s), heap size 376, 1 row lock(s)? //2個鎖楚午，1個行鎖，1個undo log
MySQL thread id 1, OS thread handle 0x2358, query id 279 localhost 127.0.0.1 roo? ? ? ? ? ? ///該事務的線程ID=1
t
show engine innodb status? ? ? //這是當前事務執(zhí)行的SQL葛虐，查詢日志
--------FILE I/O? ? ? ? ? ? ? ? //IO流輸出日志
--------I/O thread 0 state: wait Windows aio (insert buffer thread)I/O thread 1 state: wait Windows aio (log thread)I/O thread 2 state: wait Windows aio (read thread)I/O thread 3 state: wait Windows aio (read thread)I/O thread 4 state: wait Windows aio (read thread)I/O thread 5 state: wait Windows aio (read thread)I/O thread 6 state: wait Windows aio (write thread)I/O thread 7 state: wait Windows aio (write thread)I/O thread 8 state: wait Windows aio (write thread)I/O thread 9 state: wait Windows aio (write thread)Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] ,ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0Pending flushes (fsync) log: 0; buffer pool: 0489 OS file reads, 12 OS? ? q file writes, 11 OS fsyncs0.00 reads/s, 0 avg bytes/read, 0.11 writes/s, 0.09 fsyncs/s
-------------------------------------INSERT BUFFER AND ADAPTIVE HASH INDEX? //插入緩沖區(qū)和自適應哈希索引
-------------------------------------Ibuf: size 1, free list len 0, seg size 2, 0 mergesmerged operations:insert 0, delete mark 0, delete 0discarded operations:insert 0, delete mark 0, delete 0Hash table size 222149, node heap has 1 buffer(s)0.00 hash searches/s, 0.04 non-hash searches/s
---LOG
---Log sequence number 1718594032Log flushed up to? 1718594032Last checkpoint at? 17185940320 pending log writes, 0 pending chkp writes12 log i/o's done, 0.04 log i/o's/second
----------------------BUFFER POOL AND MEMORY? ? ? //緩存池與內存
----------------------Total memory allocated 114835456; in additional pool allocated 0Dictionary memory allocated 267680Buffer pool size? 6848Free buffers? ? ? 6369Database pages? ? 478Old database pages 0Modified db pages? 0Pending reads 0Pending writes: LRU 0, flush list 0, single page 0Pages made young 0, not young 00.00 youngs/s, 0.00 non-youngs/sPages read 478, created 0, written 30.00 reads/s, 0.00 creates/s, 0.04 writes/sBuffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/sLRU len: 478, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Main thread id 3220, state: waiting for server activity
Number of rows inserted 0, updated 0, deleted 1, read 30
0.00 inserts/s, 0.00 updates/s, 0.02 deletes/s, 0.02 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================1 row in set (0.00 sec)ERROR:No query specified
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
二译株、MySQL各種鎖詳解（并針對MyISAM和InnoDB引擎）：
（1）在這之前先講述下MySQL在共享鎖瓜喇、排他鎖基礎上的一個鎖拓展–意向鎖。
（InnoDB特有歉糜，此外在索引加上中乘寒，InnoDB還有一種鎖叫間隙鎖。一會補充匪补。）
封鎖粒度結合封裝類型又是一層設計肃续。也就是說表鎖中使用又可分為共享鎖和排他鎖。同理行鎖叉袍。（MySQL意向鎖基于這個問題的出現(xiàn)而設計）
事務A鎖住了表中的一行始锚，讓這一行只能讀，不能寫喳逛。之后瞧捌，事務B申請整個表的寫鎖。如果事務B申請成功润文，那么理論上它就能修改表中的任意一行姐呐，這與A持有的行鎖是沖突的。數(shù)據庫需要避免這種沖突典蝌，就是說要讓B的申請被阻塞曙砂，直到A釋放了行鎖。數(shù)據庫要怎么判斷這個沖突呢骏掀？
普通認為兩步：step1：判斷表是否已被其他事務用表鎖鎖表鸠澈。step2：判斷表中的每一行是否已被行鎖鎖住。但是這樣的方法效率很低截驮，因為要遍歷整個表笑陈。
在意向鎖存在的情況下葵袭，事務A必須先申請表的意向共享鎖涵妥，成功后再申請一行的行鎖。
注意：申請意向鎖的動作是數(shù)據庫完成的贸弥，就是說窟坐，事務A申請一行的行鎖的時候，數(shù)據庫會自動先開始申請表的意向鎖，不需要我們程序員使用代碼來申請哲鸳。
行級鎖：開銷大歇终，加鎖慢；會出現(xiàn)死鎖逼龟；鎖定粒度最小评凝，發(fā)生鎖沖突的概率最低，并發(fā)度也最高腺律。
表級鎖：開銷小奕短，加鎖快；不會出現(xiàn)死鎖疾渣；鎖定粒度大篡诽，發(fā)生鎖沖突的概率最高崖飘，并發(fā)度最低榴捡。
頁級鎖：開銷和加鎖時間界于表鎖和行鎖之間；會出現(xiàn)死鎖朱浴；鎖定粒度界于表鎖和行鎖之間吊圾，并發(fā)度一般。
前面得知：mysql的表鎖有兩種模式：表共享讀鎖（table read lock）和表獨占寫鎖（table write lock）栓辜。（意向鎖是解決行鎖與表鎖沖突，不在此引擎中）垛孔。
所以對于MyISAM引擎的鎖兼容用一個常規(guī)圖描述：
表閱讀姿勢：先確定當前鎖模式藕甩，思考另一用戶請求，就去看請求鎖模式周荐，思考是否兼容狭莱。
請求鎖模式/是否兼容/當前鎖模式NONE讀鎖寫鎖
讀鎖是是否
寫鎖是否否
（一）MyISAM表的讀操作，不會阻塞其他用戶對同一個表的讀請求概作，但會阻塞對同一個表的寫請求腋妙。
（二）MyISAM表的寫操作，會阻塞其他用戶對同一個表的讀和寫操作讯榕。
（三）MyISAM表的讀辉阶、寫操作之間、以及寫操作之間是串行的瘩扼。
當一個線程獲得對一個表的寫鎖后谆甜，只有持有鎖線程可以對表進行更新操作。其他線程的讀集绰、寫操作都會等待规辱，直到鎖被釋放為止。
首先明確：1. MySQL認為寫請求一般比讀請求重要栽燕。 2. MyISAM在執(zhí)行查詢語句（SELECT）前罕袋，會自動給涉及的所有表加讀鎖，在執(zhí)行更新操作（UPDATE碍岔、DELETE浴讯、INSERT等）前，會自動給涉及的表加寫鎖蔼啦，這個過程并不需要用戶干預榆纽，因此用戶一般不需要直接用LOCK TABLE命令給MyISAM表顯式加鎖。
CREATETABLE`tb3`(`id`SMALLINT(5) UNSIGNEDNOTNULLAUTO_INCREMENT,`username`VARCHAR(30)NOTNULL,PRIMARYKEY(`id`))COLLATE='latin1_swedish_ci'ENGINE=MyISAMAUTO_INCREMENT=6;//測試數(shù)據捏肢，就這樣插幾條insertintotb3(username)values('fuzhu');
1
2
3
4
5
6
7
8
9
10
11
兩個窗口，一個開啟寫的事務辩棒，這樣的話在這個事務提交以前狼忱，其他事務都不可以修改與讀取這個表了膨疏。
直到這個事務提交，另一個事務才能讀取成功钻弄。且看時間停了多久
（三）另外成肘，MyISAM的鎖調度就是讓我們更好地去讓MySQL適應市場的需求，解決剛剛首要明確的問題斧蜕。
1. 通過指定啟動參數(shù)low-priority-updates双霍，使MyISAM引擎默認給予讀請求以優(yōu)先的權利。
2. 通過執(zhí)行命令SET LOW_PRIORITY_UPDATES=1批销，使該連接發(fā)出的更新請求優(yōu)先級降低洒闸。
3. 通過指定INSERT、UPDATE均芽、DELETE語句的LOW_PRIORITY屬性丘逸，降低該語句的優(yōu)先級。
雖然上面方式都挺極端的掀宋。但是MySQL也提供了一種折中的辦法來調節(jié)讀寫沖突深纲，即給系統(tǒng)參數(shù)max_write_lock_count設置一個合適的值，當一個表的讀鎖達到這個值后劲妙，MySQL變暫時將寫請求的優(yōu)先級降低湃鹊，給讀進程一定獲得鎖的機會。
MyISAM存儲引擎有一個系統(tǒng)變量镣奋，concurrent_insert币呵，專門用來控制并發(fā)插入行為的，值可以為0侨颈，1余赢，2.
concurrent_insert為1時候哈垢，如果mysql沒有空洞（中間沒有被刪除的行）妻柒，myISAM運行一個進程讀表的時候，另一個進程從表尾插入記錄耘分，這也是mysql默認設置举塔。
concurrent_insert為2時候，無論MyISAM表中有沒有空洞陶贼，都允許在表尾并行的插入啤贩。
一共三類：共享鎖拜秧，排他鎖，意向鎖章郁。其中意向鎖分為意向共享鎖和意向排他鎖枉氮。詳情請見前文志衍。
表閱讀姿勢：先確定當前鎖模式，思考另一用戶請求聊替，就去看請求鎖模式楼肪，思考是否兼容。
請求鎖模式/是否兼容/當前鎖模式共享鎖排他鎖意向共享鎖意向排他鎖
共享鎖兼容沖突兼容沖突
排他鎖沖突沖突沖突沖突
意向共享鎖兼容沖突兼容兼容
意向排他鎖沖突沖突兼容兼容
如果一個事務請求的鎖模式與當前的鎖模式兼容惹悄，innodb就將請求的鎖授予該事務春叫；反之，如果兩者不兼容泣港，該事務就要等待鎖釋放暂殖。意向鎖是Innodb自動加的，不需要用戶干預当纱。
對于UPDATE呛每、DELETE、INSERT語句坡氯，Innodb會自動給涉及的數(shù)據集加排他鎖（X）晨横；對于普通SELECT語句，Innodb不會加任何鎖箫柳。
//顯示共享鎖（S） ：SELECT*FROMtable_nameWHERE....LOCKINSHARE MODE //顯示排他鎖（X）：SELECT*FROMtable_nameWHERE....FORUPDATE.
1
2
3
4
使用select … in share mode獲取共享鎖手形，主要用在需要數(shù)據依存關系時，確認某行記錄是否存在悯恍，并確保沒有人對這個記錄進行update或者delete叁幢。
（五）InnoDB行鎖實現(xiàn)方式與驗證：（可能會遇到所有事務并發(fā)問題–InnoDB是事務引擎）
InnoDB行鎖是通過給索引上的索引項加鎖來實現(xiàn)的，這一點MySQL與Oracle不同坪稽，后者是通過再數(shù)據塊中曼玩，對相應數(shù)據行加鎖來實現(xiàn)的。InnoDB這種行鎖實現(xiàn)特點意味著：只有通過索引條件檢索數(shù)據窒百，innoDB才使用行級鎖黍判，否則InnoDB將使用表鎖，在實際開發(fā)中應當注意篙梢。
1.數(shù)據表準備：注意是沒有索引的表顷帖！沒有索引！沒有索引渤滞！唯一索引也是一種索引贬墩，不能用！
CREATETABLE`tb0`(`id`SMALLINT(5) UNSIGNEDNOTNULL,`id2`SMALLINT(5) UNSIGNEDNOTNULL)COLLATE='latin1_swedish_ci'ENGINE=InnoDB;//插兩條記錄insertintotb0values(1,1),(2,2);
1
2
3
4
5
6
7
8
9
第一個窗口（用戶）去訪問陶舞。先設置事務提交方式，再進行去查詢修改操作绪励。沒有索引情況下肿孵，一個用戶訪問唠粥，可事務還沒提交，第二個用戶就不能訪問停做。可見晤愧，行鎖是針對索引的！ｒ入纭官份！
1. 數(shù)據準備：表基于第一個驗證去改造（加索引–普通索引舅巷，索引值可出現(xiàn)多次。）蜀变，測試狀態(tài)也是：set autocommi = 0; 事務手動提交悄谐。
CREATETABLE`tb7`(`id`SMALLINT(5) UNSIGNEDNOTNULL,`id2`SMALLINT(5) UNSIGNEDNOTNULL,? ? INDEX id (id))COLLATE='latin1_swedish_ci'ENGINE=InnoDB;insertintotb7values(1,1),(2,2),(1,3);//并且添加索引：ALTERTABLEtb0ADDINDEX id1(id);
1
2
3
4
5
6
7
8
9
10
11
12
2. 還是兩個窗口（用戶）同時訪問：結果是第一個用戶訪問不再鎖表爬舰，而是鎖行。
因為Mysql行鎖是針對索引加的鎖少辣，不是針對記錄加的鎖凌摄，索引雖然訪問不同的記錄，但是他們的索引相同漓帅，是會出現(xiàn)沖突的锨亏，在設計數(shù)據庫時候需要注意這一點。上面只有將字段id2忙干，也添加上索引才能解決沖突問題器予。這也是mysql效率低的一個原因。
當我們用范圍條件而不是相等條件檢索數(shù)據捐迫，并請求共享或排他鎖時乾翔，InnoDB會給符合條件的已有數(shù)據的索引項加鎖；對于鍵值在條件范圍內但并不存在的記錄弓乙，叫做“間隙(GAP)”末融，InnoDB也會對這個“間隙”加鎖钧惧，這種鎖機制就是所謂的間隙鎖（Next-Key鎖）暇韧。
上面是一個范圍條件的檢索巧婶，InnoDB不僅會對符合條件的userid 值為101的記錄加鎖，也會對userid 大于101（這些記錄并不存在）的“間隙”加鎖涂乌。
一方面是為了防止幻讀艺栈，以滿足相關隔離級別的要求，對于上面的例子湾盒，要是不使用間隙鎖湿右，如果其他事務插入了userid 大于100的任何記錄，那么本事務如果再次執(zhí)行上述語句罚勾，就會發(fā)生幻讀毅人；另一方面，是為了滿足其恢復和復制的需要尖殃。有關其恢復和復制對機制的影響丈莺，以及不同隔離級別下InnoDB使用間隙鎖的情況。
可見送丰，在使用范圍條件檢索并鎖定記錄時缔俄，InnoDB這種加鎖機制會阻塞符合條件范圍內鍵值的并發(fā)插入，這往往會造成嚴重的鎖等待器躏。因此俐载，在實際開發(fā)中，尤其是并發(fā)插入比較多的應用登失，我們要盡量優(yōu)化業(yè)務邏輯遏佣，盡量使用相等條件來訪問更新數(shù)據，避免使用范圍條件壁畸。
對于InnoDB表捏萍，在絕大部分情況下都應該使用行級鎖太抓，因為事務和行鎖往往是我們之所以選擇InnoDB表的理由。但在個另特殊事務中令杈，也可以考慮使用表級鎖走敌。
2. 第二種情況是：事務涉及多個表僧须，比較復雜，很可能引起死鎖项炼，造成大量事務回滾担平。這種情況也可以考慮一次性鎖定事務涉及的表，從而避免死鎖锭部、減少數(shù)據庫因事務回滾帶來的開銷暂论。
（１）使用LOCK TALBES雖然可以給InnoDB加表級鎖取胎，但必須說明的是，表鎖不是由InnoDB存儲引擎層管理的湃窍，而是由其上一層ＭySQL Server負責的闻蛀，僅當autocommit=0、innodb_table_lock=1（默認設置）時坝咐，InnoDB層才能知道MySQL加的表鎖循榆，ＭySQL Server才能感知InnoDB加的行鎖，這種情況下墨坚，InnoDB才能自動識別涉及表級鎖的死鎖秧饮；否則，InnoDB將無法自動檢測并處理這種死鎖泽篮。
（２）在用LOCAK TABLES對InnoDB鎖時要注意盗尸，要將AUTOCOMMIT設為0，否則ＭySQL不會給表加鎖帽撑；事務結束前泼各，不要用UNLOCAK TABLES釋放表鎖，因為UNLOCK TABLES會隱含地提交事務亏拉；COMMIT或ROLLBACK產不能釋放用LOCAK TABLES加的表級鎖扣蜻，必須用UNLOCK TABLES釋放表鎖，正確的方式見如下語句及塘。
mysql> SET AUTOCOMMIT=0;mysql> LOCAK TABLES t1 WRITE, t2 READ,...;? ? [do something with tables t1 and here];mysql> COMMIT;mysql> UNLOCK TABLES;
1
2
3
4
5
6
7
ＭyISAM表鎖是無死鎖的，這是因為ＭyISAM總是一次性獲得所需的全部鎖笙僚，要么全部滿足芳肌，要么等待，因此不會出現(xiàn)死鎖。但是在InnoDB中亿笤，除單個SQL組成的事務外翎迁，鎖是逐步獲得的，這就決定了InnoDB發(fā)生死鎖是可能的净薛。
發(fā)生死鎖后汪榔，InnoDB一般都能自動檢測到，并使一個事務釋放鎖并退回罕拂，另一個事務獲得鎖揍异，繼續(xù)完成事務全陨。但在涉及外部鎖爆班，或涉及鎖的情況下，InnoDB并不能完全自動檢測到死鎖辱姨，這需要通過設置鎖等待超時參數(shù)innodb_lock_wait_timeout來解決柿菩。需要說明的是，這個參數(shù)并不是只用來解決死鎖問題雨涛，在并發(fā)訪問比較高的情況下枢舶，如果大量事務因無法立即獲取所需的鎖而掛起，會占用大量計算機資源替久，造成嚴重性能問題凉泄，甚至拖垮數(shù)據庫。我們通過設置合適的鎖等待超時閾值蚯根，可以避免這種情況發(fā)生后众。
通常來說，死鎖都是應用設計的問題颅拦，通過調整業(yè)務流程蒂誉、數(shù)據庫對象設計、事務大小距帅、以及訪問數(shù)據庫的SQL語句右锨，絕大部分都可以避免。
１）在應用中绍移，如果不同的程序會并發(fā)存取多個表，應盡量約定以相同的順序為訪問表讥电，這樣可以大大降低產生死鎖的機會蹂窖。如果兩個session訪問兩個表的順序不同，發(fā)生死鎖的機會就非常高允趟！但如果以相同的順序來訪問恼策，死鎖就可能避免。
2）在程序以批量方式處理數(shù)據的時候，如果事先對數(shù)據排序涣楷，保證每個線程按固定的順序來處理記錄分唾，也可以大大降低死鎖的可能。
3）在事務中狮斗，如果要更新記錄绽乔，應該直接申請足夠級別的鎖，即排他鎖碳褒，而不應該先申請共享鎖折砸，更新時再申請排他鎖，甚至死鎖沙峻。
4）在REPEATEABLE-READ隔離級別下睦授，如果兩個線程同時對相同條件記錄用SELECT…ROR UPDATE加排他鎖，在沒有符合該記錄情況下摔寨，兩個線程都會加鎖成功去枷。程序發(fā)現(xiàn)記錄尚不存在，就試圖插入一條新記錄是复，如果兩個線程都這么做删顶，就會出現(xiàn)死鎖。這種情況下淑廊，將隔離級別改成READ COMMITTED逗余，就可以避免問題。
5）當隔離級別為READ COMMITED時季惩，如果兩個線程都先執(zhí)行SELECT…FOR UPDATE看靠，判斷是否存在符合條件的記錄吉挣，如果沒有酒奶，就插入記錄杖小。此時，只有一個線程能插入成功碾阁，另一個線程會出現(xiàn)鎖等待输虱，當?shù)冢眰€線程提交后，第２個線程會因主鍵重出錯脂凶，但雖然這個線程出錯了宪睹，卻會獲得一個排他鎖！這時如果有第３個線程又來申請排他鎖蚕钦，也會出現(xiàn)死鎖亭病。對于這種情況，可以直接做插入操作嘶居，然后再捕獲主鍵重異常罪帖，或者在遇到主鍵重錯誤時促煮，總是執(zhí)行ROLLBACK釋放獲得的排他鎖。
盡管通過上面的設計和優(yōu)化等措施整袁，可以大減少死鎖菠齿，但死鎖很難完全避免。因此坐昙，在程序設計中總是捕獲并處理死鎖異常是一個很好的編程習慣绳匀。
