一条sql既更新又删除mysql_MySQL InnoDB 锁 学习笔记

所在文集：数据库

本文的内容参考了：

下面会涉及到 MySQL 数据库隔离级别和索引，请先参见：

自增锁

MySQL InnoDB 默认的隔离级别为 RR，假设有数据表：

t(id AUTO_INCREMENT, name);

数据表中有数据：

1, shenjian

2, zhangsan

3, lisi

事务 A 先执行，还未提交：insert into t(name) values(xxx);

事务 B 后执行：insert into t(name) values(ooo);

问：事务B会不会被阻塞？ 答案是会阻塞，分析如下：

事务 A 先执行 insert，会得到一条 (4, xxx) 的记录，由于是自增列，InnoDB 会自动增长，注意此时事务并未提交；

事务 B 后执行 insert，假设不会被阻塞，那会得到一条 (5, ooo) 的记录；

此时，并未有什么不妥，但如果，事务 A 继续 insert：insert into t(name) values(xxoo);会得到一条 (6, xxoo) 的记录。

事务 A 再 select：select * from t where id>3; 得到的结果是：

4, xxx

6, xxoo

注意：不可能查询到 (5, ooo) 的记录，因为在 RR 的隔离级别下，不可能读取到还未提交事务生成的数据。

这对于事务 A 来说，就很奇怪了，对于 AUTO_INCREMENT 的列，连续插入了两条记录，一条是 4，接下来一条变成了 6，就像莫名其妙的幻影。

自增锁是一种特殊的表级别锁(table-level lock)，专门针对事务插入 AUTO_INCREMENT 类型的列。最简单的情况，如果一个事务正在往表中插入记录，所有其他事务的插入必须等待，以便第一个事务插入的行，是连续的主键值。

共享/排他锁(Shared and Exclusive Locks)

共享锁(S锁)和排他锁(X锁)是行级别的锁(row-level locking)：

事务拿到某一行记录的共享S锁，才可以读取这一行；

多个事务可以拿到一把共享S锁，读读可以并行

事务拿到某一行记录的排它X锁，才可以修改或者删除这一行；

只有一个事务可以拿到排它X锁，写写/读写必须互斥；

共享/排它锁的潜在问题是，不能充分的并行，解决思路是数据多版本。参见：MySQL InnoDB 并发控制，事务的实现 学习笔记

意向锁(Intention Locks)

意向锁是指，未来的某个时刻，事务可能要加共享/排它锁了，先提前声明一个意向。意向锁有这样一些特点：

首先，意向锁，是一个表级别的