MySQL高級9-鎖

来源:https://www.cnblogs.com/Se7eN-HOU/archive/2023/09/04/17641868.html
-Advertisement-
Play Games

一、簡介 鎖是電腦協調多個進程或線程併發訪問某一資源的機制。在資料庫中,除了傳統的計算資源(CPU、RAM、i/O)的掙用以外,數據也是一種供許多用戶共用的資源。如何保證數據併發訪問的一致性,有效性是所有資料庫必須解決的一個問題,鎖衝突也是影響資料庫併發訪問性能的一個重要因素,從這個角度來說,鎖對 ...


一、簡介

  鎖是電腦協調多個進程或線程併發訪問某一資源的機制。在資料庫中,除了傳統的計算資源(CPU、RAM、i/O)的掙用以外,數據也是一種供許多用戶共用的資源。如何保證數據併發訪問的一致性,有效性是所有資料庫必須解決的一個問題,鎖衝突也是影響資料庫併發訪問性能的一個重要因素,從這個角度來說,鎖對資料庫而言顯得尤其重要,也更加複雜。

 

二、分類

  MySQL中的鎖、按照鎖的粒度分,分為以下三類

  • 全局鎖:鎖定數據量的所有表
  • 表級鎖:每次操作鎖住整張表
  • 行級鎖:每次操作鎖住對應的行數據

 

三、全局鎖

  3.1 簡介

    全局鎖就是對整個資料庫實例枷鎖,加鎖後整個實例就處於只讀狀態,後續的DML的寫語句,DDL語句,以及更新操作的事物提交語句都會被阻塞,其典型的使用場景是做全庫的邏輯備份,對所有的表進行鎖定,從而獲取一致性視圖,保證數據的完整性。

  3.2 添加全局鎖語法 

flush tables with read lock;

  3.3 打開全局鎖語法

unlock tables;

  3.4 案例一

  

    說明1:客戶端1中設置了全局鎖

    說明2:客戶端2中,執行查詢語句正常的,但是執行DML語句中的更新操作卻是處於阻塞狀態

  3.5 案例二

  

    說明1:客戶端1中設置了全局鎖

    說明2:客戶端3中做了資料庫備份的語句,其中mysqldump是和mysql一樣由MySQL伺服器提供的資料庫備份的命令

    說明3:當資料庫設置了全局鎖的時候,不影響資料庫的備份

    說明4:MySQL備份實在終端命令行模式下,不是在資料庫命令模式下,註意!註意!註意!

  3.6 案例三

  

    說明1:客戶端1釋放全局鎖 

    說明2:客戶端2的更新語句馬上執行成功

    說明3:通過客戶端2的更新語句的執行時間37min,可以說明該語句被客戶端1的全局鎖,阻塞了37分鐘

  3.7 全局鎖特點

    資料庫中加全局鎖,是一個比較重的操作,存在以下問題

    • 如果在主庫上備份,那麼在備份期間都不能執行更新,業務上基本就是停擺狀態
    • 如果在從庫上備份,那麼在備份期間從庫不能執行主庫同步過來的二進位日誌(binlog),會導致主從延遲

    在innoDB引擎中,我們可以在備份時加上 --single-transaction 參數來完成不加鎖的一致性數據備份

 

四、表級鎖

  4.1 簡介

    表級鎖,每次操作鎖住整張表,鎖定粒度大,發生鎖衝突的概率最高,併發度最低,應用在MyISAM,InnoDB等存儲引擎中

  4.2 表級鎖的分類

    • 表鎖
    • 元數據鎖(meta data lock, MDL)
    • 意向鎖

