Mysql Innodb 锁（摘录）

Mysql Innodb 锁介绍

为什么要加锁

总所周知数据库是一个多用户使用的共享资源。当多个用户并发地存取数据时，在数据库中就会产生多个事务同时存取同一数据的情况。若对并发操作不加控制就可能会读取和存储不正确的数据，破坏数据库的一致性。

数据库有 ACID 原则:

1. 原子性(Atomicity):一个事务必须被视为一个不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚，对于一个事务来说，不可能只执行其中的一部分操作，这就是事务的原子性

2. 一致性(Consistency): 数据库总是从一个一致性的状态转换到另一个一致性的状态。

3. 持久性(Durability):一旦事务提交，则其所做的修改不会永久保存到数据库。

4. 隔离性(Isolation)：一个事务所做的修改在最终提交以前，对其他事务是不可见的。

其中 I 是隔离性，标准 SQL 规范中定义了四种隔离级别：

通过 show global variables like '%iso%'; 可以看出 mysql 默认隔离级别是 可重复读.
上图隔离级别越往下，隔离级别越高，问题越少，同时并发度也越低。隔离级别和并发度成反比的。

1. 脏读：事务 A 读取了事务 B 未提交的数据
2. 不可重复读：对于一条记录，事务 A 两次读取的数据变了
3. 幻读：事务 A 按照相同的查询条件，读取到了新增的数据
   

和标准 SQL 规范相比，MySQL 中可重复读解决了脏读，不可能重复度，实现了串行化隔离级别的功能，同时没有严重影响并发。是通过加锁、阻止插入新数据，来解决幻读。

通过客户端修改事务隔离级别演示脏读，不可重复度，幻读

准备数据：

锁的分类

锁的种类繁多比如读锁、写锁、共享锁、互斥锁、行锁等等各种名词，根据自己的理解，简单对这些锁进行了分类。


加锁机制：


乐观锁：先修改，保存时判断是够被更新过，应用级别（CAS:Compare-and-Swap，比较并替换）


悲观锁：先获取锁，再操作修改，数据库级别


锁粒度：
表级锁：开销小，加锁快，粒度大，锁冲突概率大，并发度低，适用于读多写少的情况。
页级锁：BDB 存储引擎（数据库数据按页存储）
行级锁：Innodb 存储引擎，默认选项
兼容性：


S 锁：也叫做读锁、共享锁，对应于我们常用的 select * from users where id =1 lock in share mode


X 锁：也叫做写锁、排它锁、独占锁、互斥锁，对应对于 select * from users where id =1 for update


下面这个表格是锁冲突矩阵，可以看到只有读锁和读锁之间兼容的，写锁和读锁、写锁都是冲突的。


冲突的时候会阻塞当前会话，直到拿到锁或者超时，开启两个事务这个很好模拟。

这里要提到的一点是，S 锁 和 X 锁是可以是表锁，也可以是行锁。
索引组织表




Innodb 中的索引数据结构是 B+ 树，数据是有序排列的，从根节点到叶子节点一层层找到对应的数据。普通索引，也叫做辅助索引，该（叶子 🍃）节点存放的是主键值。主键上的索引叫做聚集索引，表里的每一条记录都存放在主键的节点上。当通过辅助索引 select 查询数据的时候，会先在辅助索引中找到对应的主键值，然后用主键值在聚集索引中找到该条记录。举个例子，用 name=Alice 来查询的时候，会先找到对应的主键值是 18 ，然后用 18 在下面的聚集索引中找到 name=Alice 的记录内容是 77 和 Alice。
了解了索引数据结构的目的是为了说明，行锁是加在索引上的。
1.select * from user where id=10 for update



一条简单的 SQL。在 user 表中查找 id 为 10 的记录，并用 for update 加 X 锁。

这里 User 表中，有 3 个字段， 主键 id 和 另外一个字段 name。下面的表格是 B+ 树索引的简化表达。第一行 id 是索引的节点，第二行和第三行是这行记录，包含了姓名和性别。
如图所示，通过锁住聚集索引中的节点来锁住这条记录。
聚集索引上的锁，比较好理解，锁住 id=10 的索引，即锁住了这条记录。
2. select * from user where name=‘d’ for update


查询 user 表中 name 为 d 的记录，并用 for update 加 X 锁
这里的 name 上加了唯一索引，唯一索引本质上是辅助索引，加了唯一约束。所以会先在辅助索引上找到 name 为 d 的索引记录，在辅助索引中加锁，然后查找聚集索引，锁住对应索引记录。
为什么聚簇索引上的记录也要加锁？试想一下，如果有并发的另外一个 SQL，是直接通过主键索引 id=30 来更新，会先在聚集索引中请求加锁。如果只在辅助索引中加锁的话，两个并发 SQL 之间是互相感知不到的。
3. select * from user where name=‘b’ for update



查询 user 表中 name 为 b 的记录，并用 for update 加 X 锁。这里 name 上加了普通的索引，不是唯一索引。普通索引的值是可以重复的。会先在辅助索引中找到 name 为 b 的两条记录，加 X 锁，然后得到主键值 7 和 30，到聚集索引中加 X 锁。
事情并没有那么简单，如果这时有另一个事务，插入了 name=b,id=40 的记录，却发现是可以插入的。



位置在途中红色线条标注的间隙内，这样就会出现幻读，两次查询得到的结果是不一致的，第一次查到两条数据，插入之后得到三条数据。
为了防止这种情况，出现了另一种锁，gap lcok 间隙锁。锁住的是索引的间隙。



