Mysql鎖專題：InnoDB鎖概述

一 概述

InnoDB與MyISAM有兩處不同：

1）InnoDB支持事務；

2）默認采用行級鎖（也可以支持表級鎖）

對于更新操作（UPDATE、INSERT、DELETE），InnoDB會自動給涉及到的數據集加排他鎖（X）；對于普通的SELECT語句，InnoDB不加任何鎖（所以即使有一個線程的寫操作在占用鎖，不影響其他線程的讀，但是如果某個線程試圖加共享鎖則不行）。

InnoDB的行鎖模式及加鎖方法

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

共享鎖(S)：允許一個事務去讀一行，阻止其他事務獲得相同數據集的排他鎖；

排他鎖(X)：允許獲得排他鎖的事務更新數據，阻止其他事務獲得相同數據集的共享讀鎖和排他寫鎖。

另外，為了允許行鎖和表鎖共存，InnoDB還有兩張內部使用的意向鎖，都是表鎖：

意向共享鎖(IS)：事務打算給數據行加行共享鎖，事務在給一個數據行加共享鎖前先必須取得該表的意向共享鎖；

意向排他鎖(IX)：事務打算給數據行加行排他鎖，事務在給一個數據行加排他鎖前必須先取得該表的意向排他鎖。

上述幾種鎖的兼容性如下：

表20-6 InnoDB行鎖模式兼容性列表

如果一個事務請求的鎖模式與當前的鎖兼容，InnoDB就將請求的鎖授予該事務；反之，如果兩者不兼容，該事務就要等待鎖釋放。

意向鎖是InnoDB自動加的，不需用戶干預。

對于更新操作（UPDATE、INSERT、DELETE），InnoDB會自動給涉及到的數據集加排他鎖（X）；對于普通的SELECT語句，InnoDB不加任何鎖（所以即使有一個線程的寫操作在占用鎖，不影響其他線程的讀，但是如果某個線程試圖加共享鎖則不行）。

顯式的給記錄集加共享鎖：

共享鎖： SELECT * FROM tableName WHERE …. LOCK IN SHARE MODE

排他鎖： SELECT * FROM tableName WHERE …. FOR UPDATE

二、 共享鎖中執行update操作容易導致死鎖

注意：**用共享鎖然后執行了update操作，則有可能和別的線程的update操作發生鎖沖突，從而死鎖。死鎖后Mysql會自動關閉一個線程的事務操作，讓鎖被一個線程使用。**如下所示：

1）線程A和線程B對同一行記錄使用了共享鎖，兩個線程讀都沒有問題（讀不需要加鎖，不管當前記錄加了共享鎖還是排他鎖，都不影響單獨的讀操作）；

2）線程A進行更新操作，因為更新操作需要加獨占鎖，而線程B還對當前記錄保留了共享鎖，故線程A無法獲得當前線程的獨占鎖，要等待線程B釋放共享鎖；

3）線程B也進行了更新操作，它也要對當前記錄加獨占鎖。那么顯然它也無法獲得到該記錄的獨占鎖，兩個線程都會等待下去，也就是死鎖。

4）此時Mysql會自動根據一定規則把鎖交給某個線程，另一個線程失去鎖重新啟動事務。

另外，注意，默認情況下單行執行后就會自動提交事務，此時鎖也就被自動釋放了。需要關閉事務的自動提交。

set autocommit = 0;

對于需要更新的操作，應當直接使用排他鎖。這種情況下，因為線程A已經占有了排他鎖，線程B無法獲得共享鎖和排他鎖，只能等待。但是注意，InnoDB的讀操作不需要加鎖，所以可以照常的讀。

當使用SELECT…FOR UPDATE加鎖后再更新記錄，出現如表20-8所示的情況。

表20-8 InnoDB存儲引擎的排他鎖例子

三、 InnodDB行鎖實現方式

InnoDB行鎖是通過給索引上的索引項加鎖來實現的。這一點Mysql和Oracle不同，Oracle是通過直接在數據塊中對相應數據行加鎖來實現的。

InnoDB的這種特性意味著：只有通過索引條件檢索數據，InnoDB才使用行級鎖；否則InnoDB將使用表鎖。

1）非索引字段加鎖變成表鎖