五、表鎖

  5.1 表鎖的分類

    • 表共用讀鎖(read lock)
    • 表獨占寫鎖(write lock)

  5.2 表鎖語法

    加鎖:lock tables 表名... read/write

    釋放鎖:unlock tables / 客戶端斷開連接

    說明1:加鎖的時候,可以多張表同時加鎖

    說明2:客戶端斷開連接也能釋放表鎖

  5.3 讀鎖案例

      

   

      說明1:當對一個表添加讀鎖,不會影響其讀取數據,但是會影響其新增,修改,刪除的操作語句

    說明2:當對一個表添加讀鎖,不會影響其他客戶端讀取數據,但是會讓其他客戶端的新增,修改,刪除等操作語句處於阻塞狀態。

    說明3:當把鎖釋放了,自己及其他客戶端的新增,刪除和修改語句才會結束阻塞。

  5.4 寫鎖案例

    

    

    說明1:添加寫鎖的客戶端可以正常對錶進行查詢和增刪改等操作

    說明2:其他客戶端的查詢,修改,新增,刪除都操作在有寫鎖的情況下,都要處於阻塞狀態,直到其添加寫鎖的客戶端釋放寫鎖。

    說明3:上圖中的修改等應該是DML語句(Data Manipulation Language 數據操作語言,用來對資料庫表中的數據進行增刪改的),不是DDL語句(DDL: Data Definition Language 數據定義語言,用來定義資料庫對象,資料庫,表,欄位)

 

六、元數據鎖

  6.1 元數據鎖簡介:

    元數據鎖(meta data lock):簡稱 MDL

    MDL加鎖過程是系統自動控制,無需顯示使用,在訪問一張表的時候會自動加上。MDL鎖主要作用是維護表元數據的數據一致性,在表上有活動事務的時候,不可以對元數據進行寫入操作,為了避免DML和DDL衝突,保證讀寫的正確性

  6.2 元數據鎖的類型

    在MySQL5.5 中引入了MDL, 當對一張表進行增刪改查的時候,加MDL讀鎖(共用);當對錶結構進行變更操作的時候,加MDL寫鎖(排他)

    

  6.3 案例

    

    說明1:select 和 update 分別會添加SHARED_READ 和 SHARED_WRITE, 但是SHARED_READ 和 SHARED_WRITE是相容的,所以其他客戶端是可以做更刪改查的

    

     說明2:alter table 會產生EXCLUSIVE 鎖。該鎖與其他MDL都互斥。ß

  6.4 查看元數據鎖

select object_type,object_schema,object_name,lock_type,locak_duration from  performance_schema.metadata_locks;

    

 

七、意向鎖

  7.1 意向鎖介紹

    為了避免DML在執行時,加的行鎖與表鎖的衝突,在InnoDB中引入了意向鎖,使得表鎖不用檢查每一行數據是否加鎖,使用意向鎖來減少表鎖的檢查。

    情況1:不加意向鎖

   

    說明1:線程A 開啟一個事務,並且使用DML語句更新數據,此時會對更新的數據添加行鎖,

    說明2:此時線程A 的事務並沒有提交事務,線程B又對該表添加一個表鎖,此時添加表鎖的時候,就會從第一條數據,依次檢查到最後一條數據,看是否有其他的鎖,如果有其他鎖,需要等到其他鎖釋放了,才能執行添加表鎖

    說明3:此時添加表鎖的效率就非常低  

    情況2:加意向鎖

   

    說明4:當線程A 開啟了一個事務,並且執行了DML的更新數據的語句,此時除了會給該行數據添加行鎖之外,還會添加一個基於整表的意向鎖。

    說明5:當線程B 再次添加表鎖的時候,就不用逐行排查是否有行鎖了,而是直接檢查整表是否有意向鎖,如果意向鎖和表鎖相容則直接加表鎖

    說明6:如果表鎖和意向鎖不相容,則仍然會等到意向鎖釋放了,表鎖才能添加成功。

  7.2 意向鎖的類型

    • 意向共用鎖(IS): 由語句 select ... lock in share mode 添加。與表鎖共用鎖(read)相容,與表鎖排它鎖(write)互斥。
    • 意向排它鎖(IX): 由 insert, update, delete, select ... for update 添加。  與表鎖共用鎖(read)及排它鎖(write)都互斥,意向鎖之間不會互斥

  7.3 意向鎖查詢

