MySQL全局鎖和表鎖的深入理解

 更新時間:2019年06月23日 11:45:12   作者:keme   我要評論

這篇文章主要給大家介紹了關于MySQL全局鎖和表鎖的相關資料,文中通過示例代碼介紹的非常詳細,對大家學習或者使用MySQL具有一定的參考學習價值,需要的朋友們下面來一起學習學習吧

前言

根據加鎖的范圍,MySQL 里面的鎖大致可以分成全局鎖,表級鎖,行鎖。

行鎖已經在前面幾篇文章說過

1. 全局鎖

全局鎖就是對整個數據庫實例加鎖。MySQL 提供了一個加全局讀鎖的方法,命令是Flush tables with read lock (FTWRL)

當你需要讓整個庫處于只讀狀態的時候,可以使用這個命令,之后其他線程的以下語句會被阻塞:數據更新語句(數據的增刪改)、數據定義語句(包括建表、修改表結構等)和更新類事務的提交語句。

1.1 全局鎖使用場景

全局鎖的典型使用場景是,做全庫邏輯備份(mysqldump)。重新做主從時候

也就是把整庫每個表都 select 出來存成文本。

以前有一種做法,是通過 FTWRL 確保不會有其他線程對數據庫做更新,然后對整個庫做備份。注意,在備份過程中整個庫完全處于只讀狀態。

數據庫只讀狀態的危險性:

如果你在主庫上備份,那么在備份期間都不能執行更新,業務基本上就能停止。如果你在從庫上備份,那么備份期間從庫不能執行主庫同步過來的binlog,會導致主從延遲。

注:上面邏輯備份,是不加--single-transaction參數

看來加全局鎖不太好。但是細想一下,備份為什么要加鎖呢?來看一下不加鎖會有什么問題?

1.2 不加鎖產生的問題

比如手機卡,購買套餐信息

這里分為兩張表 u_acount (用于余額表),u_pricing (資費套餐表)
步驟:
1 . u_account 表中數據 用戶A 余額:300
u_pricing 表中數據 用戶A 套餐:空

2. 發起備份,備份過程中先備份u_account表,備份完了這個表,這個時候u_account 用戶余額是300
3. 這個時候套用戶購買了一個資費套餐100,餐購買完成,寫入到u_print套餐表購買成功,備份期間的數據。
4. 備份完成

可以看到備份的結果是,u_account 表中的數據沒有變, u_pricing 表中的數據 已近購買了資費套餐100.

哪這時候用這個備份文件來恢復數據的話,用戶A 賺了100 ,用戶是不是很舒服啊。但是你的想想公司利益啊。  

也就是說,不加鎖的話,備份系統備份的得到的庫不是一個邏輯時間點,這個數據是邏輯不一致的。

1.3 為什么需要全局讀鎖(FTWRL)

可能有的人在疑惑,官方自帶的邏輯備份工具是 mysqldump。當 mysqldump 使用參數--single-transaction的時候,導數據之前就會啟動一個事務,來確保拿到一致性快照視圖。而由于 MVCC 的支持,這個過程中數據是可以正常更新的。

為什么還需要 FTWRL 呢?
一致性讀是好,但前提是引擎要支持這個隔離級別。比如,對于 MyISAM 這種不支持事務的引擎,如果備份過程中有更新,總是只能取到最新的數據,那么就破壞了備份的一致性。這時,我們就需要使用FTWRL 命令了。

所以,single-transaction 方法只適用于所有的表使用事務引擎的庫。如果有的表使用了不支持事務的引擎,那么備份就只能通過 FTWRL 方法。這往往是 DBA 要求業務開發人員使用 InnoDB 替代 MyISAM 的原因之一。

1.4 全局鎖兩種方法

一.FLUSH TABLES WRITE READ LOCK

二.set global readonly=true

既然要全庫只讀,為什么不使用 set global readonly=true 的方式呢?確實 readonly 方式也可以讓全庫進入只讀狀態,但我還是會建議你用 FTWRL 方式,主要有幾個原因:

一是,在有些系統中,readonly 的值會被用來做其他邏輯,比如用來判斷一個庫是主庫還是備庫。因此,修改 global 變量的方式影響面更大,我不建議你使用。

二是,在異常處理機制上有差異。如果執行FTWRL 命令之后由于客戶端發生異常斷開,那么 MySQL 會自動釋放這個全局鎖,整個庫回到可以正常更新的狀態。而將整個庫設置為 readonly 之后,如果客戶端發生異常,則數據庫就會一直保持 readonly 狀態,這樣會導致整個庫長時間處于不可寫狀態,風險較高。

三是,readonly 對super用戶權限無效

