mysql資料庫鎖介紹,資料庫鎖表如何解鎖 mysql

本文目錄一覽：

• 1、面試你應該知道的 MySQL 的鎖
• 2、mysql 的鎖以及間隙鎖
• 3、mysql中的鎖都有哪些
• 4、深入理解MySQL資料庫各種鎖（總結）
• 5、mysql鎖定了資料庫表只能寫，為什麼還可以讀？
• 6、mysql中鎖的類型有哪些

面試你應該知道的 MySQL 的鎖

背景

資料庫的鎖是在多線程高並發的情況下用來保證數據穩定性和一致性的一種機制。MySQL 根據底層存儲引擎的不同，鎖的支持粒度和實現機制也不同。MyISAM 只支持表鎖，InnoDB 支持行鎖和表鎖。目前 MySQL 默認的存儲引擎是 InnoDB，這裡主要介紹 InnoDB 的鎖。

使用 InnoDB 的兩大優點：一是支持事務；二是支持行鎖。

在高並發的情況下事務的並發處理會帶來幾個問題

由於高並發事務帶來這幾個問題，所以就產生了事務的隔離級別

舉個例子

按照上面 1，2，3，4 的順序執行會發現第 4 步被阻塞了，必須執行完第 5 步後才能插入成功。這裡我們會很奇怪明明鎖住的是uid=6 的這一行，為什麼不能插入 5 呢？原因就是這裡採用了 next-key 的演算法，鎖住的是（3,10）整個區間。感興趣的可以試一下。

今天給大家分享了一下 MySQL 的 InnoDB 的事務以及鎖的一些知識，通過自己的實際上手實踐對這塊更加熟悉了，希望大家在看的時候也可以動手試試，這樣更能體會，理解的更深刻。

mysql 的鎖以及間隙鎖

mysql 為並發事務同時對一條記錄進行讀寫時，提出了兩種解決方案：

1）使用 mvcc 的方法，實現多事務的並發讀寫，但是這種讀只是「快照讀」，一般讀的是歷史版本數據，還有一種是「當前讀」，一般加鎖實現「當前讀」，或者 insert、update、delete 也是當前讀。

2）使用加鎖的方法，鎖分為共享鎖（讀鎖），排他鎖（寫鎖）

快照讀：就是select

當前讀：特殊的讀操作，插入/更新/刪除操作，屬於當前讀，處理的都是當前的數據，需要加鎖。

mysql 在 RR 級別怎麼處理幻讀的呢？一般來說，RR 級別通過 mvcc 機制，保證讀到低於後面事務的數據。但是 select for update 不會觸發 mvcc，它是當前讀。如果後面事務插入數據並提交，那麼在 RR 級別就會讀到插入的數據。所以，mysql 使用 行鎖 + gap 鎖（簡稱 next-key 鎖）來防止當前讀的時候插入。

Gap Lock在InnoDB的唯一作用就是防止其他事務的插入操作，以此防止幻讀的發生。

Innodb自動使用間隙鎖的條件：

mysql中的鎖都有哪些

MySQL 中有哪些鎖？

資料庫中鎖的設計初衷處理並發問題，作為多用戶共享資源，當出現並發訪問的時候，資料庫需要合理控制資源訪問規則。鎖就是實現這些訪問規則中的重要數據。

鎖的分類

根據加鎖範圍，MySQL 裡面的鎖可以分成 全局鎖 、 表級鎖 、 行鎖 三類。

全局鎖

全局鎖，就是對整個資料庫實例加鎖，MySQL 提供了一個加全局讀鎖的方法，命令是:

Flush tables with read lock (FTWRL)

當需要整個庫只讀狀態的時候，可以使用這個命令，之後其他線程的：數據更新語句（增刪改），數據定義語句（建表，修改表結構）和更新事務的提交語句將會被阻塞。

全局鎖的使用場景

全局鎖的定型使用場景，做 全庫邏輯備份 。也就是把整個庫每個表都 Select 出來，然後存成文本。

