一、定義

鎖是計(jì)算機(jī)協(xié)調(diào)多個(gè)進(jìn)程或線程并發(fā)訪問某一資源的機(jī)制
在數(shù)據(jù)庫(kù)中莺债，除傳統(tǒng)的計(jì)算資源（如CPU紧憾、RAM到千、I/O等）的爭(zhēng)用以外，數(shù)據(jù)也是一種供許多用戶共享的資源赴穗。如何保證數(shù)據(jù)并發(fā)訪問的一致性憔四、有效性是所有數(shù)據(jù)庫(kù)必須解決的一個(gè)問題，鎖沖突也是影響數(shù)據(jù)庫(kù)并發(fā) 訪問性能的一個(gè)重要因素般眉。從這個(gè)角度來說了赵，鎖對(duì)數(shù)據(jù)庫(kù)而已顯得尤其重要，也更加復(fù)雜甸赃。

二柿汛、鎖

2.1、讀寫鎖

• 讀鎖
  • 針對(duì)同一份數(shù)據(jù)埠对，多個(gè)讀操作可以同時(shí)進(jìn)行而不會(huì)互相影響
• 寫鎖
  • 當(dāng)前寫操作沒有完成前络断，它會(huì)阻斷其他寫鎖和讀鎖

2.2、表鎖

偏向MyISAM存儲(chǔ)引擎项玛，開銷小貌笨，加鎖快，無(wú)死鎖稍计；鎖定粒度大躁绸，發(fā)生鎖沖突的概率最高，并發(fā)度最低

2.2.1臣嚣、SQL

CREATE TABLE t_lock(
id INT NOT NULL PRIMARY KEY auto_increment,
name VARCHAR(20)
)engine myisam

2.2.2、查看表是否上鎖

SHOW OPEN TABLES

2.2.3剥哑、手動(dòng)給表加鎖

給表加 讀鎖
LOCK TABLE t_lock read

image.png

2.2.4硅则、釋放鎖

UNLOCK TABLES

image.png

2.2.5、鎖表-讀鎖

2.2.5.1 查詢被鎖表

SELECT * FROM t_lock

• 1株婴、讀鎖會(huì)話怎虫，可查讀鎖的表

• 2、其他會(huì)話困介，也可查讀鎖的表

2.2.5.2 更新被鎖表

UPDATE t_lock SET NAME='a2' WHERE id=1

• 1大审、讀鎖會(huì)話，不可操作

  
  
  image.png
• 2座哩、其他會(huì)話徒扶，阻塞

2.2.5.3 操作其它表

SELECT * FROM t_dept

• 1、讀鎖會(huì)話根穷，不可操作其它表

  
  
  image.png

• 2姜骡、其他會(huì)話导坟，可操作其它表

  
  
  read鎖.png

2.2.6、鎖表-寫鎖

LOCK TABLE t_lock WRITE

2.2.6.1 查詢被鎖表

SELECT * FROM t_lock

• 1圈澈、寫鎖會(huì)話惫周，一直等待
• 2、其他會(huì)話康栈，一直等待

2.2.7递递、小結(jié)

MyISAM在執(zhí)行查詢語(yǔ)句（SELECT）前，會(huì)自動(dòng)給涉及的所有表加讀鎖啥么，在執(zhí)行增刪改操作前漾狼，會(huì)自動(dòng)給涉及的表加寫鎖

• MySQL的表級(jí)鎖有兩種模式：
  • 表共享讀鎖（Table Read Lock）

  • 表獨(dú)占寫鎖（Table Write Lock）

    
    
    image.png
• 對(duì)MyISAM表的讀操作（加讀鎖），不會(huì)阻塞其他進(jìn)程對(duì)同一表的讀請(qǐng)求饥臂，但會(huì)阻塞對(duì)同一表的寫請(qǐng)求逊躁。只有當(dāng)讀鎖釋放后，才會(huì)執(zhí)行其它進(jìn)程的寫操作
• 對(duì)MyISAM表的寫操作（加寫鎖）隅熙，會(huì)阻塞其它進(jìn)程對(duì)同一表的讀和寫操作稽煤，只有當(dāng)寫鎖釋放后，才會(huì)執(zhí)行其他進(jìn)程的讀寫操作
• 簡(jiǎn)而言之囚戚，就是讀鎖會(huì)阻塞寫酵熙，但是不會(huì)阻塞讀
• 寫鎖會(huì)把讀和寫都阻塞

2.2.8、查看表鎖定

通過檢查 Table_locks_waited 和 Table_locks_immediate 狀態(tài)變量來分析系統(tǒng)上的表鎖定

