mysql資料庫鎖有幾種,mysql有哪幾種鎖

本文目錄一覽：

• 1、mysql中的鎖都有哪些
• 2、深入理解MySQL資料庫各種鎖（總結）
• 3、下列什麼不是mysql資料庫鎖級別

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資料庫鎖級別

MySQL有三種鎖的級別：頁級、表級、行級。MySQL是一個關係型資料庫管理系統，由瑞典MySQL AB 公司開發，屬於 Oracle 旗下產品。MySQL 是最流行的關係型資料庫管理系統之一，在 WEB 應用方面，MySQL是最好的RDBMS應用軟體之一。