mysql deadlock

 4種事務隔離級別：

讀資料一致性及允許的併發副作用   隔離級別	讀資料一致性	髒讀	不可重複讀	幻讀
未提交讀（Read uncommitted）	最低階別，只能保證不讀取物理上損壞的資料	是	是	是
已提交度（Read committed）	語句級	否	是	是
可重複讀（Repeatable read）	事務級	否	否	是？
可序列化（Serializable）	最高階別，事務級	否	否	否

Mysql 的repeatable read 是不存在幻讀的， 這是由它的複製和恢復機制決定的。

Serializable 普通的select 語句也會加鎖， 序列的， 併發效能低

 

 

 

請求鎖模式      是否相容   當前鎖模式	X	IX	S	IS
X	衝突	衝突	衝突	衝突
IX	衝突	相容	衝突	相容
S	衝突	衝突	相容	相容
IS	衝突	相容	相容	相容

 

 

SELECT ... IN SHARE MODE 顯示加共享鎖

SELECT... FOR UPDATE 顯示加排他鎖

Innodb 行鎖實現方式：

（1）    在不透過索引條件查詢的時候，InnoDB確實使用的是表鎖，而不是行鎖。（這個也是因為所有的主鍵被鎖定）

（2）       由於MySQL的行鎖是針對索引加的鎖，不是針對記錄加的鎖，所以雖然是訪問不同行的記錄，但是如果是使用相同的索引鍵，是會出現鎖衝突的。應用設計的時候要注意這一點

（3）       當表有多個索引的時候，不同的事務可以使用不同的索引鎖定不同的行，另外，不論是使用主鍵索引、唯一索引或普通索引，InnoDB都會使用行鎖來對資料加鎖

我覺得這個理解是有誤的，因為mysql的鎖是加在索引上的，怎麼又會加在資料上？正確的理解是，對某條記錄加鎖的時候， 會對這條記錄的主鍵索引或者唯一索引加鎖， 所以會看到相同的記錄相互鎖定， 雖然用的索引不同。 沒有指定主鍵的時候， 系統會分配預設的主鍵（GEN_CLUST_INDEX）。

（4）       即便在條件中使用了索引欄位，但是否使用索引來檢索資料是由MySQL透過判斷不同執行計劃的代價來決定的，如果MySQL認為全表掃描效率更高，比如對一些很小的表，它就不會使用索引，這種情況下InnoDB將使用表鎖，而不是行鎖。因此，在分析鎖衝突時，別忘了檢查SQL的執行計劃，以確認是否真正使用了索引。

 

Be careful:

例子中tab_with_index表的name欄位有索引，但是name欄位是varchar型別的，如果where條件中不是和varchar型別進行比較，則會對name進行型別轉換，而執行的全表掃描

間隙鎖（Next-Key鎖）即範圍鎖

當我們用範圍條件而不是相等條件檢索資料，並請求共享或排他鎖時，InnoDB會給符合條件的已有資料記錄的索引項加鎖；對於鍵值在條件範圍內但並不存在的記錄，叫做“間隙（GAP)”，InnoDB也會對這個“間隙”加鎖，這種鎖機制就是所謂的間隙鎖（Next-Key鎖）。

因此，在實際應用開發中，尤其是併發插入比較多的應用，我們要儘量最佳化業務邏輯，儘量使用相等條件來訪問更新資料，避免使用範圍條件。

還要特別說明的是，InnoDB除了透過範圍條件加鎖時使用間隙鎖外，如果使用相等條件請求給一個不存在的記錄加鎖，InnoDB也會使用間隙鎖！對不存在的鍵值的範圍鎖， 譬如要新加資料的時候insert。

(5) Update 語句中的set欄位也會成為鎖物件, a and b all have index

Session a

update test_innodb_lock set b='4' where a =1;

session b:

mysql> update test_innodb_lock set a = 7 where b='4';

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

mysql> rollback;

Query OK, 0 rows affected (0.00 sec)

mysql> update test_innodb_lock set a = 7 where b='5';

Query OK, 1 row affected (0.05 sec)

Rows matched: 1  Changed: 1  Warnings: 0

兩次insert 語句除非是主鍵相同，就算普通索引鍵重複， 也不會相互鎖。

但是update和 delete 有範圍鎖，很容易和insert 衝突。

譬如insert into test (a,b) (1,1) (4,4) (7,7) (10,10)

Delete from test where a >=7. 會鎖4-7， 大於7的， 4之前的不鎖

Delete from test where a>=7 and a<=10會鎖4-7， 大於7的。 因為10為最後一個數， 所以10以後的都會鎖

Delete from test where a<=10, 全部都鎖，不是受只有10之前的會鎖。 因為10時最後一個