• Table_locks_waited：出現(xiàn)表級(jí)鎖定爭(zhēng)用而發(fā)生等待的次數(shù)（不能立即獲取鎖的次數(shù)驰坊，每等待一次鎖值加1）匾二，此值高則說明存在著較嚴(yán)重的表級(jí)鎖爭(zhēng)用情況
• Table_locks_immediate：產(chǎn)生表級(jí)鎖定的次數(shù)，表示可以立即獲取鎖的查詢次數(shù)拳芙，每立即獲取值加1
• MyISAM的讀寫鎖調(diào)度是寫優(yōu)先察藐，這也是MyISAM不適合做寫為主表的引擎。因?yàn)閷戞i后舟扎，其他線程不能做任何操作分飞，大量的更新會(huì)使查詢很難得到鎖，從而造成永遠(yuǎn)阻塞

SHOW STATUS LIKE 'table%'

image.png

2.3睹限、行鎖

偏向InnoDB存儲(chǔ)引擎譬猫，開銷大，加鎖慢羡疗；會(huì)出現(xiàn)死鎖染服，鎖定粒度最小，發(fā)送鎖沖突的概率最低叨恨，并發(fā)度也最高

2.3.1柳刮、InnoDB優(yōu)勢(shì)

• 支持事務(wù)
• 行鎖

2.3.2、事務(wù)（Transaction）及其ACID屬性

事務(wù)是由一組SQL語(yǔ)句組成的邏輯處理單元，事務(wù)具有以下4個(gè)屬性诚亚，通常簡(jiǎn)稱為事務(wù)的ACID屬性

• 原子性（Atomicity）：事務(wù)是一個(gè)原子操作單元晕换，其對(duì)數(shù)據(jù)的修改，要么全都執(zhí)行站宗，要么全都不執(zhí)行
• 一致性（Consistent）：在事務(wù)開始和完成時(shí)闸准，數(shù)據(jù)都必須保持一致狀態(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樹索引或雙向鏈表）也都必須是正確的
• 隔離性（Isolation）：數(shù)據(jù)庫(kù)系統(tǒng)提供一定的隔離機(jī)制敏释，保證事務(wù)在不受外部并發(fā)操作影響的“獨(dú)立”環(huán)境執(zhí)行库快。這意味著事務(wù)處理過程中間狀態(tài)對(duì)外部是不可見的，反之亦然
• 持久性（Durable）：事務(wù)完成之后钥顽，它對(duì)于數(shù)據(jù)的修改是永久性的义屏，即使出現(xiàn)系統(tǒng)故障也能狗保持

2.3.3、并發(fā)處理帶來的問題

• 更新丟失（Lost Update）

  • 當(dāng)兩個(gè)或多個(gè)事務(wù)選擇同一行蜂大，然后基于最初選定的值更新該行時(shí)闽铐，由于每個(gè)事務(wù)都不知道其他事務(wù)的存在，就會(huì)發(fā)生丟失更新問題——最后的更新覆蓋了由其他事務(wù)所做的更新

• 臟讀（Dirty Reads）

  • 一個(gè)事務(wù)正在對(duì)一條記錄做修改奶浦，這個(gè)事務(wù)完成并提交前兄墅，這條記錄的數(shù)據(jù)就處于不一致的狀態(tài)；這時(shí)澳叉，另一個(gè)事務(wù)也來讀取同一條記錄隙咸，如果不加控制，第二個(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ú)效，不符合一致性原則

• 不可重復(fù)讀（Non-Repeatable Reads）

  • 一個(gè)事務(wù)在讀取某些數(shù)據(jù)后的某個(gè)時(shí)間继薛，再次讀取以前讀過的數(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ù)按相同的查詢條件重新讀取以前檢索過的數(shù)據(jù)，卻發(fā)現(xiàn)其他事務(wù)插入了滿足其查詢條件的新數(shù)據(jù)灌具，這種現(xiàn)象就稱為“幻讀”
  • 事務(wù)A讀取事務(wù)B提交的新增數(shù)據(jù)青团，不符合隔離性

• 小結(jié)

  
  
  小結(jié).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ā)訪問的能力

2.2.4料扰、行鎖測(cè)試

2.2.4.1凭豪、測(cè)試SQL

CREATE TABLE t_innodb_lock(
a INT(11),
b VARCHAR(16)
)ENGINE=INNODB

2.2.4.2、創(chuàng)建索引

CREATE INDEX idx_innodb_a ON t_innodb_lock(a)

CREATE INDEX idx_innodb_b ON t_innodb_lock(b)

2.2.4.3晒杈、取消自動(dòng)提交

SET autocommit=0;

2.2.4.4嫂伞、update執(zhí)行同一行

