MySQL中InnoDB存儲引擎的鎖的基本使用教程_MySQL

BDB采用頁面鎖（page-leve locking）或表級鎖，默認為頁面鎖

InnoDB支持行級鎖（row-level locking）和表級鎖，默認為行級鎖

各種鎖特點

表級鎖：開銷小，加鎖快；不會出現死鎖；鎖定粒度大，發生沖突的概率最高，并發度最低

行級鎖：開銷大，加鎖慢；會出現死鎖；鎖定粒度最小，發生鎖沖突的概率最低，并發度也最高

頁面鎖：開銷和加鎖時間介于表鎖和行鎖之間；會出現死鎖；鎖定粒度介于表鎖和行鎖之間，并發度一般

InnoDB存儲引擎的鎖

InnoDB存儲引擎實現了如下兩種鎖

1、共享鎖（S Lock），允許事務讀一行數據

2、排他鎖（X Lock)，允許事務更新或者刪除一行數據

共享鎖和排他鎖的兼容如下圖所示

一致性的非鎖定讀

一致性的非鎖定行讀（consistent nonlocking read）是指InnoDB存儲引擎通過行多版本控制（multi versioning）的方式來讀取當前執行時間數據庫中行的數據。如果讀取的行正在執行DELETE、UPDATE操作，這是讀取操作不會因此而會等待行上鎖的釋放，相反，InnoDB會去讀取行的一個快照數據。

之所以稱其為非鎖定讀，因為不需要等待訪問的行上X鎖的釋放。快照數據是指改行之前版本的數據，該實現是通過undo段來實現的。但是在不同事務隔離級別下，讀取的方式不同，并不是每個事務隔離級別下讀取的都是一致性讀。

例如：

對于read committed的事務隔離級別，他總是讀取行的最新版本，如果行被鎖定了，則讀取該行版本的最新一個快照。

對于repeatable read(innoDB存儲引擎的默認隔離級別),總是讀取事務開始時的行數據。

非鎖定讀的機制大大提高了數據讀取的并發性，在Innodb存儲引擎默認設置下，這是默認的讀取方式，但是在某些情況下，可以對讀進行加鎖，比如：

1、顯式對讀進行加鎖，如使用 select --- for update ;select --- lock in share mode

2、在外鍵的插入和更新上，因為在外鍵的插入和更新上，對于數據的隔離性要求較高，在插入前需要掃描父表中的記錄是否存在，所以，在外鍵的插入刪除上，InnoDB會使用加S鎖的方式來實現。

InnoDB鎖的算法

1、Record Lock：單個行記錄上的鎖

2、Gap Lock：間隙鎖，鎖定一個范圍，但不包含記錄本身

3、Next-key Lock:Gap Lock+Record Lock，鎖定一個范圍，并且鎖定記錄本身

Record Lock總是會去鎖住索引記錄，如果InnoDB存儲引擎表建立的時候沒有設置任何一個索引，這時InnodB存儲引擎會使用隱式的主鍵來進行鎖定，在Repeatable Read隔離級別下，Next-key Lock 算法是默認的行記錄鎖定算法。

鎖帶來的問題

1、丟失更新

如何避免丟失更新：讓事務變成串行操作，而不是并發的操作，即對每個事務開始---對讀取記錄加排他鎖。

2、臟讀

臟讀即一個事務可以讀到另一個事務中未提交的數據，這違反了數據庫的隔離性。

臟讀發生的條件是需要事務的隔離級別為Read uncommitted。

3、不可重復讀

不可重復讀與臟讀的區別是：臟讀是讀到未提交的數據，而不可重復讀讀到的是已經提交的數據。

一般來說，不可重復讀是可以接受的，在InnoDB存儲引擎中，通過使用Next-Key Lock算法來避免不可重復讀的問題。

值得注意的是，默認情況下InnoDB存儲引擎不會回滾超時引發的錯誤異常。

死鎖的相關問題

1、死鎖發生的條件

互斥條件：一個資源每次只能被一個進程使用；請求與保持條件：一個進程因請求資源而阻塞時，對已獲得的資源保持不放；不剝奪條件:進程已獲得的資源，在末使用完之前，不能強行剝奪；循環等待條件:若干進程之間形成一種頭尾相接的循環等待資源關系。

2、死鎖檢測（根據網上的經驗）