select object_schema, object_name, index_name, lock_type, lock_mode, lock_data from performance_schema.data_locks;

  7.4 意向鎖案例

    

    說明1:客戶端1開啟事務,並且查詢語句時設置一個意向共用鎖(IS)

    說明2:客戶端2在客戶端1的事務未提交的時候,創建read鎖成功

    說明3:意向共用鎖(IS)和read共用鎖相容

    

    說明4:執行update等DML語句時,MySQL會自動添加行鎖和排他意向鎖。

    

    說明5:排他意向鎖與共用read鎖和排他write鎖都互斥

    說明6:意向鎖的作用主要是解決行鎖和表鎖之間的矛盾。

 

八、行級鎖

  8.1、簡介

    行級鎖,每次操作鎖住對應的行數據,所得粒度最小,發生鎖衝突的概率最低,併發度最高,應用在InnoDB存儲引擎中

    InnoDB的數據是基於索引組織的行鎖是通過對索引上的索引項加鎖來實現的,而不是對記錄加的鎖,對於行級鎖,主要分為一下三類

    1、行鎖(record lock): 鎖定單個行記錄的鎖,防止其他事務對此行進行update和delete,在read committed和repeatable read隔離級別下都支持。

    

     2、間隙鎖(Gap lock): 鎖定索引記錄間隙(不包含記錄),確保索引記錄間隙不變,防止其他事務在這個間隙進行insert,產生幻讀,在repeatable read隔離級別下支持

     

     3、臨建鎖(Next-Key lock): 行鎖和間隙鎖組合,同時鎖住數據,並鎖住數據前面的間隙,在repeatable read隔離級別下支持

     

   8.2 行鎖

    InnoDB 實現了一下兩種類型的行鎖

    1、共用鎖(S): 允許一個事物去讀一行,阻止其他事務獲得相同數據集的排他鎖。

    2、排他鎖(X): 允許獲取排他鎖的事務更新數據,阻止其他事物獲得相同數據集的共用鎖和排他鎖

    

     3、不同語句的枷鎖情況

    

     4、預設情況下,InnoDB在 repeatable read隔離級別下運行,InnoDB使用next-key鎖進行行搜索和索引掃描,以防止幻讀

    • 針對唯一索引進行檢索時,對已經存在的記錄進行等值匹配時,將會自動優化為行鎖
    • InnoDB的行鎖是針對於索引加的鎖,不通過索引檢索數據,那麼InnoDB將對錶中的所有記錄加鎖,此時就會升級為表鎖

   8.3 行鎖案例

    

    說明1:客戶端1開始事務,並執行查詢語句,客戶端2鎖的情況,顯示為空表

    說明2:客戶端執行查詢語句,並手動添加共用,在客戶端2中,查詢lock_mode欄位中有一個S,即共用鎖: 

    

    說明3:客戶端2也手動開啟了一個共用鎖,並且成功,說明共用鎖與共用鎖之間是相容的。

    

    說明5:在客戶端2上執行更新id=2的數據成功,並且查詢可以看出,自動增加了一個(X)排它鎖。

    說明6:  因為更新id=2的數據時,id=2的這一行上沒有其他的鎖,所以可以執行成功

    說明7:因為在客戶點1上已經對id=1的行上添加了一個共用鎖(S), 這是客戶端2對id=1的數據做update操作時會自動在id=1的數據上在添加一個排它鎖(X),這是id=1的數據上就會有共用鎖(S)和排它鎖(X),又因為共用鎖和排它鎖不相容,所以在update id = 1 的時候就會阻塞,需要等到共用鎖(S)釋放了,才能執行成功。

    

    說明8:排它鎖X 與 排它鎖X 也是不相容的。

    

     說明9:InnoDB的行鎖是針對於索引加的鎖,不通過索引檢索數據,那麼InnoDB將對錶中的所有記錄加鎖,此時就會升級為表鎖

    

     說明10:根據業務需要,儘量使用索引當所查詢條件,既快又減少阻塞。

 

