MySQL InnoDB锁原理剖析

事务的四大特性（ACID）中，隔离性（Isolation）是一个非常重要的特性。隔离性能保证不同的事务操作相同数据（表或行）时互相不干扰，进而保证数据的一致性。其中锁机制是实现事务隔离级别的一种重要方案（另一种是MVCC）。今天我们主要来了解一下MySQL锁的原理。

前提知识

一、怎么开启数据库事务？

1）手工开启事务：

  begin / start transaction;

2）设定事务是否自动开启（off代表事务不会自动提交）：

  set session autocommit = on/off; 

二、怎么结束事务？

1）提交或者回滚都会结束事务：

  commit / rollback; 

三、怎么加锁？

1）自动加锁：

  delete / update / insert  语句会自动加上排他锁；

2）手动加排它锁：

  select * from student where id=1 FOR UPDATE； 

锁是在事务结束的时候释放的，所以如果没有手工开启一个事务，或者没有设置事务自动提交为关闭（off），锁也随事务的自动提交而释放。

锁的原理

MySQL InnoDB的锁到底锁住了什么？

我们先假设MySQL的锁锁定的是一行数据或者记录（Record）。

· 演示1

创建一张没有索引的表：

CREATE TABLE t1 (

id int(11) DEFAULT NULL,

name varchar(255) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO t1 (id, name) VALUES (1, ‘1’);

INSERT INTO t1 (id, name) VALUES (2, ‘2’);

INSERT INTO t1 (id, name) VALUES (3, ‘3’);

INSERT INTO t1 (id, name) VALUES (4, ‘4’);

测试语句：

我们发现，当事务1锁住id=1的数据时，事务2操作id=3的数据行（存在）和id=5的数据行（不存在）都被阻塞了。

实际上，大家可以动手验证一下，表中的其他任何数据行的锁都无法获取，也就是整张表都被锁了。

至此，我们可以确定前面的假设是错误的，MySQL的锁，锁住的不是数据行（或者记录Record），否则不会出现整张表都被锁住的情况。

那为什么会锁住整张表？MySQL锁，到底锁住了什么？我们继续往下验证。

· 演示2

创建一张带主键索引的表：

DROP TABLE IF EXISTS t2;

CREATE TABLE t2 (

id int(11) NOT NULL,

name varchar(255) DEFAULT NULL,

PRIMARY KEY (id)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO t2 (id, name) VALUES (1, ‘1’);

INSERT INTO t2 (id, name) VALUES (4, ‘4’);

INSERT INTO t2 (id, name) VALUES (7, ‘7’);

INSERT INTO t2 (id, name) VALUES (10, ‘10’);

当一个事务锁定的记录有主键索引的时候， 其他事务不可以再次获取这一条记录的锁，但是其他的记录都不受影响。

结论：如果InnoDB的行锁不是通过锁定记录实现的，那么可能和索引有关？

· 演示3

创建一张有主键索引和唯一索引的表，其中id列上有主键索引，name列上有唯一索引：

CREATE TABLE t3 (

id int(11) NOT NULL,

name varchar(255) DEFAULT NULL,

PRIMARY KEY (id),

UNIQUE KEY uk_name (name)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO t3 (id, name) VALUES (1, ‘Qingshan’);

INSERT INTO t3 (id, name) VALUES (4, ‘Seven’);

INSERT INTO t3 (id, name) VALUES (7, ‘Tom’);

INSERT INTO t3 (id, name) VALUES (11, ‘Mic’);

当一个事务通过有唯一索引的name列锁定一条记录时，另一个事务尝试获取相同的锁，被阻塞了。改用id列去获取同一行记录的锁，一样被阻塞。

这个跟我们前面的结论不是矛盾了吗？我们已经确定MySQL的锁锁定的不是一行记录，如果锁跟索引相关，这里是两个索引（一个唯一索引，一个主键索引），应该不会冲突才对。

事实上，我们得到的结论并没错，MySQL InnoDB的锁就是靠锁住索引项来实现的。

我们需要解决两个问题

1、为什么锁住唯一索引后，主键索引也会被锁住？

这个是由InnoDB索引的存储和检索方式决定的。辅助索引中存储的是二级索引和主键的ID，所以锁住辅助索引后，会根据主键ID找到对应的主键索引，也锁定之。

而通过主键索引检索数据加锁，则只会锁住主键索引。

2、为什么表没有索引，表里所有的记录都会被锁住？

当表上没有创建索引的时候，InnoDB会为每一行创建一个隐藏的主键作为聚集索引。这个隐藏的主键是一个6个字节的列，该列的值会随着数据的插入自增。

当不通过索引检索数据的时候，MySQL会使用全表扫描，此时所有行的索引都会被锁定，行锁升级为表锁。

原文：https://gper.club/articles/7e7e7f7ff0g52gc7