MySQL鎖相關問題學習

參考文章鏈接：Mysql常見的七種鎖詳細介紹

參考文章鏈接：MySQL的鎖與事務隔離級別詳解

個人博客文章鏈接：MySQL鎖相關問題學習

MySQL中有幾種鎖？

常見的是7種鎖，還有一種不常見的預測鎖

行鎖（Record Locks）屬于行級鎖，悲觀鎖

間隙鎖（Gap Locks）屬于行級鎖，悲觀鎖

臨鍵鎖（Next-key Locks）屬于行級鎖，悲觀鎖

(讀)共享鎖/(寫)排他鎖（Shared Locks/Exclusive Locks）屬于行級鎖，悲觀鎖

意向共享鎖/意向排他鎖（Intention Shared Locks/Intention Exclusive Locks）屬于表級鎖，悲觀鎖

插入意向鎖（Insert Intention Locks）屬于特殊的間隙鎖，悲觀鎖

自增鎖（Auto-inc Locks）屬于表級鎖

MySQL中如何劃分鎖？

按照對數據操作的鎖粒度來分：（鎖定粒度依次遞增）

1.行級鎖

2.間隙鎖

3.頁級鎖

4.表級鎖

按照鎖的共享策略來分：

1.共享鎖

2.排他鎖

3.意向共享鎖

4.意向排他鎖

從加鎖策略上分：

樂觀鎖

悲觀鎖

其他：

自增鎖

按照對數據操作的鎖粒度來分

1. 不同存儲引擎使用的鎖的類型？

MYISAM和MEMORY采用：表級鎖(table-level locking)

BDB采用：頁面鎖(page-level locking)或表級鎖，默認為頁面鎖

InnoDB支持：行級鎖(row-level locking)和表級鎖，默認為行級鎖

2. 行級鎖Record Lock（偏寫）

行級鎖介紹

行級鎖（記錄鎖）是MySQL中鎖定粒度最細的一種鎖。表示單個行記錄上的鎖，行鎖一定是作用在索引上的。行級鎖能大大減少數據庫操作的沖突，其加鎖粒度最小，但加鎖的開銷也最大。

行級鎖可分為：

共享鎖

排他鎖

行鎖的種類：

行級鎖（Record Locks）：單個行記錄上的鎖。

間隙鎖（Gap Locks）：間隙鎖，鎖定一個范圍，但不包括記錄本身。比如鎖定a=5以及其前后2個范圍內的數據，也就是將a=3,4,6,7這些行都鎖了起來，不包括a=5本身。GAP鎖的目的，是為了防止同一事務的兩次當前讀，出現幻讀的情況。

臨鍵鎖（Next-key Locks）：鎖定一個范圍，并且鎖定記錄本身。比如鎖定a=5以及其前后2個范圍內的數據，也就是將a=3,4,5,6,7這些行都鎖了起來。對于行的查詢，都是采用該方法，主要目的是解決幻讀的問題。

行級鎖特點

開銷大，加鎖慢，會出現死鎖。發生鎖沖突的概率最低，并發度也最高。

3. 間隙鎖Gap Lock

間隙鎖介紹

間隙鎖，鎖定一個范圍，但不包括記錄本身（它的鎖粒度比記錄鎖的鎖整行更大一些，他是鎖住了某個范圍內的多個行，包括根本不存在的數據），隙鎖一定是開區間，比如（3，5）。

GAP鎖的目的，是為了防止同一事務的兩次當前讀，出現幻讀的情況。該鎖只會在隔離級別是RR(可重復讀)或者以上的級別內存在。間隙鎖的目的是為了讓其他事務無法在間隙中新增數據。

4. 臨鍵鎖Next-Key Lock

臨鍵鎖介紹

它是記錄鎖和間隙鎖的結合，鎖定一個范圍，并且鎖定記錄本身。對于行的查詢，都是采用該方法，主要目的是解決幻讀的問題。next-key鎖是InnoDB默認的鎖，臨鍵鎖是是一個左開右閉的區間，比如（3，5]。

next-key lock的效果相當于一個記錄鎖加一個間隙鎖。當next-key lock加在某索引上，則該記錄和它前面的區間都被鎖定。假設有記錄1, 3, 5, 7，現在記錄5上加next-key lock，則會鎖定區間(3, 5]，任何試圖插入到這個區間的記錄都會阻塞。

record lock、gap lock、next-key lock，都是加在索引上的。假設有記錄1,3,5,7，則5上的記錄鎖會鎖住5，5上的gap lock會鎖住(3,5)，5上的next-key lock會鎖住(3,5]。

