mysql行級鎖，表級鎖怎么添加

當 web 日志中出現行鎖超時錯誤后，很多開發都會找我來排查問題，這里說下問題定位的難點！1. MySQL 本身不會主動記錄行鎖等待的相關信息，所以無法有效的進行事后分析。2. 鎖爭用原因有多種，很難在事后判斷到底是哪一類問題場景，尤其是事后無法復現問題的時候。3. 找到問題 SQL 后，開發無法有效從代碼中挖掘出完整的事務，這也和公司框架-產品-項目的架構有關，需要靠 DBA 事后采集完整的事務 SQL 才可以進行分析。

創新互聯公司專注于蘇州網站建設服務及定制，我們擁有豐富的企業做網站經驗。 熱誠為您提供蘇州營銷型網站建設，蘇州網站制作、蘇州網頁設計、蘇州網站官網定制、微信小程序開發服務，打造蘇州網絡公司原創品牌,更為您提供蘇州網站排名全網營銷落地服務。

mysql 的鎖以及間隙鎖

mysql 為并發事務同時對一條記錄進行讀寫時，提出了兩種解決方案：

1）使用 mvcc 的方法，實現多事務的并發讀寫，但是這種讀只是“快照讀”，一般讀的是歷史版本數據，還有一種是“當前讀”，一般加鎖實現“當前讀”，或者 insert、update、delete 也是當前讀。

2）使用加鎖的方法，鎖分為共享鎖（讀鎖），排他鎖（寫鎖）

快照讀：就是select

當前讀：特殊的讀操作，插入/更新/刪除操作，屬于當前讀，處理的都是當前的數據，需要加鎖。

mysql 在 RR 級別怎么處理幻讀的呢？一般來說，RR 級別通過 mvcc 機制，保證讀到低于后面事務的數據。但是 select for update 不會觸發 mvcc，它是當前讀。如果后面事務插入數據并提交，那么在 RR 級別就會讀到插入的數據。所以，mysql 使用 行鎖 + gap 鎖（簡稱 next-key 鎖）來防止當前讀的時候插入。

Gap Lock在InnoDB的唯一作用就是防止其他事務的插入操作，以此防止幻讀的發生。

Innodb自動使用間隙鎖的條件：

MySQL從入門到精通(九) MySQL鎖，各種鎖

鎖是計算機協調多個進程或線程并發訪問某一資源的機制，在數據庫中，除傳統的計算資源（CPU、RAM、I/O）爭用外，數據也是一種供許多用戶共享的資源，如何保證數據并發訪問的一致性，有效性是所有數據庫必須解決的一個問題，鎖沖突也是影響數據庫并發訪問性能的一個重要因素，從這個角度來說，鎖對數據庫而言是尤其重要，也更加復雜。MySQL中的鎖，按照鎖的粒度分為:1、全局鎖，就鎖定數據庫中的所有表。2、表級鎖，每次操作鎖住整張表。3、行級鎖，每次操作鎖住對應的行數據。

全局鎖就是對整個數據庫實例加鎖，加鎖后整個實例就處于只讀狀態，后續的DML的寫語句，DDL語句，已經更新操作的事務提交語句都將阻塞。其典型的使用場景就是做全庫的邏輯備份，對所有的表進行鎖定，從而獲取一致性視圖，保證數據的完整性。但是對數據庫加全局鎖是有弊端的，如在主庫上備份，那么在備份期間都不能執行更新，業務會受影響，第二如果是在從庫上備份，那么在備份期間從庫不能執行主庫同步過來的二進制日志，會導致主從延遲。

解決辦法是在innodb引擎中，備份時加上--single-transaction參數來完成不加鎖的一致性數據備份。

添加全局鎖: flush tables with read lock; 解鎖 unlock tables。

表級鎖,每次操作會鎖住整張表.鎖定粒度大,發送鎖沖突的概率最高,并發讀最低,應用在myisam、innodb、BOB等存儲引擎中。表級鎖分為: 表鎖、元數據鎖(meta data lock, MDL)和意向鎖。

表鎖又分為: 表共享讀鎖 read lock、表獨占寫鎖write lock

語法: 1、加鎖 lock tables 表名 ... read/write

2、釋放鎖 unlock tables 或者關閉客戶端連接

注意: 讀鎖不會阻塞其它客戶端的讀，但是會阻塞其它客戶端的寫，寫鎖既會阻塞其它客戶端的讀，又會阻塞其它客戶端的寫。大家可以拿一張表來測試看看。

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

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