即图中，红色线条标识的空隙。因为新插入 name=b 的记录，可能出现在这三个间隙内。
这张图里出现了三种锁
记录锁：单行记录上的锁
间隙锁：锁定记录之间的范围，但不包含记录本身。
Next Key Lock: 记录锁 + 间隙锁，锁定一个范围，包含记录本身。
4. 意向锁( Intention Locks )

InnoDB 为了支持多粒度(表锁与行锁)的锁并存，引入意向锁。意向锁是表级锁，
IS: 意向共享锁

IX: 意向排他锁
事务在请求某一行的 S 锁和 X 锁前，需要先获得对应表的 IS、IX 锁。
意向锁产生的主要目的是为了处理行锁和表锁之间的冲突，用于表明“某个事务正在某一行上持有了锁，或者准备去持有锁”。比如，表中的某一行上加了 X 锁，就不能对这张表加 X 锁。

如果不在表上加意向锁，对表加锁的时候，都要去检查表中的某一行上是否加有行锁，多麻烦。


5. 插入意向锁（Insert Intention Lock）

Gap Lock 中存在一种插入意向锁，在 insert 操作时产生。
有两个作用：

和 next-key 互斥，阻塞 next-key 锁，防止插入数据，这样就不会幻读。
插入意向锁互相是兼容的，允许相同间隙、不同数据的并发插入

加锁分析
后面会有多个 SQL 语句，先说明一下表结构
select  * from user where id =1 lock in share mode;

select  * from user where id =1 for update;

显式指出要加什么样的锁。上面一个加的是共享锁，下面的是互斥锁。
这里需要强调的一点，需要明确在事务中是用这些锁，不在事务中是没有意义的。
3. 隐式加锁
update user set address '北京' where id=1;
delete from user where id=1;

update 和 delete 也会对查询出的记录加 X 锁，隐式加互斥锁。加锁类型和 for update 类似。
4. 按索引类型
select  * from user where id =1 for update;

select  * from user where id_no ='a22' for update;

select  * from user where name ='王二' for update;

select  * from user where address ='杭州' for update;

四条 SQL，区别在于 where 条件的过滤列，分别是主键、唯一索引、普通索引、无索引。


主键：之前提到过索引组织表，这里会在聚集索引上对查询出的记录，加 X 锁
唯一索引：会在辅助索引上，把在对应的 id_no=a22 的索引加 X 锁，因为是唯一的，所以不是 next-key 锁。然后在主键上，也会在这条记录上加 X 锁。
普通索引：因为不是唯一的，会在辅助索引上，把对应的 id_no=a22 的索引加 next-key 锁。然后在主键加 X 锁。
无索引：首先，是不推荐这种写法，没有索引的话，因为会全表扫描，数据量大的话查询会很慢。这里讨论的是，这种情况下，会加什么锁？ 答案： 首先，聚簇索引上的所有记录，都被加上了 X 锁。其次，聚簇索引每条记录间的间隙(GAP)，也同时被加上了 GAP 锁。在这种情况下，这个表上，除了不加锁的快照度，其他任何加锁的并发 SQL，均不能执行，不能更新，不能删除，不能插入，全表被锁死。这是一个很恐怖的事情，请注意。
5. 记录不存在的情况

前面几个例子中，都是可以查到结果的。如果对应记录不存在会怎样？答案是锁住间隙，不允许插入。mysql 要保证没有其他人可以插入，所以锁住间隙。
6. 普通 insert 语句

在插入之前，会先在插入记录所在的间隙加上一个插入意向锁。
insert 会对插入成功的行加上排它锁，这个排它锁是个记录锁，而非 next-key 锁（当然更不是 gap 锁了），不会阻止其他并发的事务往这条记录之前插入 。
分析死锁
先说一下死锁的定义，死锁是指两个或两个以上的事务在执行过程中，因争夺资源而造成的一种互相等待的现象。这个定义适用于数据库，有几个重点，两个或两个以上的事务，一个事务是不会出现死锁的。争夺的资源一般都是表或者记录。
出现死锁了会怎样，正常情况下，mysql 会检查出死锁，并回滚某一个事务，让另一个事务正常运行。
Mysql 会回滚副作用小的事务，判定的标准是执行的时间以及影响的范围。
1.如何知道系统有没有发生过死锁，如何去查看发生过的锁

show status like 'innodb_row_lock%'



Innodb_row_lock_current_waits：当前正在等待锁的数量；
Innodb_row_lock_time ：锁定的总时间长度，单位 ms；
Innodb_row_lock_time_avg ：每次等待所花平均时间；
Innodb_row_lock_time_max：从系统启动到现在等待最长的一次所花的时间；
Innodb_row_lock_waits ：从系统启动到现在总共等待的次数。

平均时间和锁等待次数比较大的话，说明可能会存在锁争用情况
查看 innodb 存储引擎的运行状态

show engine innodb status


通过这个命令显示的内容比较多，其中有一项 lasted detected deadlock 显示最近发生的死锁。以及加了什么锁。
查看已开启事务的状态

select * from information_schema.innodb_trx;

trx_id：innodb 存储引擎内部事务唯一的事务 id。
trx_state：当前事务的状态。
trx_started：事务开始的时间。
trx_requested_lock_id：等待事务的锁 id，如 trx_state 的状态为 LOCK WAIT，那么该值代表当前事务之前占用锁资源的 id，如果 trx_state 不是 LOCK WAIT 的话，这个值为 null。
trx_wait_started：事务等待开始的时间。
trx_weight：事务的权重，反映了一个事务修改和锁住的行数。在 innodb 的存储引擎中，当发生死锁需要回滚时，innodb 存储引擎会选择该值最小的事务进行回滚。
trx_mysql_thread_id：正在运行的 mysql 中的线程 id，show full processlist 显示的记录中的 thread_id。
trx_query：事务运行的 sql 语句
完结~