注意，next-Key鎖規定是左開右閉區間!

以這個圖為例name是主鍵，id是普通索引，插入id=10，它加的next-key其實就是一個左開右閉，id=6本身沒有加鎖，所以是開區間，id=10本身加鎖了，所以是閉區間。即(6，10]

5. 表級鎖（偏讀）

表級鎖介紹

表級鎖是mysql中鎖定粒度最大的一種鎖，表示對當前操作的整張表加鎖，它實現簡單，資源消耗較少，被大部分mysql引擎支持。最常使用的MYISAM與InnoDB都支持表級鎖定。

表級鎖可分為：

表共享讀鎖（共享鎖）

表獨占寫鎖（排他鎖）

表級鎖特點

開銷小，加鎖快，不會出現死鎖。發生鎖沖突的概率最高，并發度也最低。

LOCK TABLE my_table_name READ; 用讀鎖鎖表，會阻塞其他事務修改表數據。

LOCK TABLE my_table_name WRITE; 用寫鎖鎖表，會阻塞其他事務讀和寫。

不同存儲引擎中的表級鎖

在對某個表執行SELECT、INSERT、DELETE、UPDATE語句時，InnoDB存儲引擎是不會為這個表添加表級別的 S鎖或者X鎖的，如果想加表級鎖需要手動顯式地聲明。

MyISAM在執行查詢語句（SELECT）前，會自動給涉及的所有表加讀鎖，在執行更新操作（UPDATE、DELETE、INSERT等）前，會自動給涉及的表加寫鎖，這個過程并不需要用戶干預，因此，用戶一般不需要直接用LOCK TABLE命令給MYISAM表顯式加鎖。

SQL基本操作

建表SQL:

