MySQL行锁

mysql锁的方式根据隔离级别不同而不同，因为默认隔离级别为repeatable-read可重复读，我们普遍理解为mysql锁实现方式为行锁，行锁就是利用索引实现完成的，mysql的支持的隔离级别有四种，这网上很多介绍，平常用的最多的也就是read-committed和repeatable-read两个，今天就对这两个隔离级别下锁的实现做下对比

首先我们就用默认级别repeatable-read的隔离级别来做测试：

mysql> CREATE TABLE `t1` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
   `age1` int(11) DEFAULT NULL,
   `age2` int(11) DEFAULT NULL,
   PRIMARY KEY (`id`),
   KEY `age1` (`age1`)
  ) ENGINE=InnoDB;

mysql> select * from t1;

±—±-----±-----+

| id | age1 | age2 |

±—±-----±-----+

| 1 | 1 | 2 |

| 2 | 1 | 2 |

| 3 | 1 | 2 |

| 4 | 1 | 4 |

| 5 | 1 | 2 |

| 6 | 2 | 3 |

±—±-----±-----+

创建了一个表t1,插入了部分数据，建立了age1的辅助索引，现在开两个session来做测试：

session1	
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update t1 set age2=55 where age1=1 and id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
rollback

session2
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql>  update t1 set age2=55 where age1=1 and id=3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
rollback

看到这各位看官肯定觉得没问题，就应该是这样的啊，行锁嘛就是利用索引找到对应行进行加锁就好了，不急不急继续测试

session1	
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update t1 set age2=55 where age1=1 and age2=4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
rollback

session2
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update t1 set age2=55 where age1=1 and id=1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
rollback

咦…这是咋回事呢？这更新的明显不是同一行为什么会被阻塞呢？我这暴脾气再用insert做下测试

session1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update t1 set age2=111 where age1=2 ;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
rollback

session2
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t1(age1,age2) values(1,33);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
rollback

…出入数据和更新数据不是同一行业被阻塞了！好吧，来看下锁等待信息

mysql> select * from innodb_lock_waits \G;

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

                wait_started: 2016-04-07 15:35:00

                    wait_age: 00:00:08

               wait_age_secs: 8

                locked_table: `sbtest`.`t1`

                locked_index: PRIMARY

                 locked_type: RECORD

              waiting_trx_id: 33333

         waiting_trx_started: 2016-04-07 15:35:00

             waiting_trx_age: 00:00:08

     waiting_trx_rows_locked: 1

   waiting_trx_rows_modified: 0

                 waiting_pid: 3

               waiting_query: update t1 set age2=55 where age1=1 and id=1

             waiting_lock_id: 33333:33:3:2

           waiting_lock_mode: X

   .....................................

为了节约版面后面截就删掉了，这是上面两个update不同行被阻塞时的记录

mysql> select * from innodb_lock_waits \G;

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

                wait_started: 2016-04-07 15:40:43

                    wait_age: 00:00:26

               wait_age_secs: 26

                locked_table: `sbtest`.`t1`

                locked_index: age1

                 locked_type: RECORD

              waiting_trx_id: 33343

         waiting_trx_started: 2016-04-07 15:40:43

             waiting_trx_age: 00:00:26

     waiting_trx_rows_locked: 1

   waiting_trx_rows_modified: 1

                 waiting_pid: 3

               waiting_query: insert into t1(age1,age2) values(1,33)

             waiting_lock_id: 33343:33:4:7

           waiting_lock_mode: X,GAP

      .......................................

这个是insert时候的记录，第一个可以看出来是在等primary的X锁，但是根据记录我们可以查询出两个update的主键值应该是不同，session1查出来的id应该为4，session2直接指定的id为1，这明显不会一行啊，再来看看结合insert的锁等待情况，这个锁是在age1索引上，但是lock_mode多了一个GAP，这玩意是啥？好吧，只有挠着头皮去官方文档里找了：

Gap lock: This is a lock on a gap between index records, or a lock on the gap before the first or after the last index record.

原来msyql有间隙锁机制，就是把两个记录直接的缝缝给锁住，又或者锁住前后记录，在repeatable-read隔离级别下的mysql会有一个间隙锁算法，假如进行update扫描时，辅助索引有多个记录满足条件是会把这几条记录及记录中间的缝缝都会锁住，而insert的时候也会去判断下一个记录是否有加锁，所以才会发生上面的阻塞。这是mysql为了保证该隔离级别下的可重复读的机制，但也影响到业务并发的效率，可以修改系统参数innodb_locks_unsafe_for_binlog=on就可以关闭改间隙锁机制。该值默认为off，修改事务隔离级别为read-committed也可以避免间隙锁。修改为read-committed来试试看。

mysql> set global tx_isolation=‘read-committed‘; 

Query OK, 0 rows affected (0.00 sec)

全部session连接重新连接一下才能生效，现在来按上面的步骤再测试一下：

session1	
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update t1 set age2=55 where age1=1 and age2=4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
rollback

session2
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update t1 set age2=55 where age1=1 and id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
rollback

这个没问题…

session1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update t1 set age2=111 where age1=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
rollback

session2
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t1(age1,age2) values(1,33);
Query OK, 1 row affected (0.00 sec)
rollback	

也没问题…

mysql在默认隔离级别repeatable-read下，会有gap lock和next-key lock两个算法锁来确保数据可重复读

Next-key lock: This is a combination of a record lock on the index record and a gap lock on the gap before the index record
next-key lock就是范围锁，当我们利用索引范围扫描时，在这一个范围内的数据都会被加锁，以防止幻读。

本文出自 “肖忠” 博客，请务必保留此出处http://xiaozhong991.blog.51cto.com/2354914/1761360