表20-9 InnoDB存儲引擎的表在不使用索引時使用表鎖例子

注意，對于表沒有加索引，線程A僅要求獲取id=1的記錄的獨占鎖，但是因為沒有加索引，所以該語句鎖住了整個表，使用了表鎖。

當我們對id行添加索引

alter table tab_with_index add index id(id);

則會有下面的例子：

2）相同索引鍵導致阻塞

由于Mysql的行鎖是針對索引加的鎖，而不是針對記錄加的，所以即使是訪問不同行，但是如果使用了相同的索引鍵，依然會沖突：

mysql> select * from tab_with_index where id = 1;

±-----±-----+

| id | name |

±-----±-----+

| 1 | 1 |

| 1 | 4 |

±-----±-----+

例如對于上表，如果對id加了索引，但是有兩個記錄的id相同，也就是索引相同。此時兩個線程分別試圖獲取兩個記錄的獨占鎖依然會導致阻塞，因為mysql的行鎖是加在索引上的。

3）不同索引鍵指向同一行記錄也會導致阻塞

mysql> alter table tab_with_index add index name(name);

alter table tab_with_index add index id(id);

假設我們分別對id和name增加索引，那么不管是什么索引，InnoDB都會使用行鎖來鎖定不同的行。

如果是不同的索引，但是指向了同一條記錄，那么依然會導致阻塞。

我的理解是不同索引最后指向了同一條主鍵id，鎖住了注解id，故依然會阻塞，應該不是鎖住記錄。

4）間隙鎖

當我們使用范圍條件而不是相等條件來檢索數據，并請求共享或排他鎖時，InnoDB會給所有符合條件的已有數據記錄的索引加鎖；對于鍵值在條件范圍內但是并不存在的記錄，叫做間隙gap，InnoDB也會對這些間隙加鎖。這種鎖機制就是間隙鎖。

舉例來說，假如emp表中只有101條記錄，其empid的值分別是 1,2,…,100,101，下面的SQL：

Select * from emp where empid > 100 for update;

這是一個范圍條件的檢索，InnoDB不僅會對empid為101的記錄加鎖，對于大于101的不存在間隙也會加鎖。

**Mysql使用間隙鎖的目的是防止幻讀（應該只是一部分滿足，不能完全回避），以滿足相關隔離級別的要求。**比如對于上面的情況，如果不加鎖，那么其他事務插入了empid為102的記錄，則會導致本事務內再次執行上述語句時得到empid為102的記錄，也就導致了幻讀。另一方面，也是為了滿足其回復和復制的需要。

因此，在使用范圍條件檢索并鎖定記錄時，InnoDB的這種間隙加鎖機制會阻塞符合條件范圍內鍵值的并發插入，從而導致嚴重的鎖等待。因此，對于并發插入較多的應用，我們要盡量優化業務邏輯，盡量用相等條件來訪問更新數據，避免使用范圍條件。

還要特別說明的是，InnoDB除了通過范圍條件加鎖時使用間隙鎖外，如果使用相等條件請求給一個不存在的記錄加鎖，InnoDB也會使用間隙鎖！

5）關于恢復和復制的需要，對InnoDB鎖機制的影響

Mysql通過BINLog記錄執行成功的INSERT、UPDATE、DELETE等更新數據的SQL語句，并由此實現MySql數據庫的回復和主從復制。Mysql的恢復記錄（復制實際就是在Slave Mysql不斷的做基于BINLOG的恢復）有以下特點：

一是MySQL的恢復是SQL語句級的，也就是重新執行BINLOG中的SQL語句。

二是MySQL的Binlog是按照事務提交的先后順序記錄的，恢復也是按這個順序進行的。

**根據上述的特點，Mysql的恢復機制要求：在一個事務未提交前，其他并發事務不能插入滿足其鎖定條件的任何記錄，也就是不允許出現幻讀。****這已經超過了ISO/ANSI SQL92“可重復讀”隔離級別的要求，實際上是要求事務要串行化。這也是許多情況下，InnoDB要用到間隙鎖的原因。**比如在用范圍條件更新記錄時，無論是Read Commited還是Repeatable Read隔離級別，InnoDB都要使用間隙鎖，這并不是隔離級別的要求，而是由于Mysql恢復和復制的要求。

MySQL SQL 任務調度

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

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