InnoDB行锁的实现方式

实现方式

与Oracle的行锁不同,MySql的InnoDB行锁锁定的数据是对应的索引,而不是对应的行数据,也就是说,当检索条件不通过索引时,InnoDB将使用表锁进行锁定。实际应用中,如果不考虑这种情况,可能会造成大量的表锁冲突,影响并发性能。

示例

创建一个测试表并写入测试数据

create table innodb_lock (id int,name varchar(20));
insert into innodb_lock values(1,'a'),(2,'b'),(3,'c'),(4,'d');

注意:此时并没有创建索引。

mysql> select * from innodb_lock;
+------+------+
| id   | name |
+-------------+
|     1|a     |
|     2|b     |
|     3|c     |
|     4|d     |
+-------------+
4 rows in set (0.01 sec)

无索引情况

  • 终端启动两个mysql客户端连入数据库
  • 两个终端各自开启一个事务

      mysql> start transaction;
      Query OK, 0 rows affected (0.00 sec)
    

    为了方便起见,下文将两个事务分别命名为事务A与事务B

  • 事务A 运行update命令修改id为4的数据的name列为e

    mysql> update innodb_lock set name='e' where id=4;
    Query OK, 1 row affected (0.07 sec)
    Rows matched: 1 Changed:1 Warnings:0
    mysql>

    注意此时事务A并没有提交事务。

  • 事务B 同样运行update命令修改id为1的数据的name列为e

    mysql> update innodb_lock set name='e' where id=1;
    

    执行此命令后,客户端会卡在获取锁的阶段上。并没有直接返回。说明此时事务A已经锁住了整张表,与我们的预期一致。
    当事务B等待锁的时间超过innodb_lock_wait_timeout时,事务B会返回如下错误:

    mysql> update innodb_lock set name='e' where id=1;
    ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
    

    innodb_lock_wait_timeout的默认设置为50秒。

  • 事务A运行commit命令提交事务

    mysql> commit;
    Query OK, 0 rows affected (0.00 sec)
    

    此时观察事务B的返回会发现事务B的update操作也成功返回了。代表事务B获取到了表锁。
    事务B返回如下所示

    mysql> update innodb_lock set name='e' where id=1;
    Query OK, 1 row affected (4.08 sec)
    Rows matched: 1 Changed: 1 Warnings: 0

    从事务B的运行时间4.08sec也可以看出来事务B确实在获取锁时进行了等待

    有索引的情况

  • 相同的表结构与数据,但是增加了id为索引。

    mysql> alter table innodb_lock add index id_idx(id);
    Query OK, 0 rows affected (0.21 sec)
    Records: 0 Duplicates: 0 Warnings: 0
  • 两个终端各自开启一个事务

     mysql> start transaction;
     Query OK, 0 rows affected (0.00 sec)
    

    为了方便起见,下文将两个事务分别命名为事务A与事务B

  • 事务A 运行update命令修改id为4的数据的name列为e

    mysql> update innodb_lock set name='e' where id=4;
    Query OK, 0 rows affected (0.03 sec)
    Rows matched: 1 Changed: 0 Warnings: 0
    mysql>

    此时事务A同无索引情况一样并没有提交事务。

  • 事务B 同样运行update命令修改id为1的数据的name列为e

    mysql> update innodb_lock set name='e' where id=1;
    Query OK, 0 rows affected (0.00 sec)
    Rows matched: 1 Changed: 0 Warnings: 0
    mysql>

    可以发现,事务B并没有像无索引情况下那样阻塞住,而是马上返回了运行结果,说明此时事务A与B的锁粒度是行锁,而并不是表锁。同样可以说明MySql的行锁实现方式实际上是锁的索引而并不是行数据。

总结

MySql的行锁实现方式是对对应的索引进行锁定,而不是对对应的行进行锁定。如果命令并不是通过索引的方式进行锁定,那么锁将膨胀为表锁,此时在大并发量下会造成大量的锁等待,影响服务性能。