九、間隙鎖/臨建鎖

  9.1 預設情況下、InnoDB在 repeatable read 事務隔離級別運行,InnoDB使用next-key 鎖進行搜索和索引掃描,以防止幻讀。

    

  9.2 索引上的等值查詢(唯一索引),給不存在的記錄加鎖時,優化為間隙鎖

    

  9.3 索引上的等值查詢(普通索引),向右遍歷時最後一個值不滿足查詢需求時,next-key lock 退化為間隙鎖

    

    說明1:非唯一索引,即普通索引,在做等值查詢的時候,會添加三把鎖

    說明2:在查詢的當條記錄上添加一個共用鎖,允許其他事務繼續查詢該記錄

    說明3:通過標註6的位置,lock_type 為S,REC_NOT_GAP 說明同時給這一行添加一個行鎖

    說明4:通過標註7的位置,lock_type 為 S,GAP 說明在第一個不滿足查詢數據的前面也會加一個間隙鎖。其目的是為了防止在select 查詢的時候,其他事務去往這個索引之前插入或者修改數據,這樣查詢就會出現幻讀的現象。

  9.4 索引上的範圍查詢(唯一索引),會訪問到不滿足條件的第一個值為止。

    

  註意:間隙鎖唯一的目的是防止其他事務插入間隙,間隙鎖可以共存,一個事務採用的間隙鎖不會阻止另一個事物在同一間隙上採用間隙鎖 


