【MySQL】gap lock 淺析

【定義】

innodb 行級鎖 record-level lock大緻有三種：record lock, gap lock and next-keylocks。

record lock  鎖住某一行記錄  

gap lock     鎖住某一段範圍中的記錄 

next key lock 是前兩者效果的疊加。

下面是mysql官方文檔中相關内容的連結

http://dev.mysql.com/doc/refman/5.1/en/innodb-record-level-locks.html

【實驗環境】

session 1 20:39:29> show create table gap \g

*************************** 1. row ***************************

       table: gap

create table: create table `gap` (

  `id` int(11) default null,

  key `ind_gap_id` (`id`)

) engine=innodb default charset=utf8

1 row in set (0.00 sec)

session 1 20:39:32> select * from gap;      

+------+

| id   |

|   17 |

|   20 |

|   33 |

|   39 |

|   42 |

|   43 |

6 rows in set (0.00 sec)

【實驗】

兩個會話都在repeatable-read 事務隔離級别。且都要在事務中進行。

session 1  20:39:37> start transaction;      

query ok, 0 rows affected (0.00 sec)

session 1  20:39:41> delete from gap where id=33;

query ok, 1 row affected (0.00 sec)

session 20:40:07> 

在會話2中 插入id <20 和 >=39的值 可以執行成功,而當要插入的id [20,39)的值時 會遇到gap lock 。

session 2 20:40:15> start transaction;

session 2 20:40:30> insert into gap values(14);

session 2 20:40:59> insert into gap values(18);

session 2 20:41:06> insert into gap values(20);

error 1205 (hy000): lock wait timeout exceeded; try restarting transaction

session 2 20:41:12> insert into gap values(24);

session 2 20:42:17> 

session 2 20:42:53> insert into gap values(35); 

session 2 20:44:09> 

session 2 20:44:56> insert into gap values(39);

session 2 20:45:13> insert into gap values(40);              

從上面的實驗中可以看出會話1 執行删除語句之後,不僅僅鎖住 id=33的記錄,同時也鎖住區間為[20,39)的記錄。具體的原因是執行delete from gap where id=33語句,mysql 會執行索引掃描并在該表上施加一個next-key lock ,向左掃描到20,向右掃描到39 ,鎖定區間左閉右開,是以lock的範圍是 [20,39)。

【gap 鎖帶來的問題】

生産環境中有這樣的一個情況：

程式會對一個表message 進行update 和insert 

session 1

update message set gmt_modified = now(),deal_times = deal_times +1   , status = 'sending' , gmt_retry = '2012-11-17 23:54:10' 

where message_id=18;

insert into  message (body ,user_id,status,message_type,version,deal_times,gmt_create,gmt_modified,gmt_retry) 

values ('hello ！',-1,'sending','instance_status_sync',2,127,now(),now(),now());

session 2

where message_id=19;

values          ('hello world！',-2,'sending','instance_status_sync',1,17,now(),now(),now());

對于上述程式在無并發情況下,運作正常,但是并發量大的情況下,執行順序可能就會變成下面的：

where message_id= 61;

where message_id= 73;

values ('hello ！',-1,'sending','instance_status_sync',2,127,now(),now(),now()); 

此時 往往會報錯

[error]  could not execute write_rows event on table db.message; deadlock found when trying toget lock; ; try restarting transaction, error_code: 1213;  

前兩條update 類型的語句都已經獲得了[59,75 )區間内記錄的s鎖，然後兩個事務又分别對該區間段内的message_id=10這個位置請求x鎖，這時就發生死鎖，誰都請求不到x鎖，因為互相都持有s鎖。

【解決方案有兩種】

1、改變程式中資料庫操作的邏輯

2、取消gap lock機制

gap locking can be disabled explicitly.this occurs if you change the transaction isolation level to read committed orenable the innodb_locks_unsafe_for_binlog system variable.