Innodb檢測死鎖有兩種情況，一種是滿足循環等待條件，還有另一種策略：鎖結構超過mysql配置中設置的最大數量或鎖的遍歷深度超過設置的最大深度時，innodb也會判斷為死鎖（這是提高性能方面的考慮，避免事務一次占用太多的資源）。

因循環等待條件而產生的死鎖只有可能是四種形式：兩張表兩行記錄交叉申請互斥鎖、同一張表則存在主鍵索引鎖沖突、主鍵索引鎖與非聚簇索引鎖沖突、鎖升級導致的鎖等待隊列阻塞。

3、死鎖避免（根據網上的經驗）

1.如果使用insert…select語句備份表格且數據量較大，在單獨的時間點操作，避免與其他sql語句爭奪資源，或使用select into outfile加上load data infile代替 insert…select，這樣不僅快，而且不會要求鎖定
2. 一個鎖定記錄集的事務，其操作結果集應盡量簡短，以免一次占用太多資源，與其他事務處理的記錄沖突。
3.更新或者刪除表格數據，sql語句的where條件都是主鍵或都是索引，避免兩種情況交叉，造成死鎖。對于where子句較復雜的情況，將其單獨通過sql得到后，再在更新語句中使用。
4. sql語句的嵌套表格不要太多，能拆分就拆分，避免占有資源同時等待資源，導致與其他事務沖突。
5. 對定點運行腳本的情況，避免在同一時間點運行多個對同一表進行讀寫的腳本，特別注意加鎖且操作數據量比較大的語句。
6.應用程序中增加對死鎖的判斷，如果事務意外結束，重新運行該事務，減少對功能的影響。

4、死鎖解決

1)先執行show processlist找到死鎖線程號.然后Kill pid

2)Show innodb status檢查引擎狀態 ,可以看到哪些語句產生死鎖

3)查看information_schema架構下的innodb_locks、innodb_trx、innodb_lock_waits等表

PS：Mysql死鎖

既然談到死鎖，那附帶地就專門說一下。
何為死鎖？

死鎖是對資源的分配和使用不當而造成的。是兩個進程爭奪某一資源而出現相互等待的現象。具體的來講，出現死鎖需要滿足四個必要條件：
（1）互斥條件：每一個資源都只能被一個進程使用
（2）請求與保持條件：一個進程因請求資源而阻塞時，對已獲得的資源保持不放
（3）不剝奪條件：進程已獲得的資源，在末使用完之前，不能強行剝奪。
（4）循環等待條件：若干進程之間形成一種頭尾相接的循環等待資源關系。
很顯然，出現死鎖需要兩個或者兩個以上的進程，換句話說，死鎖發生在并發的程序中。在Mysql中，由于目前只有InnoDB引擎使用事務（InnoDB支持鎖），便有了InnoDB和死鎖的曠世基情。
死鎖的檢測

1、通過使用Show innodb status檢查引擎狀態 ,可以看到哪些語句產生deadlock
2、MySQL提供了一個information_schema，通過查看innodb_locks、innodb_trx、innodb_lock_waits這幾個表檢測死鎖。
因循環等待條件而產生的死鎖只有可能是四種形式：兩張表兩行記錄交叉申請互斥鎖、同一張表則存在主鍵索引鎖沖突、主鍵索引鎖與非聚簇索引鎖沖突、鎖升級導致的鎖等待隊列阻塞。

死鎖避免

1. 如果使用insert…select語句備份表格且數據量較大，在單獨的時間點操作，避免與其他sql語句爭奪資源，或使用select into outfile加上load data infile代替 insert…select，這樣不僅快，而且不會要求鎖定
   2. 一個鎖定記錄集的事務，其操作結果集應盡量簡短，以免一次占用太多資源，與其他事務處理的記錄沖突。
   3.更新或者刪除表格數據，sql語句的where條件都是主鍵或都是索引，避免兩種情況交叉，造成死鎖。對于where子句較復雜的情況，將其單獨通過sql得到后，再在更新語句中使用。
   4. sql語句的嵌套表格不要太多，能拆分就拆分，避免占有資源同時等待資源，導致與其他事務沖突。
   5. 對定點運行腳本的情況，避免在同一時間點運行多個對同一表進行讀寫的腳本，特別注意加鎖且操作數據量比較大的語句。
   6.應用程序中增加對死鎖的判斷，如果事務意外結束，重新運行該事務，減少對功能的影響。
   

聲明：本網頁內容旨在傳播知識，若有侵權等問題請及時與本網聯系，我們將在第一時間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com