Mysql的鎖分析

1. 間隙鎖（Gap Lock）

間隙鎖：鎖的就是兩條記錄之間的間隙，更具體一點來說，鎖的應該是兩條之間範圍的所有存在和不存在的資料記錄。如：修改 id>3 and id <8，那麼id在 3 和 8 之間 的所有記錄和不存在的記錄都會加鎖，其它事務不能操作這些資料。

開啟間隙鎖：在驗證的時候或者使用間隙鎖的時候，一定要確定間隙鎖在mysql中是開啟的狀态，并且確定隔離級别為REPEATABLE-READ，否則不起作用。在my.cnf中[mysqld]添加配置 innodb_locks_unsafe_for_binlog = 1，重新開機。

檢視間隙鎖是否開啟的指令：

show variables like 'innodb_locks_unsafe_for_binlog'，

并且檢視隔離級别是否為REPEATABLE-READ。

檢視指令：show global variables like 'transaction_isolation';

舉個例子：

表myuser

開啟一個事務并且做一次修改操作：

#事務1
begin;
update `myuser` set `name` = '莉莉1' where id = 8;
#先不送出，wait...


#事務2
begin;
INSERT INTO `myuser`(`id`, `name`, `account`) VALUES (7, '赫赫11', 350);
commit;
           

在開啟間隙鎖和設定隔離級别為REPEATABLE-READ時，先執行事務1，不送出，再執行事務2，送出，那麼事務2會堵塞，等待事務1執行完畢，才能繼續執行。

2.  臨鍵鎖（Next-key Locks）

臨鍵鎖是行鎖與間隙鎖的組合，如上表記錄 id>3 and id<12時，(3,12] 的所有記錄。

3. 無索引行鎖會更新為表鎖

如上表 myuser，account 字段沒有加索引，寫兩個事務腳本：

#事務1
begin;
select * from myuser where account =  300 for update;
#不送出


#事務2
begin;
update myuser set name = '張三1' where account =  1000;
...
...
           

先執行事務1，不送出，再執行事務2，事務2操作的資料和事務1操作的不是同一條記錄，那麼執行事務2時一直處于堵塞狀态，等事務1送出後，事務2才繼續執行。同樣事務2腳本換為插入一條新資料時，一樣也會堵塞，因為行鎖不是對記錄加的鎖，而是對索引加的鎖。

現在我們給這個字段再加個索引，再驗證一下。就不會出現堵塞了。是以，加行鎖時，一定注意不要在無索引的字段上加行鎖。

4. 行鎖分析

show status like 'innodb_row_lock%';

對于各個狀态說明如下：

Innodb_row_lock_current_waits:目前正在等待鎖的數量；

Innodb_row_lock_time:從系統啟動到現在鎖定總時間長度；

Innodb_row_lock_time_avg：每次等待所花平均時間；

Innodb_row_lock_time_max:從系統啟動到現在等待最長的一次所花的時間長度；

Innodb_row_lock_waits:系統啟動到現在總共等待的次數

對于這5個狀态變量，比較重要的是：

Innodb_row_lock_time_avg，

Innodb_row_lock_waits，

Innodb_row_lock_time。

尤其是當等待次數很高，而且每次等待時長也很大的時候，我們就要分析系統中為什麼有這麼多的等待，然後根據分析結果來制定優化。

5.檢視 INFORMATION_SCHEMA 系統庫鎖相關資料表

#檢視事務
select * from information_schema.INNODB_TRX;

#檢視鎖
select * from information_schema.INNODB_LOCKS;

#檢視鎖等待
select * from information_schema.INNODB_LOCK_WAITS;

#釋放鎖，trx_mysql_thread_id可以從INNODB_TRX表中檢視。
kill trx_mysql_thread_id;
           

6. 死鎖

舉個例子說明：

事務1，事務2。以上表 myuser 為操作表。

以下兩個事務中的 select 語句剛好同一時刻執行的話，可能會發生死鎖，兩個事務，互相等待自己需要的資源，且需要的資源同時又被對方加索。

事務1對 id=1 的記錄加鎖，且需要修改 id=2 的記錄。

事務恰好對 id=2 的記錄加鎖，且需要修改 id=1 的記錄。互相等待被對方加了鎖的記錄，且都不釋放鎖。這個時候就可能形成死循環了。

#事務1
begin;

select * from myuser where id = 1 for update;

update myuser set name = '李四1' where id = 2；

commit;

#事務2
begin;

select * from myuser where id = 2 for update;

update myuser set name = '張三1' where id = 1；

commit;
           

多數情況下，mysql會自動檢測到死鎖的存在，并自動復原死鎖的事務。有些情況下又不能檢測到死鎖。這個時候可以通過 5 中的檢視鎖等待，根據事務的線程id查詢到死鎖的sql，進行優化，并且通過 “kill trx_mysql_thread_id” 指令手動結束死鎖。

InnoDB目前處理死鎖的方法是：将持有最少行級排它鎖的事務復原。