注 :業務的更新不只是增刪改數據(DML),還有可能是加字段等修改表結構的操作(DDL)。不論是哪種方法,一個庫被全局鎖上以后,你要對里面任何一個表做加字段操作,都是會被鎖住的。

即使沒有被全局鎖住,加字段也不是就能一帆風順的,還有表級鎖了

2. 表級鎖

MySQL 里面表級別的鎖有兩種:一種是表鎖,一種是元數據鎖(meta data lock,MDL)。

2.1 表鎖

lock tables 表名 read;#該表可以讀,不能ddl 和 dml 中增刪改,只能讀取表數據

lock tables 表名 read;# 既不能讀,也不能寫

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

舉個例子, 如果在某個線程 A 中執行 lock tables t1 read, t2 write; 這個語句,則其他線程寫 t1、讀寫 t2 的語句都會被阻塞。同時,線程 A 在執行 unlock tables 之前,也只能執行讀 t1、讀寫 t2 的操作。連寫 t1 都不允許,自然也不能訪問其他表。

在還沒有出現更細粒度的鎖的時候,表鎖是最常用的處理并發的方式。而對于 InnoDB 這種支持行鎖的引擎,一般不使用 lock tables 命令來控制并發,畢竟鎖住整個表的影響面還是太大

2.2 MDL 鎖

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

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

  • 讀鎖之間不互斥,因此你可以有多個線程同時對一張表增刪改查。
  • 讀寫鎖之間、寫鎖之間是互斥的,用來保證變更表結構操作的安全性。因此,如果有兩個線程要同時給一個表加字段,其中一個要等另一個執行完才能開始執行。

雖然 MDL 鎖是系統默認會加的,但卻是你不能忽略的一個機制。

比如下面這個例子,我經常看到有人掉到這個坑里:給一個小表加個字段,導致整個庫掛了。

肯定知道,給一個表加字段,或者修改字段,或者加索引,需要掃描全表的數據。在對大表操作的時候,你肯定會特別小心,以免對線上服務造成影響。而實際上,即使是小表,操作不慎也會出問題。我們來看一下下面的操作序列,假設表 t 是一個小表。

show full processlist查看mdl 鎖詳情

我們可以看到 session A 先啟動,這時候會對表 t 加一個 MDL 讀鎖。由于 session B 需要的也是 MDL 讀鎖,因此可以正常執行。

之后 session C 會被 blocked,是因為 session A 的 MDL 讀鎖還沒有釋放,而 sessionC 需要MDL 寫鎖,因此只能被阻塞。

如果只有 session C 自己被阻塞還沒什么關系,但是之后所有要在表 t 上新申請 MDL 讀鎖的請求也會被 session C 阻塞。前面說了,所有對表的增刪改查操作都需要先申請MDL 讀鎖,就都被鎖住,等于這個表現在完全不可讀寫了。

如果某個表上的查詢語句頻繁,而且客戶端有重試機制,也就是說超時后會再起一個新session 再請求的話,這個庫的線程很快就會爆滿。

事務中的 MDL 鎖,在語句執行開始時申請,但是語句結束后并不會馬上釋放,而會等到整個事務提交后再釋放。
注 : 一般行鎖都有鎖超時時間。但是MDL鎖沒有超時時間的限制,只要事務沒有提交就會一直鎖注。

2.2.1 怎么解決了這個MDL鎖

上面不是說了嗎,提交或者回滾這個事務。所以要找到這個事務

怎么找到這個事務, 通過information_schema.innodb_trx查看事務的執行時間

# 查看事務超過60s的事務
mysql> select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60\G;
trx_started 表示什么時候執行的這個事務
 
#查看系統當前時間
mysql> select now();

事務開始時間和系統現在時間,一看事務執行了這么久。

查看這個線程id

怎么處理了這個長事務的線程id了

首先看show full processlist; 中host 哪個字段 ,到底是誰連接了數據庫。例:我上面是localhost環境,進去commit或者/rollback ,哪如果不是localhost 環境了,是程序連接了這時候就要kill掉了

2.2.2 我身上發生的趣事

上次有個DBA 問我, 造成很大主從延遲,說要怎么解決。

我說你怎么解決的延遲, 知道主從延遲造成的具體的原因嗎?

ta給我說,開啟了多線程, 但是延遲還是很大,基本沒怎么用到多線程。

我說你怎么知道主從延遲,需要開啟多線程復制來解決, ta給我說,網上別人blog這莫說的,我一口老血吐出來。

后來又問ta, 主從延遲正常情況下,做了什么操作, ta給我說修改了alter 表結構。