如果多了一個值（100,100）. 則100以後的就不會鎖， 100之前的還是會鎖

這就是間隙鎖的實際， 注意。

恢復和複製的需要，對InnoDB鎖機制的影響

一是MySQL的恢復是SQL語句級的，也就是重新執行BINLOG中的SQL語句。這與Oracle資料庫不同，Oracle是基於資料庫檔案塊的。

·二是MySQL的Binlog是按照事務提交的先後順序記錄的，恢復也是按這個順序進行的。這點也與Oralce不同，Oracle是按照系統更新號（System Change Number，SCN）來恢復資料的，每個事務開始時，Oracle都會分配一個全域性唯一的SCN，SCN的順序與事務開始的時間順序是一致的。

從上面兩點可知，MySQL的恢復機制要求：在一個事務未提交前，其他併發事務不能插入滿足其鎖定條件的任何記錄，也就是不允許出現幻讀，這已經超過了ISO/ANSI SQL92“可重複讀”隔離級別的要求，實際上是要求事務要序列化。這也是許多情況下，InnoDB要用到間隙鎖的原因，比如在用範圍條件更新記錄時，無論在Read Commited或是Repeatable Read隔離級別下，InnoDB都要使用間隙鎖，但這並不是隔離級別要求的

 

INSERT...SELECT...和CREATE TABLE...SELECT...語句 會對元表加共享鎖。

因此，INSERT...SELECT...和CREATE TABLE...SELECT...語句，可能會阻止對源表的併發更新，造成對源表鎖的等待。如果查詢比較複雜的話，會造成嚴重的效能問題，我們在應用中應儘量避免使用。實際上，MySQL將這種SQL叫作不確定（non-deterministic）的SQL，不推薦使用

InnoDB在不同隔離級別下的一致性讀及鎖的差異

死鎖：

發生死鎖後，InnoDB一般都能自動檢測到，並使一個事務釋放鎖並回退（事物比較小的， insert/update/delete 資料量相對較小的），另一個事務獲得鎖，繼續完成事務。但在涉及外部鎖，或涉及表鎖的情況下，InnoDB並不能完全自動檢測到死鎖，這需要透過設定鎖等待超時引數innodb_lock_wait_timeout來解決。需要說明的是，這個引數並不是只用來解決死鎖問題，在併發訪問比較高的情況下，如果大量事務因無法立即獲得所需的鎖而掛起，會佔用大量計算機資源，造成嚴重效能問題，甚至拖跨資料庫。我們透過設定合適的鎖等待超時閾值，可以避免這種情況發生

通常來說，死鎖都是應用設計的問題，透過調整業務流程、資料庫物件設計、事務大小，以及訪問資料庫的SQL語句，絕大部分死鎖都可以避免。下面就透過例項來介紹幾種避免死鎖的常用方法：也是減少鎖發生機率

1． 在應用中，如果不同的程式會併發存取多個表，應儘量約定以相同的順序來訪問表，這樣可以大大降低產生死鎖的機會。

2． 在程式以批次方式處理資料的時候，如果事先對資料排序，保證每個執行緒按固定的順序來處理記錄，也可以大大降低出現死鎖的可能

3． 在事務中，如果要更新記錄，應該直接申請足夠級別的鎖，即排他鎖，而不應先申請共享鎖，更新時再申請排他鎖，因為當使用者申請排他鎖時，其他事務可能又已經獲得了相同記錄的共享鎖，從而造成鎖衝突，甚至死鎖。（這種情況實際情況中基本不可能出現）。

4．  降低資料庫隔離級別

5．  減少事務大小

6．  在程式設計中總是捕獲並處理死鎖異常是一個很好的程式設計習慣。

7．  使用表鎖， 缺點是降低了併發

 

在瞭解InnoDB鎖特性後，使用者可以透過設計和SQL調整等措施減少鎖衝突和死鎖，包括：

·儘量使用較低的隔離級別；

·精心設計索引，並儘量使用索引訪問資料，使加鎖更精確，從而減少鎖衝突的機會；

·選擇合理的事務大小，小事務發生鎖衝突的機率也更小；

·給記錄集顯示加鎖時，最好一次性請求足夠級別的鎖。比如要修改資料的話，最好直接申請排他鎖，而不是先申請共享鎖，修改時再請求排他鎖，這樣容易產生死鎖；

·不同的程式訪問一組表時，應儘量約定以相同的順序訪問各表，對一個表而言，儘可能以固定的順序存取表中的行。這樣可以大大減少死鎖的機會；

·儘量用相等條件訪問資料，這樣可以避免間隙鎖對併發插入的影響；

·不要申請超過實際需要的鎖級別；除非必須，查詢時不要顯示加鎖；

·對於一些特定的事務，可以使用表鎖來提高處理速度或減少死鎖的可能。