1 鎖的類型

1.1 行級(jí)鎖

MySQL提供兩種標(biāo)準(zhǔn)級(jí)別的行級(jí)鎖

• 共享鎖 (S Lock)：允許事務(wù)讀取一行數(shù)據(jù)
• 排他鎖 (X Lock)：允許事務(wù)刪除或更新一行數(shù)據(jù)

排他鎖與共享鎖的兼容性

1.2 意圖鎖

InnoDB支持多細(xì)粒度鎖定，允許在行級(jí)上的鎖和表級(jí)上的鎖同時(shí)存在

InnoDB還支持額外的鎖方式，稱之意向鎖，是表級(jí)別的鎖，用于在一個(gè)事務(wù)中揭示下一行將被請(qǐng)求的鎖的類型

• 意向共享鎖(IS Lock)：事務(wù)想獲取表中某幾行的共享鎖
• 意向排他鎖(IX Lock)：事務(wù)想獲取表中某幾行的排他鎖

意圖鎖需遵循以下協(xié)議：

• 在事務(wù)獲取某行的共享鎖前，首先需獲取IS鎖或更強(qiáng)的鎖
• 在事務(wù)獲取某行的獨(dú)占鎖前，首先在表上獲取IX鎖

X鎖與任何的鎖都不兼容，S鎖與IX鎖不兼容，剩下的均兼容

意向鎖只會(huì)阻塞表級(jí)別的鎖，并不會(huì)阻塞行級(jí)別的鎖

InnoDB存儲(chǔ)引擎的兩種加鎖操作：

• select … for update：對(duì)讀取的行加一個(gè)X鎖，其他事務(wù)加鎖將會(huì)被阻塞
• select … lock in share mode：對(duì)讀取的行記錄加一個(gè)S鎖，其他事務(wù)可以加S鎖，但對(duì)于X鎖將會(huì)被阻塞

1.3 自增鎖

事務(wù)插入到具有AUTO-INC列的一種特殊表級(jí)鎖，當(dāng)事務(wù)插入時(shí)，必須獲取自增鎖以獲取自增列的值

innodb_autoinc_lock_mode參數(shù)

• 值0：在5.1版本之前的自增長(zhǎng)實(shí)現(xiàn)方式，通過(guò)表鎖的AUTO-INC Locking方式
• 值1(默認(rèn))：對(duì)于Simple Inserts，使用互斥量對(duì)內(nèi)存的計(jì)數(shù)器進(jìn)行累加，對(duì)于Bulk Inserts，使用傳統(tǒng)表鎖的AUTO-INC Locking方式
• 值2：此模式任何時(shí)候都可以使用Row-Base Replication，保證了最大并發(fā)性能和Replication數(shù)據(jù)的同步

1.4 外鍵的鎖機(jī)制

外鍵的插入更新需先查詢父表記錄，若該行記錄被另一個(gè)事務(wù)加X(jué)鎖未提交，則后續(xù)插入操作首先會(huì)加入S鎖，則插入操作會(huì)被阻塞，提交后也會(huì)產(chǎn)生數(shù)據(jù)不一致的情況

2 鎖算法

InnoDB三個(gè)行鎖算法：

• Record Lock： 單行記錄的鎖
• Gap Lock：間隙鎖，鎖定一個(gè)范圍，不包含記錄本身
• Next-Key Lock：鎖定一個(gè)范圍，包含記錄本身，在可重復(fù)讀隔離級(jí)別下的默認(rèn)行記錄鎖定算法

3 鎖問(wèn)題

• 丟失更新

  多個(gè)事務(wù)同時(shí)修改某個(gè)行數(shù)據(jù)，導(dǎo)致某個(gè)事務(wù)的修改被覆蓋

• 臟讀

  臟數(shù)據(jù)概念：緩沖區(qū)已被修改的頁(yè)，尚未刷寫(xiě)會(huì)磁盤的數(shù)據(jù)

  臟讀：不同的事務(wù)下，讀取到其他事務(wù)未提交的數(shù)據(jù)，存在于讀未提交隔離級(jí)別下

• 不可重復(fù)讀

  同一個(gè)事內(nèi)，若數(shù)據(jù)被另一個(gè)事務(wù)修改提交，會(huì)導(dǎo)致兩次讀取的數(shù)據(jù)不一致，違反了事務(wù)一致性的原則

  在InnoDB里，通過(guò)Next-Key Lock算法避免了不可重復(fù)讀的問(wèn)題，在mysql里定義為Phantom Problem(幻讀)，Next-key算法會(huì)鎖定掃描到的以及間隙，避免了范圍內(nèi)的插入操作，避免了不可重復(fù)讀的問(wèn)題

4 阻塞

一個(gè)事務(wù)中的鎖需要等待另一個(gè)事務(wù)中的鎖釋放占用的資源，使用mutex數(shù)據(jù)結(jié)構(gòu)實(shí)現(xiàn)阻塞機(jī)制

InnoDB使用參數(shù)innodb_lock_wait_timeout控制鎖的等待時(shí)間，參數(shù)innodb_rollback_on_timeout設(shè)定是否進(jìn)行回滾，默認(rèn)OFF表示不回滾，lock參數(shù)可在數(shù)據(jù)庫(kù)運(yùn)行時(shí)動(dòng)態(tài)調(diào)整，而rollback參數(shù)是靜態(tài)的，僅可以在啟動(dòng)時(shí)調(diào)整

5 死鎖

數(shù)據(jù)庫(kù)在并行情況下可能會(huì)發(fā)生死鎖，存在于兩種資源之間的相互等待

InnoDB在偵測(cè)到死鎖后，會(huì)回滾事務(wù)。如oracle，死鎖多發(fā)生于未對(duì)外鍵添加索引，而InnoDB引擎會(huì)自動(dòng)進(jìn)行添加，不可人為刪除外鍵索引，因此較好的解決了這種問(wèn)題

6 鎖升級(jí)

定義：將當(dāng)前鎖的粒度降低，例如把一個(gè)表的100個(gè)行鎖升級(jí)成一個(gè)頁(yè)鎖，或頁(yè)鎖升級(jí)成表鎖，為避免鎖的開(kāi)銷，會(huì)頻繁出現(xiàn)鎖升級(jí)的現(xiàn)象

mssql在05版之后支持行鎖，當(dāng)一條SQL在一個(gè)對(duì)象的鎖數(shù)量超過(guò)閾值5000或鎖資源占用的內(nèi)存超過(guò)40%會(huì)發(fā)生鎖升級(jí)

InnoDB中不存在鎖升級(jí)的問(wèn)題，MySQL里鎖的開(kāi)銷與數(shù)量無(wú)關(guān)，類似Oracle的設(shè)計(jì)