查看元數據鎖:

select object_type,object_schema,object_name,lock_type,lock_duration from performance_schema_metadata_locks;

意向鎖，為了避免DML在執行時，加的行鎖與表鎖的沖突，在innodb中引入了意向鎖，使得表鎖不用檢查每行數據是否加鎖，使用意向鎖來減少表鎖的檢查。意向鎖分為,意向共享鎖is由語句select ... lock in share mode添加。意向排他鎖ix,由insert，update，delete，select。。。for update 添加。

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

行級鎖，每次操作鎖住對應的行數據，鎖定粒度最小，發生鎖沖突的概率最高，并發讀最高，應用在innodb存儲引擎中。

innodb的數據是基于索引組織的，行鎖是通過對索引上的索引項加鎖來實現的，而不是對記錄加的鎖，對于行級鎖，主要分為以下三類:

1、行鎖或者叫record lock記錄鎖，鎖定單個行記錄的鎖，防止其他事物對次行進行update和delete操作，在RC,RR隔離級別下都支持。

2、間隙鎖Gap lock,鎖定索引記錄間隙(不含該記錄)，確保索引記錄間隙不變，防止其他事物在這個間隙進行insert操作，產生幻讀，在RR隔離級別下都支持。

3、臨鍵鎖Next-key-lock,行鎖和間隙鎖組合，同時鎖住數據，并鎖住數據前面的間隙Gap,在RR隔離級別下支持。

innodb實現了以下兩種類型的行鎖

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

2、排他鎖 X: 允許獲取排他鎖的事務更新數據，阻止其他事務獲得相同數據集的共享鎖和排他鎖。

insert 語句 排他鎖 自動添加的

update語句 排他鎖 自動添加

delete 語句 排他鎖 自動添加

select 正常查詢語句 不加鎖 。。。

select 。。。lock in share mode 共享鎖 需要手動在select 之后加lock in share mode

select 。。。for update 排他鎖 需要手動在select之后添加for update

默認情況下，innodb在repeatable read事務隔離級別運行，innodb使用next-key鎖進行搜索和索引掃描，以防止幻讀。

間隙鎖唯一目的是防止其它事務插入間隙，間隙鎖可以共存，一個事務采用的間隙鎖不會阻止另一個事務在同一間隙上采用的間隙鎖。

mysql默認隔離級別怎么加鎖

修改方法

有兩種方法可以對配置了 systemd 的程序進行資源隔離：1. 命令行修改：通過執行?systemctl set-property?命令實現，形式為?systemctl set-property?name parameter=value；修改默認即時生效。2. 手工修改文件：直接編輯程序的 systemd unit file 文件，完成之后需手工執行?systemctl?daemon-reload?更新配置，并重啟服務?systemctl restart name.service。

systemd unit file 里支持的資源隔離配置項，如常見的：

CPUQuota=value

該參數表示服務可以獲取的最大 CPU 時間，value 為百分數形式，高于 100% 表示可使用?1 核以上的?CPU。與 cgroup cpu 控制器?cpu.cfs_quota_us?配置項對應。

MemoryLimit=value

該參數表示服務可以使用的最大內存量，value 可以使用 K, M, G, T 等后綴表示值的大小。與 cgroup?memory 控制器?memory.limit_in_bytes?配置項對應。

事務的4種隔離級別

READ UNCOMMITTED ? ? ? 未提交讀，可以讀取未提交的數據。

READ COMMITTED ? ? ? ? 已提交讀，對于鎖定讀(select with for update 或者 for share)、update 和 delete 語句，InnoDB 僅鎖定索引記錄，而不鎖定它們之間的間隙，因此允許在鎖定的記錄旁邊自由插入新記錄。 ? ? ? ? ? ? ? ? ? ?

Gap locking 僅用于外鍵約束檢查和重復鍵檢查。

REPEATABLE READ ? ? ? ?可重復讀，事務中的一致性讀取讀取的是事務第一次讀取所建立的快照。

SERIALIZABLE ? ? ? ? ? 序列化在了解了 4 種隔離級別的需求后，在采用鎖控制隔離級別的基礎上，我們需要了解加鎖的對象(數據本身間隙)，以及了解整個數據范圍的全集組成。

數據范圍全集組成

SQL 語句根據條件判斷不需要掃描的數據范圍（不加鎖）；

SQL 語句根據條件掃描到的可能需要加鎖的數據范圍；