如何整個庫都只讀，會有什麼問題？ 如果你在主庫上備份，那麼在備份期間都不能執行更想，業務就基本上停擺。 如果在從庫上備份，那麼備份期間從庫不能執行主庫同步過來的 binlog ,會導致從延遲。 既然要全庫只讀， 為什麼不使用set global readonly=true的方式呢?

readonly 方式也可以讓全庫進入只讀狀態,但我還是會建議你用FTWRL方式， 主要有兩個原因：

一是， 在有些系統中， readonly的值會被用來做其他邏輯，比如用來判斷一個庫是主庫還是備庫。因此，修改global變數的方式影響面更大， 我不建議你使用。 二是， 在異常處理機制上有差異。如果執行FTWRL命令之後由於客戶端發生異常斷開， 那麼MySQL會自動釋放這個全局鎖， 整個庫回到可以正常更新的狀態。而將整個庫設置為readonly之後， 如果客戶端發生異常， 則資料庫就會一直保持readonly狀態， 這樣會導致整個庫長時間處於不可寫狀態， 風險較高 表級別鎖

MySQL 裡面表級別的鎖有兩種：一種是表鎖，一種是元數據鎖（meta data lok, MDL）。表鎖的語法是 ：

lock tables … read/write

與 FTWRL 類似,可以使用 unlock tables 主動釋放鎖，也可以在客戶端斷開的時候自動釋放。需要注意的是，lock tables語法除了會限制別的線程的讀寫外，也限定了本線程接下來的操作對象。

MDL 表級鎖

MDL 不需要顯示使用，在訪問一個表的時候自動加上， MDL 保證讀寫的正確性，也就是說在查詢數據時，不允許有其他線程對這個表結構做變更。

什麼操作會加 MDL 鎖？

在MySQL 5.5版本中引入了MDL， 當對一個表做增刪改查操作的時候，加 MDL讀鎖 ；當要對錶做結構變更操作的時候，加 MDL寫鎖 。

讀鎖之間不互斥，因此可以有多個線程同時對一張表增刪改查。 讀寫之間、寫鎖之間是互斥的，用來保證變更表結構操作的安全性，如果有兩個線程要同時給一個表加欄位，其中一個要等另外一個執行完才能執行。 更改表結構要注意哪些？

給一個表加欄位， 或者修改欄位， 或者加索引， 需要掃描全表的數據。在對大表操作的時候， 你肯定會特別小心， 以免對線上服務造成影響。而實際上， 即使是小表， 操作不慎也會出問題，導致整個庫的線程爆滿。

舉個例子

我們來看一下下面的操作序列， 假設表t是一個小表。

image

session A先啟動， 這時候會對錶t加一個 MDL讀鎖 。由於session B需要的也是 MDL讀鎖 ， 因此可以正常執行。 session C會被blocked， 是因為session A的MDL讀鎖還沒有釋放， 而session C需要MDL寫鎖， 因此只能被阻塞，讀寫鎖互斥。 如果只有session C自己被阻塞還沒什麼關係， 但是之後所有要在表t上新申請MDL讀鎖的請求也會被session C阻塞。前面我們說了，所有對錶的增刪改查操作都需要先申請MDL讀鎖， 就都被鎖住， 等於這個表現在完全不可讀寫了。

如果某個表上的查詢語句頻繁， 而且客戶端有重試機制，也就是說超時後會再起一個新session 再請求的話， 這個 庫的線程很快就會爆滿 。事務中的MDL鎖， 在語句執行開始時申請， 但是語句結束後並不會馬上釋放， 而會等到整個事務提交後再釋放。

怎麼解決這個 更改表結構問題

比較理想的機制是， 在alter table語句裡面設定等待時間， 如果在這個指定的等待時間裡面能夠拿到MDL寫鎖最好， 拿不到也不要阻塞後面的業務語句， 先放棄。

ALTER TABLE tbl_name NOWAIT add column … ALTER TABLE tbl_name WAIT N add column …