侯哥語錄:我曾經是一個職業教育者,現在是一個自由開發者。我希望我的分享可以和更多人一起進步。分享一段我喜歡的話給大家:"我所理解的自由不是想乾什麼就乾什麼,而是想不幹什麼就不幹什麼。當你還沒有能力說不得時候,就努力讓自己變得強大,擁有說不得權利。"
您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • ## 1、線程的基本管控 包含頭文件``後,通過構建`std::thread`對象啟動線程,任何可調用類型都適用於`std::thread`。 ```c++ void do_some_work(); struct BackgroundTask { void operator()() const; } ...
  • > 本文深入探討了Go語言的多個關鍵方面,從其簡潔的語法、強大的併發支持到出色的性能優勢,進一步解析了Go在雲原生領域的顯著應用和廣泛的跨平臺支持。文章結構嚴謹,逐一分析了Go語言在現代軟體開發中所占據的重要地位和其背後的技術原理。 > 關註TechLeadCloud,分享互聯網架構、雲服務技術的全 ...
  • java中要實現excel新老格式的轉換比較麻煩,開源庫也沒幾個好用的。用ChatGpt查詢也是推薦直接用POI,下麵是藉助ChatGPT寫出來的代碼,經過小小修改,格式轉換良好,基本能用,就是效率比較低下。將就著用吧,哎! /** * Excel格式從xls轉換成xlsx格式 * * @param ...
  • Hi,大家好,我是一路狂奔的烏龜 很高興遇見你~ 我們可能會在睡前下載文件,而不想自己的電腦一整晚都在運行,浪費電也損耗電腦硬體 基於這個日常需求(你怎麼知道我喜歡在半夜下載文件?) 所以我就開發了這款軟體 至今已經修改了三四個版本了,基本滿足要求了就不再更新了 好了, 接下來,,就容我介紹一下這款 ...
  • # iptables、共用上網SNAT、埠轉發DNAT ## 1.防火牆概述 封端⼝,封ip 實現NAT功能 共用上⽹ 端⼝映射(端⼝轉發),ip映射 ## 2.防火牆 ### 2.1防火牆種類以及使用說明 硬體:整個企業入口 軟體:開源軟體 網站內部 封ip iptables 雲防火牆 安全組 ...
  • 提要:本系列文章主要參考`MIT 6.828課程`以及兩本書籍`《深入理解Linux內核》` `《深入Linux內核架構》`對Linux內核內容進行總結。 記憶體管理的實現覆蓋了多個領域: 1. 記憶體中的物理記憶體頁的管理 2. 分配大塊記憶體的伙伴系統 3. 分配較小記憶體的slab、slub、slob分 ...
  • # Keepalived高可用集群 ## 高可用集群簡介 **什麼是高可用集群?** 高可用集群 (High Availability;Cluster,簡稱HA Cluster) ,是指以減少服務中斷時間為目的的伺服器集群技術。它通過保護用戶的業務程式對外不間斷提供的服務,把因軟體、硬體、人為造成的 ...
  • ![](https://img2023.cnblogs.com/blog/3076680/202309/3076680-20230902230646018-938272092.png) # 1. 元數據 ## 1.1. metadata ## 1.2. 關於數據的數據 ## 1.3. 數據字典 ## ...
一周排行
    -Advertisement-
    Play Games
  • 一個自定義WPF窗體的解決方案,借鑒了呂毅老師的WPF製作高性能的透明背景的異形視窗一文,併在此基礎上增加了滑鼠穿透的功能。可以使得透明窗體的滑鼠事件穿透到下層,在下層窗體中響應。 ...
  • 在C#中使用RabbitMQ做個簡單的發送郵件小項目 前言 好久沒有做項目了,這次做一個發送郵件的小項目。發郵件是一個比較耗時的操作,之前在我的個人博客裡面回覆評論和友鏈申請是會通過發送郵件來通知對方的,不過當時只是簡單的進行了非同步操作。 那麼這次來使用RabbitMQ去統一發送郵件,我的想法是通過 ...
  • 當你使用Edge等瀏覽器或系統軟體播放媒體時,Windows控制中心就會出現相應的媒體信息以及控制播放的功能,如圖。 SMTC (SystemMediaTransportControls) 是一個Windows App SDK (舊為UWP) 中提供的一個API,用於與系統媒體交互。接入SMTC的好 ...
  • 最近在微軟商店,官方上架了新款Win11風格的WPF版UI框架【WPF Gallery Preview 1.0.0.0】,這款應用引入了前沿的Fluent Design UI設計,為用戶帶來全新的視覺體驗。 ...
  • 1.簡單使用實例 1.1 添加log4net.dll的引用。 在NuGet程式包中搜索log4net並添加,此次我所用版本為2.0.17。如下圖: 1.2 添加配置文件 右鍵項目,添加新建項,搜索選擇應用程式配置文件,命名為log4net.config,步驟如下圖: 1.2.1 log4net.co ...
  • 之前也分享過 Swashbuckle.AspNetCore 的使用,不過版本比較老了,本次演示用的示例版本為 .net core 8.0,從安裝使用開始,到根據命名空間分組顯示,十分的有用 ...
  • 在 Visual Studio 中,至少可以創建三種不同類型的類庫: 類庫(.NET Framework) 類庫(.NET 標準) 類庫 (.NET Core) 雖然第一種是我們多年來一直在使用的,但一直感到困惑的一個主要問題是何時使用 .NET Standard 和 .NET Core 類庫類型。 ...
  • WPF的按鈕提供了Template模板,可以通過修改Template模板中的內容對按鈕的樣式進行自定義。結合資源字典,可以將自定義資源在xaml視窗、自定義控制項或者整個App當中調用 ...
  • 實現了一個支持長短按得按鈕組件,單擊可以觸發Click事件,長按可以觸發LongPressed事件,長按鬆開時觸發LongClick事件。還可以和自定義外觀相結合,實現自定義的按鈕外形。 ...
  • 一、WTM是什麼 WalkingTec.Mvvm框架(簡稱WTM)最早開發與2013年,基於Asp.net MVC3 和 最早的Entity Framework, 當初主要是為瞭解決公司內部開發效率低,代碼風格不統一的問題。2017年9月,將代碼移植到了.Net Core上,併進行了深度優化和重構, ...