mysql進階-鎖

鎖

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

鎖的一般應用場合:
簡單事務控制:在些簡單的事務控制場景下。
如:在單個資料庫事務中需要確保某些數據的完整性和一致性時，可以直接在SQL中增加排他鎖來實現。這樣做既簡單又直接，避免引入額外的複雜性。

低併發情況:業務場景下併發訪問量不高或不需要跨服務或跨實例進行加鎖操作，直接在SQL中增加排他鎖可能會更加輕量和直觀，不需要引入額外的分散式系統組件。

資料庫專用功能:有些資料庫系統提供了特定的排他鎖機制，並且對於特定的業務場景，這些資料庫專用的排他鎖功能可能更加適用和高效。

特定的數據操作:在某些需要對整個表或某些數據範圍進行原子操作的情況下，直接在SQL中增加排他鎖可能更加方便和可行，特別是針對較小規模的數據操作。

MySQL中的鎖，按照鎖的粒度分，分為以下三類:
1.全局鎖:鎖定資料庫中的所有表。()
2.表級鎖:每次操作鎖住整張表。
3.行級鎖:每次操作鎖住對應的行數據。

全局鎖

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

備份資料庫操作:
flush tables with read lock;//全局鎖-只能讀不能寫
mysqldump -uroot -p1234 資料庫名 >itcast.sql//執行數據備份
mysqldump -h ip -uroot p1234 db01 >D:/db01.sql//遠程備份
unlock tables;//解鎖

弊端-資料庫中加全局鎖是一個比較重的操作，存在以下問題:
如果在主庫上備份，那麼在備份期間都不能執行更新，業務基本上就得停擺。
如果在從庫上備份，那麼在備份期間從庫不能執行主庫同步過來的二進位日誌(binlog)，會導致主從延遲。

上述問題將在運維篇深入講解

在InnoDB引擎中，我們可以在備份時加上參數 --single-transaction 參數來完成不加鎖的一致性數據備份。
mysqldump --single-transaction -uroot -p123456 itcast>itcast.sql

表級鎖

表級鎖在操作時會鎖住整張表。鎖定粒度大，發生鎖衝突的概率最高，併發度最低。在MISAM、InnoDB、BDB等存儲引擎中都支持。
對於表級鎖，主要分為:表鎖、元數據鎖(meta data lock，MDL)、意向鎖

• 表鎖
  表鎖的分類:
  1.表共用讀鎖(read lock)-所有用戶都只能讀不能寫
  2.表獨占寫鎖(write lock)-僅當前用戶能讀能寫，其他用戶不能讀不能寫
  語法:

lock tables 表名 read/write;//加鎖
unlock tables;或讓客戶端斷開連接//釋放鎖

• 元數據鎖
  元數據的定義:
  元數據是描述數據的數據，具體到MySQL中，它描述了資料庫的結構和意義，包括資料庫、表、列、索引等對象的信息。

元數據鎖的介紹:
MDL加鎖過程是系統自動控制，無需顯式使用，在訪問一張表的時候會自動加上。
MDL鎖主要作用是維護表元數據的數據一致性，在表上有活動事務的時候，不可以對元數據進行寫入操作。為了避免DML與DDL衝突，保證讀寫的正確性。
在MySQL5.5中引入了MDL，當對一張表進行增刪改查的時候，加MDL讀鎖(共用);當對錶結構進行變更操作的時候，加MDL寫鎖(排他)。

select object type,object schema,object name,lock type,lock duration from performance_schema.metadata_locks;//查看元數據鎖

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

意向鎖分類:

1、意向共用鎖(IS)添加:  lock in share mode
通過A視窗先開啟事務
begin;
加行鎖的同時對錶添加意向共用鎖。
select * from score where id=1 lock in share mode。//與表鎖共用鎖(read)相容，與表鎖排它鎖(write)互斥

開啟B Mysql視窗進行驗證
lock tables score  read;//驗證-對錶加共用讀鎖==》成功
lock tables score  write;//驗證-對錶加獨占寫鎖==》阻塞

總結
A視窗對score表加了意向共用鎖並給id為1的行加了共用行鎖。
此時可以對score表加共用讀鎖但不能加寫鎖。因為**意向共用鎖和讀鎖是相容的**

2、意向排他鎖(IX)添加: insert、update、delete、select ... for update
通過A視窗先開啟事務
begin;
為更新語句增加意向排他鎖
update score set math=66 where id = 1;

開啟B視窗進行驗證
lock tables score  read;//驗證-對錶加共用讀鎖==》阻塞
lock tables score  write;//驗證-對錶加獨占寫鎖==》阻塞


3、查看意向鎖及行鎖的加鎖情況:
select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;

行級鎖

介紹:
行級鎖，每次操作鎖住對應的行數據。鎖定粒度最小，發生鎖衝突的概率最低，併發度最高。應用在InnoDB存儲引擎中。
InnoDB的數據是基於索引組織的，行鎖是通過對索引上的索引項加鎖來實現的，而不是對記錄加的鎖。
對於行級鎖，主要分為以下三類:

1.行鎖(Record Lock):鎖定單個行記錄的鎖，防止其他事務對此行進行update和delete。在RC、RR隔離級別下都支持。
2.間隙鎖(GapLock):鎖定索引記錄間隙(不含該記錄)，確保索引記錄間除不變，防止其他事務在這個間隙進行insent，產生幻讀。在RR隔離級別下都支持。
3.臨鍵鎖(Next-KeyLock):行鎖和間隙鎖組合，同時鎖住數據，並鎖住數據前面的間隙Gap。在RR隔離級別下支持。

• 行鎖
  InnoDB實現了以下兩種類型的行鎖:
  共用鎖(S):允許一個事務去讀一行，阻止其他事務獲得相同數據集的它鎖。
  排他鎖(X):允許獲取排他鎖的事務更新數據，阻止其他事務獲得相同數據集的共用鎖和排他鎖。

常見增刪改查對應鎖情況:

預設情況下，InnoDB在REPEATABLE READ(預設)事務隔離級別運行，InnoDB使用next-key鎖進行搜索和索引掃描以防止幻讀。
1.針對唯一索引進行檢索時，對已存在的記錄進行等值匹配時，將會自動優化為行鎖。
2.InnoDB的行鎖是針對於索引加的鎖，如果不通過索引條件檢索數據那麼InnoDB將對錶中的所有記錄加鎖，此時就會升級為表鎖。(常見優化問題-重要)

• 間隙鎖/臨鍵鎖
  預設情況下，InnoDB在REPEATABLE READ事務隔離級別運行，InnoDB使用next-key鎖(臨建鎖)進行搜索和索引掃描以防止幻讀。
  1.索引上的等值查詢(唯一索引)，給不存在的記錄加鎖時，優化為間隙鎖。
  2.索引上的等值查詢(普通索引)，向右遍歷時最後一個值不滿足查詢需求時，next-key lock 退化為間隙鎖。
  3.索引上的範圍查詢(唯一索引)--會訪問到不滿足條件的第一個值為止。