• 會(huì)話1執(zhí)行一下更新命令

UPDATE t_innodb_lock SET b='b1' WHERE a = 1

• 會(huì)話2執(zhí)行一下更新命令，造成阻塞

UPDATE t_innodb_lock SET b='b2' WHERE a = 1

• 會(huì)話1執(zhí)行COMMIT

COMMIT

• 會(huì)話2會(huì)立刻執(zhí)行

UPDATE t_innodb_lock SET b='b2' WHERE a = 1

SELECT * FROM t_innodb_lock

會(huì)話2更新成功.png

• 小結(jié)：在操作同一行的時(shí)候拯钻，會(huì)造成阻塞

2.2.4.5帖努、update執(zhí)行不同行

• 會(huì)話1執(zhí)行

UPDATE t_innodb_lock SET b='1b' WHERE a = 1

• 執(zhí)行查詢修改

SELECT * FROM t_innodb_lock

image.png

• 會(huì)話2執(zhí)行

UPDATE t_innodb_lock SET b='bb' WHERE a = 9

SELECT * FROM t_innodb_lock

image.png

• 只有會(huì)話1和會(huì)話2同時(shí)執(zhí)行

COMMIT

• 小結(jié)：操作不同行的時(shí)候不存在阻塞

2.2.4.6、索引使用不當(dāng)行鎖變成表鎖

• 初始表

  
  
  image.png
• 會(huì)話1執(zhí)行

UPDATE t_innodb_lock SET a=41 WHERE b = 4000

image.png

• 會(huì)話2執(zhí)行

UPDATE t_innodb_lock SET b='9001' WHERE a = 9

• 發(fā)現(xiàn)阻塞現(xiàn)象说庭，現(xiàn)在表現(xiàn)出表鎖的特性
• 因?yàn)樵跁?huì)話1中然磷，b索引時(shí)字符串，但是傳入的是4000刊驴，造成了索引b自動(dòng)類型轉(zhuǎn)換姿搜，從而造成了行鎖變成了表鎖

2.2.4.7、間隙鎖

• 間隙鎖概念
  當(dāng)我們用范圍條件而不是相等條件檢索數(shù)據(jù)捆憎，并請(qǐng)求共享或排他鎖時(shí)舅柜，InnoDB會(huì)給符合條件的已有數(shù)據(jù)基類的索引項(xiàng)加鎖；對(duì)于鍵值在條件范圍內(nèi)但并不存在的記錄躲惰，叫做“間隙（GAP）”致份，InnoDB也會(huì)對(duì)這個(gè)“間隙”加鎖，這種鎖機(jī)制就是所謂的間隙鎖（Next-Key鎖）
• 間隙鎖危害
  因?yàn)镼uery執(zhí)行過程中通過范圍查找的話础拨，他會(huì)鎖定整個(gè)范圍內(nèi)所有的索引鍵值氮块，即使這個(gè)鍵值并不存在。間隙鎖有一個(gè)比較致命的弱點(diǎn)诡宗，就是當(dāng)鎖定一個(gè)范圍鍵值之后滔蝉，即使某些不存在的鍵值也會(huì)被無(wú)辜的鎖定，而造成在鎖定的時(shí)候無(wú)法插入鎖定鍵值范圍內(nèi)的任何數(shù)據(jù)塔沃。
• 例如
• 會(huì)話1執(zhí)行

UPDATE t_innodb_lock SET b='RANGE' WHERE a > 1 AND a < 5

SELECT * FROM t_innodb_lock

image.png

• 會(huì)話2執(zhí)行

INSERT INTO t_innodb_lock VALUES(2, '2000')

• 一直在阻塞蝠引，當(dāng)會(huì)話2提交COMMIT，阻塞立即執(zhí)行

  
  
  image.png

小結(jié)

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

分析行鎖定

通過檢查 InnoDB_row_lock狀態(tài)變量來分析系統(tǒng)上的行鎖的爭(zhēng)奪情況

SHOW STATUS LIKE 'innodb_row_lock%'

image.png

• 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)在等待最常的一次所花的時(shí)間
• Innodb_row_lock_waits：系統(tǒng)啟動(dòng)后到現(xiàn)在總共等待的次數(shù)

原則

• 盡可能讓所有數(shù)據(jù)檢索都通過索引來完成，避免無(wú)索引行鎖升級(jí)為表鎖
• 合理設(shè)計(jì)索引超燃，盡量縮小鎖的范圍
• 盡可能較少檢索條件区拳，避免間隙鎖
• 盡量控制事務(wù)大小，減少鎖定資源量和時(shí)間長(zhǎng)度
• 盡可能低級(jí)別事務(wù)隔離