以單個數據范圍為例，數據范圍全集包含：（數據范圍不一定是連續的值，也可能是間隔的值組成）

mysql鎖的簡單想法(不確定對不對)

事務可以看見其他未提交事務的修改。會導致臟讀。

事務只能看見其他提交事務的修改。但是如果事務A讀取一批數據set，其他事務之后修改了這個數據set并提交(此時事務A沒有提交)，這時事務A再讀取數據set就跟第一次讀取的結果不一致。會導致不可重復讀。

在事務執行過程中，重復讀到的數據是一致的

事務A讀到了事務B未提交的數據。(read uncommitted)

事務A第一次讀取行num，此時事務B修改行num并提交，事務A再讀行num，數據會發生變化。（read uncommitted, read committed）

事務A第一次查詢范圍query_range，返回n行，此時事務B在該查詢范圍內插入了一行數據并提交，事務A再次查詢范圍query_range會看到B插入的數據。（read uncommitted, read committed, repeatable read）

在同一個事務執行過程中，兩次相同的查詢，但是查詢的返回數據行數不同。

開啟sessionA, sessionB

此時鎖(行鎖、gap鎖)的情況如下圖,標紅為加鎖，采用next key lock。(不光在索引a上加鎖，索引a中3所對應的主鍵索引也會加鎖，只畫了索引a)

注意：這里是主鍵索引的順序與a索引的順序一致的情況。一致的情況下，新插入的4會插入在原來的4之后。我們定義函數index_key(x), 表示獲取x所對應的主鍵索引，new(x)表示新插入的x，old(x)表示已經存在的x。如果index_key(old(4))index_key(new(4))，那新的4是插不進去的，因為新的4會被放在老的4的前面。同理，新插入的2也有可能插入進去(只要index_key(new(2))index_key(old(2)))。具體的例子整理后發出

對于sessionB，插入2，3失敗，插入4成功。(如圖lock.png，新插入的2會被2與3之間gap鎖阻止，新插入的3肯定失敗，但是新插入的4就沒問題)

開啟sessionA：

這個時候情況如下圖所示:

如果按照實例1的分析，不看主鍵索引id，應該插入(id=4,a=3)應該會被gap鎖鎖定，但是事實是可以插入成功。因為插入的(i4=d,a=3)放在了(id=5,a=3)的上面。

同樣的道理，不看主鍵索引id，通過實例1的分析，插入(id=6,a=7)應該可以插入，但是事實不行，因為(id=6,a=7)被a=5與a=7之間的gap鎖阻止了。

新插入的(id=4, a=3)，需要判斷會插入在(id=5, a=3)之前還是之后，很明顯之前(45)，那么(id=3, a=5)與(id=5, a=3)之間的gap鎖不會阻止。

新插入的(id=6, a=7)，需要判斷會插入在(id=7, a=7)之前還是之后，很明顯之前(67)，那么(id=6, a=7)會被(id=3, a=5)與(id=5, a=3)與(id=7, a=7)之間的gap鎖阻止。

mysql讀數據時怎么加寫鎖

加鎖情況與死鎖原因分析

為方便大家復現，完整表結構和數據如下：

CREATE TABLE `t3` (

`c1` int(11) NOT NULL AUTO_INCREMENT,

`c2` int(11) DEFAULT NULL,

PRIMARY KEY (`c1`),

UNIQUE KEY `c2` (`c2`)

) ENGINE=InnoDB

insert into t3 values(1,1),(15,15),(20,20);

在 session1 執行 commit 的瞬間，我們會看到 session2、session3 的其中一個報死鎖。這個死鎖是這樣產生的：

1.?session1 執行 delete ?會在唯一索引 c2 的 c2 = 15 這一記錄上加 X lock（也就是在MySQL 內部觀測到的：X Lock but not gap）；

2.?session2 和 session3 在執行 insert 的時候，由于唯一約束檢測發生唯一沖突，會加 S Next-Key Lock，即對 (1,15] 這個區間加鎖包括間隙，并且被 seesion1 的 X Lock 阻塞，進入等待；

3.?session1 在執行 commit 后，會釋放 X Lock，session2 和 session3 都獲得 S Next-Key Lock；

4.?session2 和 session3 繼續執行插入操作，這個時候 INSERT INTENTION LOCK（插入意向鎖）出現了，并且由于插入意向鎖會被 gap 鎖阻塞，所以 session2 和 session3 互相等待，造成死鎖。

死鎖日志如下：

請點擊輸入圖片描述

