mysql資料庫的全局鎖和表鎖,mysql查詢鎖表的sql

本文目錄一覽：

• 1、mysql中的鎖都有哪些
• 2、MySQL資料庫表被鎖、解鎖，刪除事務
• 3、nysql全備需要鎖表嗎
• 4、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資料庫表被鎖、解鎖，刪除事務

在程序員的職業生涯中，總會遇到資料庫表被鎖的情況，前些天就又撞見一次。由於業務突發需求，各個部門都在批量操作、導出數據，而資料庫又未做讀寫分離，結果就是：資料庫的某張表被鎖了！

用戶反饋系統部分功能無法使用，緊急排查，定位是資料庫表被鎖，然後進行緊急處理。這篇文章給大家講講遇到類似緊急狀況的排查及解決過程，建議點贊收藏，以備不時之需。

用戶反饋某功能頁面報502錯誤，於是第一時間看服務是否正常，資料庫是否正常。在控制台看到資料庫CPU飆升，堆積大量未提交事務，部分事務已經阻塞了很長時間，基本定位是資料庫層出現問題了。

查看阻塞事務列表，發現其中有鎖表現象，本想利用控制台直接結束掉阻塞的事務，但控制台賬號許可權有限，於是通過客戶端登錄對應賬號將鎖表事務kill掉，才避免了情況惡化。

下面就聊聊，如果當突然面對類似的情況，我們該如何緊急響應？

想像一個場景，當然也是軟體工程師職業生涯中會遇到的一種場景：原本運行正常的程序，某一天突然資料庫的表被鎖了，業務無法正常運轉，那麼我們該如何快速定位是哪個事務鎖了表，如何結束對應的事物？

首先最簡單粗暴的方式就是：重啟MySQL。對的，網管解決問題的神器——「重啟」。至於後果如何，你能不能跑了，要你自己三思而後行了！

重啟是可以解決表被鎖的問題的，但針對線上業務很顯然不太具有可行性。

下面來看看不用跑路的解決方案：

遇到資料庫阻塞問題，首先要查詢一下表是否在使用。

如果查詢結果為空，那麼說明表沒在使用，說明不是鎖表的問題。

如果查詢結果不為空，比如出現如下結果：

則說明表（test）正在被使用，此時需要進一步排查。

查看資料庫當前的進程，看看是否有慢SQL或被阻塞的線程。

執行命令：

該命令只顯示當前用戶正在運行的線程，當然，如果是root用戶是能看到所有的。

在上述實踐中，阿里雲控制台之所以能夠查看到所有的線程，猜測應該使用的就是root用戶，而筆者去kill的時候，無法kill掉，是因為登錄的用戶非root的資料庫賬號，無法操作另外一個用戶的線程。

如果情況緊急，此步驟可以跳過，主要用來查看核對：

如果情況緊急，此步驟可以跳過，主要用來查看核對：

看事務表INNODB_TRX中是否有正在鎖定的事務線程，看看ID是否在show processlist的sleep線程中。如果在，說明這個sleep的線程事務一直沒有commit或者rollback，而是卡住了，需要手動kill掉。

搜索的結果中，如果在事務表發現了很多任務，最好都kill掉。

執行kill命令：

對應的線程都執行完kill命令之後，後續事務便可正常處理。

針對緊急情況，通常也會直接操作第一、第二、第六步。

這裡再補充一些MySQL鎖相關的知識點：資料庫鎖設計的初衷是處理並發問題，作為多用戶共享的資源，當出現並發訪問的時候，資料庫需要合理地控制資源的訪問規則，而鎖就是用來實現這些訪問規則的重要數據結構。

根據加鎖的範圍，MySQL裡面的鎖大致可以分成全局鎖、表級鎖和行鎖三類。MySQL中表級別的鎖有兩種：一種是表鎖，一種是元數據鎖（metadata lock，MDL)。

表鎖是在Server層實現的，ALTER TABLE之類的語句會使用表鎖，忽略存儲引擎的鎖機制。表鎖通過lock tables… read/write來實現，而對於InnoDB來說，一般會採用行級鎖。畢竟鎖住整張表影響範圍太大了。

另外一個表級鎖是MDL（metadata lock），用於並發情況下維護數據的一致性，保證讀寫的正確性，不需要顯式的使用，在訪問一張表時會被自動加上。