深入理解MySQL資料庫各種鎖（總結）

MyISAM和InnoDB存儲引擎使用的鎖：

封鎖粒度小：

由於InnoDB存儲引擎支持的是行級別的鎖，因此意向鎖（因為意向鎖是表鎖）其實不會阻塞除全表掃以外的任何請求。故表級意向鎖與行級鎖的兼容性如下所示

參考

參考

行鎖的三種演算法：

這條語句阻止其他事務插入10和20之間的數字，無論這個數字是否存在。 間隙可以跨越0個，單個或多個索引值。

共享鎖：

排他鎖：

樂觀鎖：總是假設最好的情況，每次去拿數據的時候都認為別人不會修改（天真）， 操作數據時不會上鎖 ，但是 更新時會判斷在此期間有沒有別的事務更新這個數據，若被更新過，則失敗重試 ；適用於讀多寫少的場景。

樂觀鎖的實現方式 有：

關閉自動提交後，我們需要手動開啟事務。

上述就實現了悲觀鎖，悲觀鎖就是悲觀主義者，它會認為我們在事務A中操作數據1的時候，一定會有事務B來修改數據1,所以，在第2步我們將數據查詢出來後直接加上排它鎖（X）鎖，防止別的事務來修改事務1，直到我們commit後，才釋放了排它鎖。

mysql鎖定了資料庫表只能寫，為什麼還可以讀？

鎖的作用，就是把許可權歸為私有，其它人用不了。你自已把表鎖了，自已當然還能用。

1、表級別的鎖定是MySQL各存儲引擎中最大顆粒度的鎖定機制。該鎖定機制最大的特點是實現邏輯非常簡單，帶來的系統負面影響最小。所以獲取鎖和釋放鎖的速度很快。由於表級鎖一次會將整個表鎖定，所以可以很好的避免困擾我們的死鎖問題。

2、資料庫鎖定機制簡單來說就是資料庫為了保證數據的一致性而使各種共享資源在被並發訪問訪問變得有序所設計的一種規則。

3、對於任何一種資料庫來說都需要有相應的鎖定機制，所以MySQL自然也不能例外。

4、MySQL資料庫由於其自身架構的特點，存在多種數據存儲引擎，每種存儲引擎所針對的應用場景特點都不太一樣，為了滿足各自特定應用場景的需求，每種存儲引擎的鎖定機制都是為各自所面對的特定場景而優化設計，所以各存儲引擎的鎖定機制也有較大區別。

5、總的來說，MySQL各存儲引擎使用了三種類型（級別）的鎖定機制：行級鎖定，頁級鎖定和表級鎖定。下面我們先分析一下MySQL這三種鎖定的特點和各自的優劣所在。

mysql中鎖的類型有哪些

全局鎖

顧名思義，全局鎖就是對整個資料庫實例加鎖。MySQL 提供了一個加全局讀鎖的方法，命令是 Flush tables with read lock (FTWRL)。當你需要讓整個庫處於只讀狀態的時候，可以使用這個命令，之後其他線程的以下語句會被阻塞：數據更新語句（數據的增刪改）、數據定義語句（包括建表、修改表結構等）和更新類事務的提交語句。

表級鎖

MySQL 裡面表級別的鎖有兩種：一種是表鎖，一種是元數據鎖（meta data lock，MDL)。

表鎖

表鎖的語法是 lock tables … read/write。與 FTWRL 類似，可以用 unlock tables 主動釋放鎖，也可以在客戶端斷開的時候自動釋放。需要注意，lock tables 語法除了會限制別的線程的讀寫外，也限定了本線程接下來的操作對象。

元數據鎖

MDL 不需要顯式使用，在訪問一個表的時候會被自動加上。MDL 的作用是，保證讀寫的正確性。你可以想像一下，如果一個查詢正在遍歷一個表中的數據，而執行期間另一個線程對這個表結構做變更，刪了一列，那麼查詢線程拿到的結果跟表結構對不上，肯定是不行的。