INSERT INTENTION LOCK

在之前的死鎖分析第四點，如果不分析插入意向鎖，也是會造成死鎖的，因為插入最終還是要對記錄加 X Lock 的，session2 和 session3 還是會互相阻塞互相等待。

但是插入意向鎖是客觀存在的，我們可以在官方手冊中查到，不可忽略：

Prior to inserting the row, a type of gap lock called an insert intention gap lock is set. This lock signals the intent to insert in such a way that multiple transactions inserting into the same index gap need not wait for each other if they are not inserting at the same position within the gap.

插入意向鎖其實是一種特殊的 gap lock，但是它不會阻塞其他鎖。假設存在值為 4 和 7 的索引記錄，嘗試插入值 5 和 6 的兩個事務在獲取插入行上的排它鎖之前使用插入意向鎖鎖定間隙，即在（4，7）上加 gap lock，但是這兩個事務不會互相沖突等待。

當插入一條記錄時，會去檢查當前插入位置的下一條記錄上是否存在鎖對象，如果下一條記錄上存在鎖對象，就需要判斷該鎖對象是否鎖住了 gap。如果 gap 被鎖住了，則插入意向鎖與之沖突，進入等待狀態（插入意向鎖之間并不互斥）。總結一下這把鎖的屬性：

1. 它不會阻塞其他任何鎖；

2. 它本身僅會被 gap lock 阻塞。

在學習 MySQL 過程中，一般只有在它被阻塞的時候才能觀察到，所以這也是它常常被忽略的原因吧...

GAP LOCK

在此例中，另外一個重要的點就是 gap lock，通常情況下我們說到 gap lock 都只會聯想到 REPEATABLE-READ 隔離級別利用其解決幻讀。但實際上在 READ-COMMITTED 隔離級別，也會存在 gap lock ，只發生在：唯一約束檢查到有唯一沖突的時候，會加 S Next-key Lock，即對記錄以及與和上一條記錄之間的間隙加共享鎖。

通過下面這個例子就能驗證：

請點擊輸入圖片描述

這里 session1 插入數據遇到唯一沖突，雖然報錯，但是對 (15,20] 加的 S Next-Key Lock 并不會馬上釋放，所以 session2 被阻塞。另外一種情況就是本文開始的例子，當 session2 插入遇到唯一沖突但是因為被 X Lock 阻塞，并不會立刻報錯 “Duplicate key”，但是依然要等待獲取 S Next-Key Lock 。

有個困惑很久的疑問：出現唯一沖突需要加 S Next-Key Lock 是事實，但是加鎖的意義是什么？還是說是通過 S Next-Key Lock 來實現的唯一約束檢查，但是這樣意味著在插入沒有遇到唯一沖突的時候，這個鎖會立刻釋放，這不符合二階段鎖原則。這點希望能與大家一起討論得到好的解釋。

如果是在 REPEATABLE-READ，除以上所說的唯一約束沖突外，gap lock 的存在是這樣的：

普通索引（非唯一索引）的S/X Lock，都帶 gap 屬性，會鎖住記錄以及前1條記錄到后1條記錄的左閉右開區間，比如有[4,6,8]記錄，delete 6，則會鎖住[4,8）整個區間。

對于 gap lock，相信 DBA 們的心情是一樣一樣的，所以我的建議是：

1. 在絕大部分的業務場景下，都可以把 MySQL 的隔離界別設置為 READ-COMMITTED；

2. 在業務方便控制字段值唯一的情況下，盡量減少表中唯一索引的數量。

鎖沖突矩陣

前面我們說的 GAP LOCK 其實是鎖的屬性，另外我們知道 InnoDB 常規鎖模式有：S 和 X，即共享鎖和排他鎖。鎖模式和鎖屬性是可以隨意組合的，組合之后的沖突矩陣如下，這對我們分析死鎖很有幫助：

請點擊輸入圖片描述

網頁題目：mysql鎖怎么加鎖,mysql鎖怎么實現
文章分享：http://www.yijiale78.com/article36/hsidpg.html

成都網站建設公司_創新互聯，為您提供手機網站建設、靜態網站、動態網站、App設計、軟件開發、Google

廣告

聲明：本網站發布的內容（圖片、視頻和文字）以用戶投稿、用戶轉載內容為主，如果涉及侵權請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網站立場，如需處理請聯系客服。電話：028-86922220；郵箱：631063699@qq.com。內容未經允許不得轉載，或轉載時需注明來源： 創新互聯