常見的一種鎖表場景就是有事務操作處於：Waiting for table metadata lock狀態。

MySQL在進行alter table等DDL操作時，有時會出現Waiting for table metadata lock的等待場景。

一旦alter table TableA的操作停滯在Waiting for table metadata lock狀態，後續對該表的任何操作（包括讀）都無法進行，因為它們也會在Opening tables的階段進入到Waiting for table metadata lock的鎖等待隊列。如果核心表出現了鎖等待隊列，就會造成災難性的後果。

通過show processlist可以看到表上有正在進行的操作（包括讀），此時alter table語句無法獲取到metadata 獨佔鎖，會進行等待。

通過show processlist看不到表上有任何操作，但實際上存在有未提交的事務，可以在information_schema.innodb_trx中查看到。在事務沒有完成之前，表上的鎖不會釋放，alter table同樣獲取不到metadata的獨佔鎖。

處理方法：通過 select * from information_schema.innodb_trxG, 找到未提交事物的sid，然後kill掉，讓其回滾。

通過show processlist看不到表上有任何操作，在information_schema.innodb_trx中也沒有任何進行中的事務。很可能是因為在一個顯式的事務中，對錶進行了一個失敗的操作（比如查詢了一個不存在的欄位），這時事務沒有開始，但是失敗語句獲取到的鎖依然有效，沒有釋放。從performance_schema.events_statements_current表中可以查到失敗的語句。

處理方法：通過performance_schema.events_statements_current找到其sid，kill 掉該session，也可以kill掉DDL所在的session。

總之，alter table的語句是很危險的（核心是未提交事務或者長事務導致的），在操作之前要確認對要操作的表沒有任何進行中的操作、沒有未提交事務、也沒有顯式事務中的報錯語句。

如果有alter table的維護任務，在無人監管的時候運行，最好通過lock_wait_timeout設置好超時時間，避免長時間的metedata鎖等待。

關於MySQL的鎖表其實還有很多其他場景，我們在實踐的過程中盡量避免鎖表情況的發生，當然這需要一定經驗的支撐。但更重要的是，如果發現鎖表我們要能夠快速的響應，快速的解決問題，避免影響正常業務，避免情況進一步惡化。所以，本文中的解決思路大家一定要收藏或記憶一下，做到有備無患，避免突然狀況下抓瞎。

nysql全備需要鎖表嗎

需要的。

MySQL的鎖按照範圍可以分為全局鎖、表鎖、行鎖，其中行鎖是由資料庫引擎實現的，並不是所有的引擎都提供行鎖，MyISAM就不支持行鎖.

MYSQL資料庫怎麼查看 哪些表被鎖了

以下五種方法可以快速定位全局鎖的位置，僅供參考。

方法1：利用 metadata_locks 視圖

此方法僅適用於 MySQL 5.7 以上版本，該版本 performance_schema 新增了 metadata_locks，如果上鎖前啟用了元數據鎖的探針（默認是未啟用的），可以比較容易的定位全局鎖會話。

方法2：利用 events_statements_history 視圖此方法適用於 MySQL 5.6 以上版本，啟用 performance_schema.eventsstatements_history（5.6 默認未啟用，5.7 默認啟用），該表會 SQL 歷史記錄執行，如果請求太多，會自動清理早期的信息，有可能將上鎖會話的信息清理掉。

方法3：利用 gdb 工具如果上述兩種都用不了或者沒來得及啟用，可以嘗試第三種方法。利用 gdb 找到所有線程信息，查看每個線程中持有全局鎖對象，輸出對應的會話 ID，為了便於快速定位，我寫成了腳本形式。也可以使用 gdb 交互模式，但 attach mysql 進程後 mysql 會完全 hang 住，讀請求也會受到影響，不建議使用交互模式。

方法4：show processlist

如果備份程序使用的特定用戶執行備份，如果是 root 用戶備份，那 time 值越大的是持鎖會話的概率越大，如果業務也用 root 訪問，重點是 state 和 info 為空的，這裡有個小技巧可以快速篩選，篩選後嘗試 kill 對應 ID，再觀察是否還有 wait global read lock 狀態的會話。

方法5：重啟試試！