然后讓ta 看是不是mdl 鎖造成的,讓tashow full processlist,一看果然是mdl 鎖原因。

然后跟ta 說 找長事務, 找到以后跟開發商議,這個長事務在做什么操作可不可以kill掉。

注:這是我真實遇到,別人問我這樣問題。首先你要知道造成這個結果,你事先做了什么操作, 然后解決這個問題,最根本你還是要知道原因,然后下次避免。

還有網上的環境,系統版本,應用版本,遇到問題的情況,跟你是不是一樣,有時候不要盲目相信。

2.3 如何安全地給小表加字段?

首先我們要解決長事務,事務不提交,就會一直占著 MDL 鎖。在 MySQL 的information_schema 庫的 innodb_trx 表中,你可以查到當前執行中的事務。如果你要做 DDL 變更的表剛好有長事務在執行,要考慮先暫停 DDL,或者 kill 掉這個長事務。這也是為什么需要在低峰期做ddl 變更,當然也要考慮具體做什么ddl,參考官方的online ddl。

2.4 online ddl 過程

  • 拿MDL寫鎖
  • 降級成MDL讀鎖
  • 真正做DDL
  • 升級成MDL寫鎖
  • 釋放MDL鎖

1、2、4、5如果沒有鎖沖突,執行時間非常短。第3步占用了DDL絕大部分時間,這期間這個個表可以正常讀寫數據,是因此稱為”online”

總結

以上就是這篇文章的全部內容了,希望本文的內容對大家的學習或者工作具有一定的參考學習價值,謝謝大家對腳本之家的支持。

相關文章

  • mysql中錯誤:1093-You can’t specify target table for update in FROM clause的解決方法

    mysql中錯誤:1093-You can’t specify target table for update

    最近在工作中遇到了一個mysql錯誤提示1093:You can’t specify target table for update in FROM clause,后來通過查找相關的資料解決了這個問題,現在將解決的方法分享給大家,有需要的朋友們可以參考借鑒,下面來一起看看吧。
    2017-01-01
  • MySQL不同表之前的字段復制

    MySQL不同表之前的字段復制

    今天小編就為大家分享一篇關于MySQL不同表之前的字段復制,小編覺得內容挺不錯的,現在分享給大家,具有很好的參考價值,需要的朋友一起跟隨小編來看看吧
    2019-03-03
  • MySQL安裝配置方法教程

    MySQL安裝配置方法教程

    這篇文章主要為大家詳細分享了MySQL安裝配置方法教程,包括Linux/UNIX和Window系統上安裝MySQL的具體步驟,感興趣的朋友可以參考一下
    2016-05-05
  • MySQL查詢語句大全集錦

    MySQL查詢語句大全集錦

    這篇文章主要介紹了MySQL查詢語句大全集錦,需要的朋友可以參考下
    2016-06-06
  • mysql 5.7.19 winx64免安裝版配置教程

    mysql 5.7.19 winx64免安裝版配置教程

    這篇文章主要為大家詳細介紹了mysql 5.7.19 winx64免安裝版配置教程,具有一定的參考價值,感興趣的小伙伴們可以參考一下
    2019-06-06
  • MySQL為例講解JDBC數據庫連接步驟

    MySQL為例講解JDBC數據庫連接步驟

    這篇文章主要為大家詳細介紹了MySQL為例講解JDBC數據庫連接步驟,感興趣的小伙伴們可以參考一下
    2016-08-08
  • MySQL存儲數據亂碼的問題解析

    MySQL存儲數據亂碼的問題解析

    這篇文章主要介紹了MySQL存儲數據亂碼的問題解析,作者從實際使用中的多個方面定位其原因然后解決,需要的朋友可以參考下
    2015-05-05
  • Mysql limit 優化,百萬至千萬級快速分頁 復合索引的引用并應用于輕量級框架

    Mysql limit 優化,百萬至千萬級快速分頁 復合索引的引用并應用于

    MySql 性能到底能有多高?用了php半年多,真正如此深入的去思考這個問題還是從前天開始。有過痛苦有過絕望,到現在充滿信心!
    2011-05-05
  • 簡單談談MySQL中的int(m)

    簡單談談MySQL中的int(m)

    設置int型的時候,需要設置int(M),以前知道這個M最大是255,但是到底應該設置多少并沒有在意。注意zerofill,今天我們來簡單探討下
    2016-09-09
  • MySQL中的SUM函數使用教程

    MySQL中的SUM函數使用教程

    這篇文章主要介紹了MySQL中的SUM函數使用教程,是MySQL入門學習中的基礎知識,需要的朋友可以參考下
    2015-05-05

最新評論

湖北11选5走势图爱彩乐