CREATE TABLE `mylock` ( `id` INT (11) NOT NULL AUTO_INCREMENT, `NAME` VARCHAR (20) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE = MyISAM DEFAULT CHARSET = utf8;

1

2

3

4

5

插入數據：

INSERT INTO`test`.`mylock` (`id`, `NAME`) VALUES ('1', 'a'); INSERT INTO`test`.`mylock` (`id`, `NAME`) VALUES ('2', 'b'); INSERT INTO`test`.`mylock` (`id`, `NAME`) VALUES ('3', 'c'); INSERT INTO`test`.`mylock` (`id`, `NAME`) VALUES ('4', 'd');

1

2

3

4

手動增加表鎖（讀鎖/寫鎖）

lock table 表名稱 read/write,表名稱2 read/write;

1

查看表上加過的鎖

show open tables;

1

刪除表鎖

unlock tables;

1

LOCK TABLES t1 READ：對表t1加表級別的S鎖。

LOCK TABLES t1 WRITE：對表t1加表級別的X鎖。

盡量不用這兩種方式去加鎖，因為InnoDB的優點就是行鎖，所以盡量使用行鎖，性能更高。

5. 頁級鎖

頁級鎖介紹

頁級鎖是 MySQL 中鎖定粒度介于行級鎖和表級鎖中間的一種鎖。表級鎖速度快，但沖突多，行級沖突少，但速度慢。因此，采取了折中的頁級鎖，一次鎖定相鄰的一組記錄。BDB引擎默認 支持頁級鎖。

頁級鎖特點

開銷和加鎖時間界于表鎖和行鎖之間；會出現死鎖；鎖定粒度界于表鎖和行鎖之間，并發度一般。

按照鎖的共享策略來分

共享鎖和排他鎖在MySQL中具體的實現就是讀鎖和寫鎖：

讀鎖（共享鎖）：Shared Locks（S鎖），針對同一份數據，多個讀操作可以同時進行而不會互相影響

寫鎖（排它鎖）：Exclusive Locks（X鎖），當前寫操作沒有完成前，它會阻斷其他寫鎖和讀鎖

IS鎖：意向共享鎖Intention Shared Lock。當事務準備在某條記錄上加S鎖時，需要先在表級別加一個IS鎖。

IX鎖：意向排他鎖Intention Exclusive Lock。當事務準備在某條記錄上加X鎖時，需要先在表級別加一個IX鎖。

IS、IX鎖是表級鎖，它們的提出僅僅為了在之后加表級別的S鎖和X鎖時可以快速判斷表中的記錄是否被上鎖，以避免用遍歷的方式來查看表中有沒有上鎖的記錄。就是說當對一個行加鎖之后，如果有打算給行所在的表加一個表鎖，必須先看看該表的行有沒有被加鎖，否則就會出現沖突。IS鎖和IX鎖就避免了判斷表中行有沒有加鎖時對每一行的遍歷。直接查看表有沒有意向鎖就可以知道表中有沒有行鎖。

注意：如果一個表中有多個行鎖，他們都會給表加上意向鎖，意向鎖和意向鎖之間是不會沖突的。

1. 共享鎖/排他鎖

共享鎖/排他鎖都只是行鎖，與間隙鎖無關。

共享鎖是一個事務并發讀取某一行記錄所需要持有的鎖。針對同一份數據，多個讀操作可以同時進行而不會互相影響；

排他鎖是一個事務并發更新或刪除某一行記錄所需要持有的鎖。當前寫操作沒有完成前，它會阻斷其他寫鎖和讀鎖；

讀鎖會阻塞寫，但是不會阻塞讀。而寫鎖則會把其他線程的讀和寫都阻塞。

2. 意向共享鎖/意向排他鎖

意向共享鎖/意向排他鎖屬于表鎖，且取得意向共享鎖/意向排他鎖是取得共享鎖/排他鎖的前置條件。

(IS)意向共享鎖 Intention Shared Lock：當事務準備在某條記錄上加S鎖時，需要先在表級別加一個IS鎖。

(IX)意向排他鎖 Intention Exclusive Lock：當事務準備在某條記錄上加X鎖時，需要先在表級別加一個IX鎖。

IS、IX鎖是表級鎖，它們的提出僅僅為了在之后加表級別的S鎖和X鎖時可以快速判斷表中的記錄是否被上鎖，以避免用遍歷的方式來查看表中有沒有上鎖的記錄。就是說當對一個行加鎖之后，如果有打算給行所在的表加一個表鎖，必須先看看該表的行有沒有被加鎖，否則就會出現沖突。IS鎖和IX鎖就避免了判斷表中行有沒有加鎖時對每一行的遍歷。直接查看表有沒有意向鎖就可以知道表中有沒有行鎖。

共享鎖/排他鎖與意向共享鎖/意向排他鎖的兼容性關系：

這四種鎖都屬于悲觀鎖，如果一個表中有多個行鎖，他們都會給表加上意向鎖，意向鎖之間都不會發生沖突，排他鎖跟誰都沖突。

3. 插入意向鎖(IIX)

插入意向鎖是一種特殊的間隙鎖，但不同于間隙鎖的是，該鎖只用于并發插入操作。如果說間隙鎖鎖住的是一個區間，那么插入意向鎖鎖住的就是一個點。

與間隙鎖的另一個非常重要的差別是：盡管插入意向鎖也屬于間隙鎖，但兩個事務卻不能在同一時間內一個擁有間隙鎖，另一個擁有該間隙區間內的插入意向鎖（當然，插入意向鎖如果不在間隙鎖區間內則是可以的）。這里我們再回顧一下共享鎖和排他鎖：共享鎖用于讀取操作，而排他鎖是用于更新或刪除操作。也就是說插入意向鎖、共享鎖和排他鎖涵蓋了常用的增刪改查四個動作。

從加鎖策略上分：樂觀鎖和悲觀鎖

1. 悲觀鎖

悲觀鎖 認為對于同一個數據的并發操作，一定是會發生修改的（增刪改多，查少），哪怕沒有修改，也會認為修改。因此對于同一個數據的并發操作，悲觀鎖采取加鎖的形式。悲觀的認為，不加鎖的并發操作一定會出問題。

悲觀鎖用的就是數據庫的行鎖，認為數據庫會發生并發沖突，直接上來就把數據鎖住，其他事務不能修改，直至提交了當前事務。

2. 樂觀鎖

樂觀鎖 則認為對于同一個數據的并發操作，是不會發生修改的（增刪改少，查多）。在更新數據的時候，會采用不斷嘗試更新的方式來修改數據。也就是先不管資源有沒有被別的線程占用，直接取申請操作，如果沒有產生沖突，那就操作成功，如果產生沖突，有其他線程已經在使用了，那么就不斷地輪詢。樂觀的認為，不加鎖的并發操作是沒有事情的。就是通過記錄一個數據歷史記錄的多個版本，如果修改完之后發現有沖突再將版本返回到沒修改的樣子，樂觀鎖就是不加鎖。好處就是減少上下文切換，壞處是浪費CPU時間。

樂觀鎖相對悲觀鎖而言，它認為數據一般情況下不會造成沖突，所以在數據進行提交更新的時候，才會正式對數據的沖突與否進行檢測，如果發現沖突了，則讓返回錯誤信息，讓用戶決定如何去做。

樂觀鎖其實是一種思想，認為不會鎖定的情況下去更新數據，如果發現不對勁，才不更新(回滾)。在數據庫中往往添加一個version字段（版本號）來實現。樂觀鎖可以用來避免更新丟失。接下來我們看一下樂觀鎖在數據表和緩存中的實現。

利用數據版本號（version）機制是樂觀鎖最常用的一種實現方式。一般通過為數據庫表增加一個數字類型的 “version” 字段，當讀取數據時，將version字段的值一同讀出，數據每更新一次，對此version值+1。當我們提交更新的時候，判斷數據庫表對應記錄的當前版本信息與第一次取出來的version值進行比對，如果數據庫表當前版本號與第一次取出來的version值相等，則予以更新，否則認為是過期數據，返回更新失敗。

例子：

-- step1: 查詢出商品信息 select (quantity,version) from items where id=100; -- step2: 根據商品信息生成訂單 insert into orders(id,item_id) values(null,100); -- step3: 修改商品的庫存 update items set quantity=quantity-1,version=version+1 where id=100 and version=#{version};

1

2

3

4

5

6

7

8

既然可以用version，那還可以使用時間戳字段，該方法同樣是在表中增加一個時間戳字段，和上面的version類似，也是在更新提交的時候檢查當前數據庫中數據的時間戳和自己更新前取到的時間戳進行對比，如果一致則OK，否則就是版本沖突。

需要注意的是，如果你的數據表是讀寫分離的表，當master表中寫入的數據沒有及時同步到slave表中時會造成更新一直失敗的問題。此時，需要強制讀取master表中的數據（將select語句放在事務中）。即：把select語句放在事務中，查詢的就是master主庫了！

樂觀鎖廣泛用于狀態同步，我們經常會遇到并發對一條物流訂單修改狀態的場景，所以此時樂觀鎖就發揮了巨大作用。但是樂觀鎖字段的選用也需要非常講究，一個好的樂觀鎖字段可以縮小鎖粒度。

商品庫存扣減時，尤其是在秒殺、聚劃算這種高并發的場景下，若采用version號作為樂觀鎖，則每次只有一個事務能更新成功，業務感知上就是大量操作失敗。因為version的粒度太大，更新失敗的概率也就會變大。

但是如果我們挑選庫存字段作為樂觀鎖（通過比較庫存數來判斷數據版本），這樣我們的鎖粒度就會減小，更新失敗的概率也會大大減小。

-- 以庫存數作為樂觀鎖 -- step1: 查詢出商品信息 select (inventory) from items where id=100; -- step2: 根據商品信息生成訂單 insert into orders(id,item_id) values(null,100); -- step3: 修改商品的庫存 update items set inventory=inventory-1 where id=100 and inventory-1>0;

1

2

3

4

5

6

7

8

9

淘寶秒殺、聚劃算，跑的就是這條SQL，通過挑選樂觀鎖，可以減小鎖力度，從而提升吞吐。

其他：自增鎖AUTO-INC

自增鎖（AUTO-INC鎖）

在執行插入語句時就在表級別加一個AUTO-INC鎖，然后為每條待插入記錄的AUTO_INCREMENT修飾的列分配遞增的值，在該語句執行結束后，再把AUTO-INC鎖釋放掉。這樣一個事務在持有 AUTO-INC鎖的過程中，其他事務的插入語句都要被阻塞，可以保證一個語句中分配的遞增值是連續的。

采用一個輕量級的鎖，在為插入語句生成AUTO_INCREMENT修飾的列的值時獲取一下這個輕量級 鎖，然后生成本次插入語句需要用到的AUTO_INCREMENT列的值之后，就把該輕量級鎖釋放掉， 并不需要等到整個插入語句執行完才釋放鎖。

系統變量innodb_autoinc_lock_mode：

innodb_autoinc_lock_mode值為0：采用AUTO-INC鎖。

innodb_autoinc_lock_mode值為2：采用輕量級鎖。

當innodb_autoinc_lock_mode值為1：當插入記錄數不確定是采用AUTO-INC鎖，當插入記錄數確定時采用輕量級鎖

自增鎖是一種特殊的表級鎖，主要用于事務中插入自增字段，也就是我們最常用的自增主鍵id。通過innodb_autoinc_lock_mode參數可以設置自增主鍵的生成策略。防止并發插入數據的時候自增id出現異常。

當一張表的某個字段是自增列時，innodb會在該索引的末位加一個排它鎖。為了訪問這個自增的數值，需要加一個表級鎖，不過這個表級鎖的持續時間只有當前sql，而不是整個事務，即當前sql執行完，該表級鎖就釋放了。其他線程無法在這個表級鎖持有時插入任何記錄。

MySQL 數據庫

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。