	事务1	事务2
1	mysql> begin; Query OK, 0 rows affected (0.00 sec)	mysql> begin; Query OK, 0 rows affected (0.00 sec)
2	mysql> insert into tt(b) values(5); Query OK, 1 row affected (0.04 sec)
3		mysql> insert into tt(b) values(6); Query OK, 1 row affected (0.04 sec)
4	commit;	commit;

T1	T2
begin；	begin
delete from tt where b = 3;//ok, 0 rows affected
	delete from tt where b = 3; //ok, 0 rows affected
insert into tt(b) values( 3);//wating,被阻塞
	insert into tt(b) values( 3);//wating,被阻塞 //ERROR 1213 (40001): Deadlock found when trying to get lock;
T1执行完成， 1 rows affected

	事务1	事务2
1	mysql> begin; Query OK, 0 rows affected (0.00 sec)	mysql> begin; Query OK, 0 rows affected (0.00 sec)
2	mysql> insert into tt(b) values(5); Query OK, 1 row affected (0.04 sec)
3		mysql> select * from tt where b >4 and b <8 lock in share mode;锁等待
4	commit;
		±—±-----+ \| a \| b \| ±—±-----+ \| 12 \| 5 \| ±—±-----+ 1 row in set (6.90 sec)commit;

	事务1	事务2	事务3
1	mysql> begin; Query OK, 0 rows affected (0.00 sec)	mysql> begin; Query OK, 0 rows affected (0.00 sec)	mysql> begin; Query OK, 0 rows affected (0.00 sec)
2	mysql> insert into tt(b) values(5); Query OK, 1 row affected (0.04 sec)
3		mysql> insert into tt(b) values(5);锁等待	mysql> insert into tt(b) values(5);锁等待
4	mysql> rollback; Query OK, 0 rows affected (0.00 sec)
		mysql> insert into tt(b) values(5); Query OK, 1 row affected (37.17 sec)	mysql> insert into tt(b) values(5); ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction死锁发生了

mysql innodb 读加锁,Mysql InnoDB加锁分析

2021-04-19 04:40:59

在文章的开始，简单思考一个小问题：假如有一个SQL语句delete from T where id = 1，这条SQL在InnoDB中执行的时候数据库如何加锁的？数据库的锁要回答上面的问题，首先我们要知道数据库的锁到底是什么。首先InnoDB...


 在文章的开始，简单思考一个小问题：假如有一个SQL语句delete from T where id = 1，这条SQL在InnoDB中执行的时候数据库如何加锁的？
 数据库的锁
 要回答上面的问题，首先我们要知道数据库的锁到底是什么。首先InnoDB数据库的锁级别有表级锁，页级锁，行级锁。
 X锁与S锁
 对于行级锁来说有两种：排它锁X、共享锁S。这两种锁的兼容性如下：
 锁
 X
 S
 X
 不兼容
 不兼容
 S
 不兼容
 兼容
 可以看到假如一个事务获取了某行数据的X锁，则其它事务就不能获取该行数据的X、S锁了，必须等待前面一个事务释放X锁才可以获取自己想要的锁。但是如果一个事务获取了某行数据的S锁，则其它事务是可以获取该数据的S锁的，但是不能获取X锁。也就是说S锁只与S锁兼容。X锁与任何锁都不兼容。
 IX锁与IS锁
 数据库为了实现不同粒度的锁的共存，引入了意向锁的概念。意向锁是表级锁，解释为事务在当前表的记录上所需要的锁(S、X)。相应的意向锁就有意向排它锁、意向共享锁。数据库结构如下：
 
 数据库结构.png
 假如有一个事务需要对记录1进行加X锁，则它需要先对页、表、数据库层面加上IX锁。如果遇到不兼容则需要等待其它事务释放锁。意向锁的兼容性如下：
 锁
 X
 S
 IX
 IS
 X
 不兼容
 不兼容
 不兼容
 不兼容
 S
 不兼容
 兼容
 不兼容
 兼容
 IX
 不兼容
 不兼容
 兼容
 兼容
 IS
 不兼容
 兼容
 兼容
 兼容
 可以看出意向锁和意向锁之间是兼容的，这也就解释了为何InnoDB不会有表级别的死锁。S锁除了可以与S锁兼容以外也可以和IS锁兼容。
 锁的算法
 锁的算法分为三种：Record锁、Gap锁、Next-Key锁。
 Record锁：记录锁，即对单个记录上进行加锁。
 Gap锁：间隙锁，对记录与记录之间的间隙进行加锁，但是不锁定记录。
 Next-Key锁：即锁定一个范围，也锁定记录。
 MVCC
 多版本控制(Multi Version Concurrency Control)，将每一条写操作都记录到undo log中，方便事务读取历史版本的数据。这样的话只有写和写之间才会互不兼容，提升了并发效率。
 当前读 & 快照读
 
 快照读.png
 数据库不同的写事务执行完成后会生成不同的快照，而快照读就是基于这些快照去读取，而并非是数据库记录的实际数值。
 当前读就是每次都获取数据库的最新实际值。不考虑快照。
 Undo log
 在MVCC中，数据库为每一张表都额外添加了几个隐藏字段：
 DB_ROW_ID：数据记录的唯一标识，随着数据的插入而递增。可以简单的把它理解为功能类似主键ID。假如创建的表没有添加主键，则数据库会用该字段创建聚簇索引。
 DB_TRX_ID：最新写操作修改该记录的事务ID。也就是说哪个写事务最后提交，这字段就更新成这个事务的ID。
 DB_ROLL_PTR：回滚指针，指向undo log。
 undo log是存放数据记录历史版本的地方。每次insert/update/delete操作都会生成对应的undo log。不同的是insert的undo log会在当前事务提交后就可以被清除掉。而update的会一直存在，除非记录被delete。delete的undo log会在当前没有快照在使用的时候允许被清除。
 所以一张数据库表的结构如下：
 
 undo log.png
 由上图可以看到一条数据库真实数据记录是有一个指针指向undo log，然后整体是一个链表结构。也就是说历史版本都可以追溯到。
 Read View
 read view主要是来辅助事务进行可见性判断的。作用是判断当前事务可以看到哪些快照。内部重要的参数如下：
 low_limit_no：不需要undo log的事务编号，也就是说所有小于改编号的事务对应的undo log都可以被清除。
 low_limit_id：不应该看到undo log的最小事务ID，也就是说小于该ID的事务undo log均可见。初始化时为当前还未分配的最小事务ID。
 up_limit_id：所有小于该ID的事务undo log均可见。初始化的时候为trx_ids中最小事务ID，如果trx_ids不存在，则up_limit_id = low_limit_id。
 trx_ids：当前read view创建的时候，其它处于未提交状态的事务ID列表，这些事务产生的undo log对当前事务是不可见的。
 creator_trx_id：当前事务ID。
 struct read_view_t{
 ulint type; /*!< VIEW_NORMAL, VIEW_HIGH_GRANULARITY */
 undo_no_t undo_no;/*!< 0 or if type is
 VIEW_HIGH_GRANULARITY
 transaction undo_no when this high-granularity
 consistent read view was created */
 trx_id_t low_limit_no;
 /*!< The view does not need to see the undo
 logs for transactions whose transaction number
 is strictly smaller (
 can be removed in purge if not needed by other
 views */
 trx_id_t low_limit_id;
 /*!< The read should not see any transaction
 with trx id >= this value. In other words,
 this is the "high water mark". */
 trx_id_t up_limit_id;
 /*!< The read should see all trx ids which
 are strictly smaller (
 In other words,
 this is the "low water mark". */
 ulint n_trx_ids;
 /*!< Number of cells in the trx_ids array */
 trx_id_t* trx_ids;/*!< Additional trx ids which the read should
 not see: typically, these are the read-write
 active transactions at the time when the read
 is serialized, except the reading transaction
 itself; the trx ids in this array are in a
 descending order. These trx_ids should be
 between the "low" and "high" water marks,
 that is, up_limit_id and low_limit_id. */
 trx_id_t creator_trx_id;
 /*!< trx id of creating transaction, or
 0 used in purge */
 UT_LIST_NODE_T(read_view_t) view_list;
 /*!< List of read views in trx_sys */
 };
 整体read view的算法可以参考mysql源码，这里就不详细介绍了。
 数据库的隔离级别
 上面简单介绍了一下锁和MVCC机制，下面我们根据不同的数据库隔离级别来分析下文章开头的那个问题。为了举例说明，这边简单创建一张表初始化内容如下：
 CREATE TABLE `test_lock` (
 `id` int(11) NOT NULL AUTO_INCREMENT, // 主键
 `age` int(11) NOT NULL, // 二级索引，允许重复
 `name` varchar(50) NOT NULL, // 无索引
 PRIMARY KEY (`id`),
 KEY `idxAge` (`age`)
 ) ENGINE=InnoDB;
 insert into test_lock(id, age, name) values(1, 20, 'A');
 insert into test_lock(id, age, name) values(2, 20, 'B');
 insert into test_lock(id, age, name) values(3, 18, 'C');
 insert into test_lock(id, age, name) values(4, 22, 'A');
 insert into test_lock(id, age, name) values(5, 17, 'B');
 读未提交 - READ UNCOMMITTED
 数据库最低隔离级别，顾名思义当前事务可以读取到其它事务未提交的更改数据。
 在这个当前隔离级别下数据库不会采用快照读，所有的读操作都是直接读取最新的数据记录。
 条件是主键
 为了方便演示把问题的SQL改写成 delete from test_lock where id = 1，下同
 
 RU_PK.png
 可以看出当执行一条写操作的时候，会锁住主键索引的一条记录(X锁)。
 条件是普通索引非唯一索引
 为了方便演示把问题的SQL改写成 delete from test_lock where age = 20，下同
 
 RU_INDEX.png
 可以看到由于是普通索引，所以会先给查询到的符合条件的辅助索引记录上X锁，然后根据辅助索引找到对应的主键索引，再给主键索引符合条件的记录加上X锁。
 条件无索引
 为了方便演示把问题的SQL改写成 delete from test_lock where name = 'A'，下同
 
 RU_NONE.png
 遇到这种情况数据库会把全表索引记录加锁，可以看到这样开销是很大的。但是mysql对这种操作进行了优化，先锁住全表，再过滤出符合条件的记录，将不符合条件的记录提前释放掉X锁。只保留符合条件的记录锁。
 读已提交 - READ COMMITTED
 当前事务不可读取到其它事务未提交的数据。也就是说假如当前有事务T1正在修改数据A，然后再有一个事务T2读取A则只能读取到A被修改以前的数据。但是如果事务T1提交后，则T2再次读取就可以读到T1提交后的数据了。
 对于此种数据库隔离级别，数据库加锁方式是跟RU是一模一样的。那么怎么区分RU和RC呢，它们最大的区别就是RU读取的是最新记录，而RC采用的就是快照读。也就是说RC采用了MVCC技术。
 可重复读 - REPEATABLE READ
 当前事务不论执行多少次读取操作，读取的结果都是一致的。举例说明：假如事务T1读取数据A，然后再有一个事务T2删除A，这时候T1再次读取还是可以读取到A的。
 条件是主键
 同RC
 条件是普通索引非唯一索引
 
 RR-INDEX.png
 可以看到加锁情况和RC是差不多的。但是多了一些Gap锁。在前面提到Gap和Recod锁结合叫Next-Key锁。这时数据库不仅仅把辅助索引的记录上X锁，而且还会在符合条件的两侧之间加上Gap锁。这样做的目的是为了解决幻读问题。那么是不是说RR这个隔离级别就解决了幻读呢？其实并不是，实际上RR在读操作上是解决了幻读问题，但是对于写操作还是不能解决幻读。
 举个例子：
 T1
 T2
 begin;
 -
 select * from test_lock;
 -
 -
 begin;
 -
 insert into test_lock(age, name) values(25, 'F');
 -
 commit;
 update test_lock set name = 'X';
 -
 commit;
 -
 假如说T1这时候查询出来5条数据，然后再执行update的时候发现实际影响的行数为6。这样就导致了幻读情况。对于幻读可以采用SERIALIZABLE来彻底解决。
 条件无索引
 
 RR_NONE.png
 可以看到数据库给所有的记录都加上X锁和所有的记录中间都有Gap锁。同时Mysql内部也做了优化过滤出符合条件的记录范围后会提前释放X锁和Gap锁。
 可以得出结论RR和RC级别下数据库采用MVCC技术来实现快照读，两者的区别是：在一个事务中RC是每次读操作都会开启一个新的Read view。而RR则是在一个事务中只有第一次读操作才会开启一个新的Read view。
 串行化 - SERIALIZABLE
 在这个隔离级别下类似RR，唯一的不同就是：所有的读操作都会隐式加上lock in share mode。即所有的写操作都加上S锁。这样再根据我们上面的数据库锁的兼容性表，可以知道在上面幻读的例子里T2执行insert操作的时候其实是要等待T1去释放S锁之后才可以操作。这样就解决了幻读。对于文章开头的问题：可以看出串行化对于写操作其实和RR是一样的，数据库加锁方式同RR一样。
 参考
 MySQL版本是基于5.6.24。
 MySQL技术内幕-InnoDB存储引擎

收起

展开全文

MySQL加锁分析

2021-01-26 12:25:59

公司的MySQL隔离级别是Read Commited，已经没有了gap lock，而且代码里的sql都再简单不过，没有显式加锁的sql语句。因此抽出时间看了一下原因。分析具体问题之前，先整体的了解一下MySQL的加锁逻辑，之后再分析...

前言

最近遇到一次MySQL死锁的问题，也算是少见的一件事情。公司的MySQL隔离级别是Read Commited，已经没有了gap lock，而且代码里的sql都再简单不过，没有显式加锁的sql语句。因此抽出时间看了一下原因。
分析具体问题之前，先整体的了解一下MySQL的加锁逻辑，之后再分析起来就游刃有余了：

MySQL的锁

为什么MySQL要加锁呢？OLTP数据库离不开事务，事务也离不开并发操作下一致性的问题。现代数据库解决事务的并发控制有两种办法，2PL和MVCC[1]。
2PL是加锁方案的代表，就是将数据操作分为加锁和解锁两个阶段，任何数据操作都会将访问对象加上锁，后续对这个对象的数据操作就会被阻塞直到锁释放（事务提交）。传统数据库大都是用2PL来实现并发控制的。
MVCC(多版本并发控制)是无锁方案的代表，通过对数据库每一次变更记录版本快照，实现读-写互不阻塞，写-写是否阻塞取决于具体实现（例如postgres的SERIALIZABLE级别下写-写互不阻塞，发生冲突抛出异常）。
对于MySQL(innoDB)来说，是通过MVCC实现读-写并发控制，又是通过2PL写-写并发控制的，因此依然保留着(悲观)锁这个概念，既然有悲观锁，自然就有可能产生死锁问题。
MySQL的事务我之前在这篇文章里做过一些粗浅的理解：传送门（痛心的是网上大部分资料还是显示mySql在RR隔离级别下会幻读。。）

那么MySQL会如何加锁呢[2]：

MySQL锁的模式：

共享/排它锁 (S锁/X锁) (Shared and Exclusive Locks)
- S锁与X锁冲突，S锁与S锁不冲突，X锁和X锁冲突
  - 锁冲突意味着无法获取锁的事务需要等待，锁被释放后才能继续。当然也有可能等待超时或检测出死锁
- 快照读(普通select …)不加锁
- select..lock in share mode / Serializable下的select 会加S锁
- select..for update / 写操作(insert update delete) 会加X锁
- 上述的锁都是行级别的，S锁和X锁同样可以加在表级别上，对应的语句分别是LOCK TABLE … READ和LOCK TABLE … WRITE
意向锁 (IS锁/IX锁) (Intention Locks)
- 意向锁是表级别的锁，用来标识该表上面有数据被锁住（或即将被锁）
- 一个事务在获取（任何一行/或者全表）S锁之前，一定会先在所在的表上加IS锁。同理，获取X锁之前一定会加上IX锁。
- 意向锁提出的目的，就是要标识这个表上面有锁，这样一来，对于表级别锁的请求（LOCK TABLE …），就可以直接判断是否有锁冲突，而不需要逐行检查锁的状态了。从更大的角度来看，意向锁就是为了实现不同粒度的锁共存，每次加锁都需要先对上面更粗粒度的数据结构加意向锁，用来表达“这个数据结构中存在被锁住的数据”。

其兼容矩阵如下（+表示兼容，-表示冲突）：

\	IS	IX	S	X
IS	+	+	+	–
IX	+	+	–	–
S	+	–	+	–
X	–	–	–	–

上面提到的锁的模式，指的是如何锁住数据，各种模式之间是否兼容；下面提到的锁的类型，定义的是具体锁在哪里。二者并不冲突，比如record lock可以分成record x lock和record s lock。

MySQL锁的类型：

Record Locks
- 对单条索引记录上加的锁。准确的说，锁是加在索引上的而非行上。因为innodb一定会有一个聚簇索引，因此最终的行锁都会落到聚簇索引上。
- 可以加在聚簇索引或者二级索引上。
Gap Locks
- gap lock是对索引间隙加的锁，可以是在一条索引记录之前，也可以在一条索引记录之后。
- gap lock的唯一作用，就是阻止其他事务向锁住的gap里插入数据。
- gap lock下的所有锁的模式都是兼容的，比如同一个位置的gap s lock和gap x lock是可以共存的。其作用也是完全相同的。
- 在READ COMMITTED隔离级别下，不会使用gap lock。因此下文关于gap lock的加锁，对于RC隔离级别可以自动忽略。
Next-Key Locks
- Next-Key lock与record lock加锁的粒度一样，都是加在一条索引记录上的。一个next-key lock=对应的索引记录的record lock+该索引前面的间隙的gap lock
- 虽然说Next-Key Lock代表着record lock+前一个间隙的gap lock，在必要的情况下，最后一条记录后面的gap也有可能作为一条单独的gap lock被锁住[3]。
- 由于锁住的是前面的间隙，所以有些资料也会用左开右闭的区间来表示next-key lock，例如(1,3]
Insert Intention Locks
- Insert Intention Lock是一种特殊的间隙锁，执行insert之前会向插入的间隙加上Insert Intention Lock
- Insert Intention Lock与已有的gap lock冲突，因此gap lock锁住的间隙是不能插入数据的
- Insert Intention Lock与Insert Intention Lock之间不冲突，因此允许了同时向同一个间隙插入不同主键的数据

其兼容矩阵如下，+表示兼容，-表示冲突：

要加的锁\ 已存在的锁	record lock	gap lock	insert intention lock	next key lock
record lock	–	+	+	–
gap lock	+	+	+	+
insert intention lock	+	–	+	–
next key lock	–	+	+	–

如何查看事务的加锁情况

当存在锁冲突/等待时，比较方便的查看锁冲突的方式：

// innodb_locks记录了所有innodb正在等待的锁，和被等待的锁
select * from information_schema.innodb_locks;

// innodb_lock_waits记录了所有innodb锁的持有和等待关系
select * from information_schema.innodb_lock_waits'

但是上述方式只能看到存在锁冲突的记录，不能看到每个事务实际锁住的记录和范围。因此更通用的办法是，直接打开innodb的锁监控，在控制台查看详细锁状态：
结果如上图，可以看到当前事务id 4579持有着’new_table’表的聚簇索引=3的X锁。事务id 4580正在等待’new_table’表的聚簇索引=3的X锁。

mysql> set global innodb_status_output=ON; // 可选。将监控输出到log_error输出中，15秒刷新一次
mysql> set global innodb_status_output_locks=ON; // 输出的内容包含锁的详细信息

通过show engine innodb status;语句，可以输出每个事务当前持有的锁结果，常见的结果类型解释如下。死锁日志也会记录如下的锁记录，因此可以用同样的方式来读MySQL的死锁日志。

// 表示事务4641对表`sys`.`new_table`持有了IX锁
TABLE LOCK table `sys`.`new_table` trx id 4641 lock mode IX

// space id=38，space id可以唯一确定一张表，表示了锁所在的表
// page no 3，表示锁所在的页号
// index PRIMARY 表示锁位于名为PRIMARY的索引上
// lock_mode X locks rec but not gap 表示x record lock
// 下方的数据表示了被锁定的索引数据，最上面一行代表索引列的十六进制值，在这里表示的就是id=3的数据
RECORD LOCKS space id 38 page no 3 n bits 80 index PRIMARY of table `sys`.`new_table` trx id 4641 lock_mode X locks rec but not gap
Record lock, heap no 4 PHYSICAL RECORD: n_fields 8; compact format; info bits 0
0: len 4; hex 00000003; asc ;;
1: len 6; hex 0000000011e9; asc ;;
2: len 7; hex a70000011b0128; asc (;;
3: len 4; hex 8000012c; asc ,;;
4: len 1; hex 63; asc c;;
5: len 4; hex 80000006; asc ;;
6: len 3; hex 636363; asc ccc;;
7: len 2; hex 3333; asc 33;;

// lock_mode X表示的是next-key lock，即当前记录的record lock+前一个间隙的gap lock
// 这个锁在名为idx1的索引上，对应的索引列的值为100（hex 64对应十进制），对应聚簇索引的值为1
RECORD LOCKS space id 38 page no 5 n bits 80 index idx1 of table `sys`.`new_table` trx id 4643 lock_mode X
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 00000064; asc d;;
1: len 4; hex 00000001; asc ;;

// lock_mode X locks gap before rec表示的是对应索引记录前一个间隙的gap lock
RECORD LOCKS space id 38 page no 5 n bits 80 index idx1 of table `sys`.`new_table` trx id 4643 lock_mode X locks gap before rec
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 800000c8; asc ;;
1: len 4; hex 00000002; asc ;;

不同语句的加锁情况

以下实验数据基于MySQL 5.7。
假设已知一张表my_table，id列为主键。

id	name	num
1	aaa	100
5	bbb	200
8	bbb	300
10	ccc	400

对该表进行读写操作，可能产生的加锁情况如下（仅考虑隔离级别为RR和RC）：

1. 查询命中聚簇索引（主键索引）

1.1 如果是精确查询，那么会在命中的索引上加record lock。
例如：

// 在id=1的聚簇索引上加X锁
update my_table set name='a' where id=1;

// 在id=1的聚簇索引上加S锁
select * from my_table where id=1 lock in share mode;

1.2 如果是范围查询，那么

1.2.1 在RC隔离级别下，会在所有命中的行的聚簇索引上加record locks（只锁行）

// 在id=8和10的聚簇索引上加X锁
update my_table set name='a' where id>7;

// 在id=1的聚簇索引上加X锁
update my_table set name='a' where id<=1;

1.2.2 在RR隔离级别下，会在所有命中的行的聚簇索引上加next-key locks（锁住行和间隙）。最后命中的索引的后一条记录，也会被加上next-key lock。

// 在id=8、10(、+∞)的聚簇索引上加X锁
// 在(5,8)(8,10)(10,+∞)加gap lock
update my_table set name='a' where id>7;
// 在id=1、5的聚簇索引上加X锁
// 在(-∞,1)(1,5)加gap lock
update my_table set name='a' where id<=1;

1.3 如果查询结果为空，那么

1.2.1 在RC隔离级别下，什么也不会锁
1.2.2 在RR隔离级别下，会锁住查询目标所在的间隙。

// 在(1,5)加gap lock
update my_table set name='a' where id=2;

2. 查询命中唯一索引

假设上述表中，num列加了唯一索引
2.1 如果是精确查询，那么会在命中的唯一索引，和对应的聚簇索引上加record lock。

// 在num=100的唯一索引上加X锁
// 并在id=1的聚簇索引上加X锁
update my_table set name='a' where num=100;

2.2 如果是范围查询，那么

2.2.1 在RC隔离级别下，会在所有命中的唯一索引和聚簇索引上加record lock。同2.1
2.2.2 在RR隔离级别下，会在所有命中的行的唯一索引上加next-key locks。最后命中的索引的后一条记录，也会被加上next-key lock。

// 在num=100和num=200的唯一索引上加X锁
// 并在id=1和id=5的聚簇索引上加X锁
// 并在唯一索引的间隙(-∞,100)(100,200)加gap lock
update my_table set name='a' where num<150;

2.3 如果查询结果为空，同1.3。唯一差别在于，此时加的gap lock是位于唯一索引上的。

3. 查询命中二级索引（非唯一索引）

假设上述表中，name列加了普通二级索引，num列没有索引
3.1 如果是精确查询，那么

3.1.1 在RC隔离级别下，同2.1，对命中的二级索引和聚簇索引加record lock

// 在name='bbb'的两条索引记录上加X锁
// 并在id=5和id=8的聚簇索引上加X锁
update my_table set num=10 where name='bbb';

3.1.2 在RR隔离级别下，会在命中的二级索引上加next-key lock，最后命中的索引的后面的间隙会加上gap lock。对应的聚簇索引上加record lock。

// 在name='bbb'的两条索引记录上加X锁
// 并在id=5和id=8的聚簇索引上加X锁
// 并在二级索引的间隙('aaa','bbb')('bbb','bbb')('bbb','ccc')加gap lock
update my_table set num=10 where name='bbb';

3.2 范围查询、模糊查询的情况比较复杂，此处不详述。可以用上述方法自己实验。

4. 查询没有命中索引

假设上述表中，name列加了普通二级索引，num列没有索引
4.1 如果查询条件没有命中索引

4.1.1 在RC隔离级别下，对命中的数据的聚簇索引加X锁。根据MySQL官方手册[4]，对于update和delete操作，RC只会锁住真正执行了写操作的记录，这是因为尽管innodb会锁住所有记录，MySQL Server层会进行过滤并把不符合条件的锁当即释放掉[5]。同时对于UPDATE语句，如果出现了锁冲突（要加锁的记录上已经有锁），innodb不会立即锁等待，而是执行semi-consistent read：返回改数据上一次提交的快照版本，供MySQL Server层判断是否命中，如果命中了才会交给innodb锁等待。因此加锁情况可以这样来认为：

// 在id=5的聚簇索引上加X锁
update my_table set num=1 where num=200;

// 先在id=1,5,8,10（全表所有记录）的聚簇索引上加X锁
// 然后马上释放id=1,8,10的锁，只保留id=5的锁
delete from my_table where num=200;

4.1.2 在RR隔离级别下，事情就很糟糕了，对全表的所有聚簇索引数据加next-key lock

// 在id=1,5,8,10（全表所有记录）的聚簇索引上加X锁
// 并在聚簇索引的所有间隙(-∞,1)(1,5)(5,8)(8,10)(10,+∞)加gap lock
update my_table set num=100 where num=200;

// 尽管name列有索引，但是like '%%'查询不使用索引，因此此时也是锁住所有聚簇索引，情况和上面一模一样
update my_table set num=100 where name like '%b%';

5. 对索引键值有修改

假设上述表中，num列加了二级索引
如果一条update语句，对索引键值有修改，那么修改前后的数据如何加锁呢。这点要结合数据多版本的可见性来考虑：无论是聚簇索引，还是二级索引，只要其键值更新，就会产生新版本。将老版本数据deleted bti设置为1；同时插入新版本[6]。因此可以认为，一次索引键值的修改实际上操作了两条索引数据：原索引和修改后的新索引。
从innodb的事务的角度来看，如果一个事务操作（写）了一条数据，那么这条数据一定要加锁。因此可以认为，如果修改了索引键值，那么修改前和修改后的索引都会加锁。另外，由于修改的数据并没有被作为查询条件，那么也不会有“不可重复读”和“幻读”的问题，因此无需加gap lock，索引修改只会加X record lock。

示例（RC和RR级别效果一样）：

// 在id=1的聚簇索引上加X锁
// 并在name='aaa'（name列索引原键值）和name='eee'（新键值）的索引上加锁
update my_table set name='eee' where id=1;

6. 插入数据

假设上述表中，num列加了二级索引
insert加锁过程：

唯一索引冲突检查：表中一定有至少一个唯一索引，那么首先会做唯一索引的冲突检查。innodb检查唯一索引冲突的方式是，对目标的索引项加S锁（因为不能依赖快照读，需要一个彻底的当前读），读到数据则唯一索引冲突，返回异常，否则检查通过。
对插入的间隙加上插入意向锁(Insert Intention Lock)
对插入记录的所有索引项加X锁

示例：

// 先对id=15加S锁
// 再对间隙id(10,+∞)和name('ccc',+∞)加Insert Intention Lock
// 然后在id=15的聚簇索引上加X锁（S锁升级为X锁）
// 并在name='fff'的索引上加X锁
insert into my_table (`id`, `name`, `num`) values ('15', 'fff', '800');

还有一个有趣的问题，如果插入的二级索引键值已经存在，那么这个插入意向锁会加在哪个间隙中呢？
顾名思义，插入意向锁锁定的间隙一定是将要插入的索引的位置，如果二级索引键值相同，默认会按照聚簇索引的大小来排序（二级索引在存储上其实就是{索引值,主键值}）。例如：

// 插入意向锁加在间隙 ({'aaa',1},{'bbb',5}) 上
insert into my_table (`id`, `name`, `num`) values ('4', 'bbb', '800');

// 插入意向锁加在间隙 ({'bbb',5},{'bbb',8}) 上
insert into my_table (`id`, `name`, `num`) values ('6', 'bbb', '800');

// 插入意向锁加在间隙 ({'bbb',8},{'ccc',10}) 上
insert into my_table (`id`, `name`, `num`) values ('11', 'bbb', '800');

隐式锁

为了降低锁的开销，innodb采用了延迟加锁机制，即隐式锁(implicit lock)[7]。
从数据存储结构上看，每张表的数据都是挂在聚簇索引的B+树下面的叶子节点上（每个节点代表一个page，每个page存放着多行数据）。每行存储的信息项中都会存有一隐藏列事务id。当有事务对这条记录进行修改时，需要先判断该行记录是否有隐式锁（原记录的事务id是否是活动的事务），如果有则为其真正创建锁并等待，否则直接更新数据并写入自己的事务id。
二级索引虽然存储上没有记录事务id，但同样可以存在隐式锁，只不过判断逻辑复杂一些，需要依赖对应的聚簇索引做计算。
当然，隐式锁只是一个实现细节，显示还是隐式加锁并不影响上文对加锁的判断。
另外，聚簇索引每行记录的事务id，还有一个重要作用就是实现MVCC快照读：由于事务id是全局递增的，那么进行快照读的时候，如果数据的事务id小于当前事务id并且不在活跃事务列表内（尚未提交），则直接返回当前行数据。否则需要根据roll pointer（和事务id一样，也在每行的隐藏列中）去查找undo日志。

一个RC隔离级别下的死锁

其实可以看到，RC隔离级别下的加锁已经很少了，用官方文档的话说”greatly reduces the probability of deadlocks”。因此尽管MySQL的默认隔离级别是RR，但是互联网应用更倾向与使用RC来避免死锁+提高并发能力。例如阿里电商的MySQL默认级别就是RC。
尴尬的是，但是我也的的确确碰到了RC的死锁。还是以这个表来举例，假设id为主键，num列无索引。

id	name	num
1	aaa	100
5	bbb	200
8	bbb	300

按以下顺序执行事务：

trx1	trx2
insert into my_table (id, name, num) values (‘16’, ‘rrr’, ‘888’);	-
-	insert into my_table (id, name, num) values (‘17’, ‘ttt’, ‘999’);
delete from sys.my_table where num=300; // waiting	-
-	delete from sys.my_table where num=400; // deadlock

对照上文的加锁逻辑，insert会对聚簇索引加X锁，因此trx1和trx2首先会分别持有id=16和id=17的X锁。
接下来坑爹的事情来了，对于无索引字段，delete操作不会执行semi-consistent read，而是先直接锁住所有数据的聚簇索引（尽管后面会马上释放，但也需要先获取锁）。这样一来，事务1的delete需要锁住所有记录，等待事务2持有的id=17的X锁，而事务2的delete需要等待事务1的id=16的X锁。死锁就产生了。
在这个例子中，如果insert和delete的顺序都颠倒一下，或者delete都变为update，死锁都不会发生。

小结

索引记录的间隙上用来避免幻读。
Select（Serializable隔离级别除外）不会加锁，而是执行快照读。
写操作都会加锁，具体加锁方式取决于隔离级别、索引命中情况以及修改的索引情况。
为了减少锁的范围，避免死锁的发生，应该尽量让查询条件命中索引，而且命中的越精确加锁越少。同时如果能接受RC级别对一致性的破坏，可以将隔离级别调整成RC。

参考资料

[1] 萧美阳, 叶晓俊. 并发控制实现方法的比较研究[J]. 计算机应用研究, 2006, 23(6):19-22.
[2] MySQL 5.7 Reference Manual :: 15.5.1 InnoDB Locking
[3] MySQL 5.7 Reference Manual :: 15.5.4 Phantom Rows
[4] MySQL 5.7 Reference Manual :: 15.5.2.1 Transaction Isolation Levels
[5] MySQL 加锁处理分析
[6] InnoDB多版本(MVCC)实现简要分析
[7] Introduction to Transaction Locks in InnoDB Storage Engine

收起

展开全文

innoDB加锁规则

2020-03-20 16:23:42

锁的类型行锁record lock，锁住的是一行索引记录（读已提交和可重复读隔离级别都...加锁规则：两个原则，两个优化，一个bug（可重复读隔离级别）原则1：加锁的基本单位是next-key lock。注意是前开后闭区间。 ...

锁的类型

行锁record lock，锁住的是一行索引记录（读已提交和可重复读隔离级别都有）
间隙锁gap lock，锁住的是一个开区间（只有可重复读隔离级别才有）
next-key lock = 行锁+间隙锁，左开右闭区间（只有可重复读隔离级别才有）

加锁规则：两个原则，两个优化，一个bug（可重复读隔离级别）

原则1：加锁的基本单位是next-key lock。注意是前开后闭区间。
原则2：查找过程中访问到的对象才会加锁。锁是加在索引上的。
优化1：索引上的等值查询，给唯一索引加锁时，next-key lock会退化成行锁。
优化2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock退化成间隙锁。
一个bug：唯一索引的范围查询会访问到不满足条件的第一个值为止。

读已提交隔离级别

读已提交隔离级别，基本上只用行锁，很少会用间隙锁。而且它有一个优化是：语句执行过程中加上的行锁，在语句执行完成后，就要把“不满足条件的行”上的行锁直接释放了，不需要等到事务提交。

收起

展开全文

静态方法加锁，和非静态方法加锁区别
2019-09-27 02:35:34
静态方法加锁，和非静态方法加锁区别博客分类： JAVA 基础多线程静态方法加锁对象锁和类锁多线程锁今天看了到有意思的题：在静态方法上加锁和非静态方法加锁有什么区别，从而再次引出锁机制的...
收起
多线程实现方法（加锁不加锁）
2020-10-28 18:14:40
线程创建原因在多线程应用程序中，当多个线程共享相同的内存时，如同时访问一个变量时，需要确保每个线程看到...变量是只读的== 则可以不用加锁流程如下 1：锁初始化； static pthread_mutex_t mutex; pthread_mut
收起
多线程 c++ java linux 并发编程
AQS 加锁过程
2021-05-27 20:49:38
// 当前是哪个线程拿到了锁。 private transient Thread exclusiveOwnerThread; // 多个线程争夺一把锁时，竞争失败的线程包装成 Node 节点保存的 AQS 的线程队列中等待着。 static final class Node {...} // 获取...
收起
aqs 原理
MySQL普通索引的加锁
2021-06-06 11:55:57
加锁是如果是多个范围，是分开加了多个锁，每个范围都有锁；（这个可以实践下 id < 20 的情况）主键等值查询，数据存在时，会对该主键索引的值加行锁 X,REC_NOT_GAP；主键等值查询，数据不存在时，会对查询条件...
收起
数据库 mysql sql 索引 java
常见 SQL 语句的加锁分析
千次阅读 2020-03-19 11:13:18
这篇博客将对一些常见的 SQL 语句进行加锁分析，看看我们平时执行的那些 SQL 都会加什么锁。只有对我们所写的 SQL 语句加锁过程了如指掌，才能在遇到死锁问题时倒推出是什么锁导致的问题。在前面的博客中我们已经...
收起
mysql 死锁
Kafka Producer 源码赏析，尤其是分段加锁、写时复制
2021-06-03 21:36:30
（2）第二步，对消息进行序列化（3）第三步，使用分区器对消息进行分区分区之后，就知道每条消息要发往哪个分区了，但是在分区之前，要获得集群的元数据才能知道该发送到哪个分区中。（4）第四步：确认消息是否...
收起
Kafka 源码剖析
CurrentHashMap 中 Segment 加锁原理
千次阅读 2017-07-04 11:52:40
CurrentHashMap 的分段加锁 get 操作不加锁，原因是使用volatile static final class HashEntry { final int hash; final K key; volatile V value; volatile HashEntry next;
收起
静态方法上加锁synchronized
2020-01-17 16:31:26
所以代码中哪个线程先执行sychronized关键字的方法,哪个线程就持有这把锁在静态方法上加锁synchronized 表示锁定.class类类级别锁 public class MultiThread { private static int num = 0; /**不加static ...
收起
加锁的各种选择
2011-01-08 21:01:00
inux 内核提供了不少有力的加锁原语能够用来使内核避免被自己绊倒. 但是, 如同我们已见到的, 一个加锁机制的设计和实现不是没有缺陷. 常常对于旗标和自旋锁没有选择; 它们可能是唯一的方法来正确地完成工作. 然而, ...
收起
数据结构算法 struct 测试
MySQL 加锁处理分析
2018-08-23 15:14:22
目录背景 MVCC：Snapshot Read vs Current Read Cluster Index：聚簇索引 2PL：Two-Phase Locking ...一条简单SQL的加锁实现分析组合一：id主键+RC 组合二：id唯一索引+RC 组合三：id非唯一索引+RC 组合...
收起
oracle加锁for update
千次阅读 2012-08-24 15:31:23
of 的使用主要是针对多表关联的时候，如果不使用of，对两个表涉及到的行都将锁住，使用of可以指定锁定哪个表，例如：select a.MOBILE,b.NAME from connector a,student b where a.STU_ID=b.ID and a.MOBILE='...
收起
oracle session 数据库 sql insert
ReadWriteLock读写锁加锁过程
2020-08-16 16:50:27
ReadWriteLock读写锁加锁过程读写锁案例 + 小小总结 //读这篇文章的时候，建议先看一下，我并发专题中的 Lock.lock() 加锁的过程的文章 //我在写读写锁的时候，好多东西，好多理念都在lock()中体现 // 读读串行 // ...
收起
java 多线程
Redis加锁的几种实现
2020-07-07 11:24:32
我们经常在工作中会碰到一些重复请求、并发等问题，而给资源加锁是一种不错的手段。我们今天就整理下使用redis作为分布式锁的几种实现。 redis可以用于几个命令是：INCR、SETNX和SET。 1. 使用INCR加锁这种加锁的...
收起
Java多线程加锁例子
2021-09-15 01:09:41
Java多线程加锁例子一、错误示例二、正确示例一、错误示例 static int a = 0; //多线程同时操作 val a @Test public void test4() throws InterruptedException { Runnable runnable = () -> { ...
收起
java
python怎么加锁_python thread模块是如何给线程上锁的？
2020-11-28 12:14:24
我觉得应该有一个过程，指明我的锁是给哪个进程的吧？代码中已经准备好锁池locks了，应该在创建派生线程的时候，拿出一个锁加到这个线程上，但是我没看出这么一个过程。应该如何来理解？是不是分配了锁对象，例如我...
收起
极品便携文件加锁程序锁
2011-12-14 16:09:06
之所以设置10分那是因为它值！！！不需要安装任何繁复的程序,334K!绝对是珍藏级别的小程序.想锁哪个盘输入两次密码即可!想锁那个程序复制于程序同文件夹下即可加锁. 如不是珍藏级别电脑程序锁,可以留言随意批斗.
收起
MySQL语句加锁分析
2020-03-28 17:41:44
则会对 number 值为 1、3、8 聚簇索引记录以及它们对应的二级索引记录加X型正经记录锁，加锁顺序和上边语句中的加锁顺序类似，都是先对一条聚簇索引记录加锁后，再给对应的二级索引记录加锁。之后会继续对number值...
收起
ReentrantLock加锁和释放锁的底层原理
2021-08-15 13:22:47
java并发包下很多API都是基于AQS来实现的加锁和释放锁等功能的，AQS是java并发包的基础类。举个栗子，比如说ReentrantLock、ReentrantReadWriteLock底层都是基于AQS来实现的。 ReentrantLock加锁和释放锁的底层原理 ...
收起
队列 java 多线程并发编程
分析 ArrayBlockingQueue 构造函数加锁问题
2018-10-14 22:34:20
1. 问题昨天有位小伙伴问我一个 ArrayBlockingQueue 中的一个构造函数为何需要加锁，其实这个问题我还真...主要是在看 ArrayBlockingQueue 源码时，觉得它很简单，不就是通过加锁的方式来操作一个数组 items 么，...
收起
mysql InnoDB加锁分析
2019-03-11 17:16:00
还有一个有趣的问题，如果插入的二级索引键值已经存在，那么这个插入意向锁会加在哪个间隙中呢？顾名思义，插入意向锁锁定的间隙一定是将要插入的索引的位置，如果二级索引键值相同，默认会按照聚簇索引的大小...
收起
oracle加锁方式,oracle中加锁与解锁
2021-05-03 10:44:41
oracle中的数据在并发操作时，为了防止错误的发生可以进行记录或者数据库表的加锁操作。当锁操作完成时可以进行解锁操作。数据库中加锁有两种方式，独占模式和共享模式。1.独占模式，不允许其他会话以任何方式共享...
收起
【高并发】面试官：讲讲高并发场景下如何优化加锁方式？
2020-10-15 11:43:31
原来可以这样优化加锁方式！又学到一项新技能！！
收起
高并发性能优化高性能
MySQL不同隔离级别下的加锁情况
2020-02-14 10:18:52
先说明的情况是，无论哪种隔离级别下，如果有索引，加锁都是加在索引上的，只是在不同的隔离级别下，支持的锁粒度不一样而已；锁粒度越小，消耗的资源就越多。如果没有索引，则加锁则是表锁，即锁住聚簇索引中所有行...
收起
5.7. 加锁的各种选择
2012-10-08 00:32:53
Linux 内核提供了不少有力的加锁原语能够用来使内核避免被自己绊倒. 但是, 如同我们已见到的, 一个加锁机制的设计和实现不是没有缺陷. 常常对于旗标和自旋锁没有选择; 它们可能是唯一的方法来正确地完成工作. 然而, ...
收起
数据结构算法 struct 测试

MySQL INNODB是怎么加锁的？

怎么查询？

测试环境

什么是行锁？

什么是间隙锁？为什么需要间隙锁？

读提交隔离级别不解决幻读问题

读提交隔离级别下的binlog必须使用raw格式

可重复读隔离级别解决幻读问题

可重复读怎么加锁？

1、每次加锁的单位都是next-key lock

2、查询过程访问到的叶节点都会加锁

update中的子查询也会加锁

索引范围查询,总会遍历到不满足条件的节点

索引范围查询 desc 向左遍历

3、唯一索引等值查询，存在匹配行，只加行锁。

唯一索引树保证互斥

4、普通索引等值查询，向右遍历最后一个节点，当不满足等值条件的时候，next-key lock 退化为间隙锁。

普通索引树不保证互斥

5、索引等值查询，不存在匹配行，next-key锁优化为间隙锁

6、索引范围查询，不存在匹配行，则对(infimum，minValue]加next-key锁

7、空表第一次加锁都相当于表锁

8、limit缩小锁范围

加锁实战

插入意向锁

概念

插入的过程

场景演示

场景一：两个并发插入到相同gap不同的记录

场景二：插入意向锁和间隙锁冲突

场景三：对插入后的行加X锁

场景四：并发insert发生duplicate-key error导致死锁

锁兼容矩阵

mysql innodb 读加锁,Mysql InnoDB加锁分析

MySQL加锁分析

前言

MySQL的锁

如何查看事务的加锁情况

不同语句的加锁情况

1. 查询命中聚簇索引（主键索引）

2. 查询命中唯一索引

3. 查询命中二级索引（非唯一索引）

4. 查询没有命中索引

5. 对索引键值有修改

6. 插入数据

隐式锁

一个RC隔离级别下的死锁

小结

参考资料

innoDB加锁规则

锁的类型

加锁规则：两个原则，两个优化，一个bug（可重复读隔离级别）

读已提交隔离级别

静态方法加锁，和非静态方法加锁区别

多线程实现方法（加锁 不加锁）

AQS 加锁过程

MySQL普通索引的加锁

常见 SQL 语句的加锁分析

Kafka Producer 源码赏析，尤其是分段加锁、写时复制

CurrentHashMap 中 Segment 加锁原理

静态方法上加锁synchronized

加锁的各种选择

MySQL 加锁处理分析

oracle加锁for update

ReadWriteLock读写锁加锁过程

Redis加锁的几种实现

Java多线程加锁例子

python怎么加锁_python thread模块是如何给线程上锁的？

极品 便携文件加锁 程序锁

MySQL语句加锁分析

ReentrantLock加锁和释放锁的底层原理

分析 ArrayBlockingQueue 构造函数加锁问题

mysql InnoDB加锁分析

oracle加锁方式,oracle中加锁与解锁

【高并发】面试官：讲讲高并发场景下如何优化加锁方式？

MySQL不同隔离级别下的加锁情况

5.7. 加锁的各种选择

哪个是加锁

多线程实现方法（加锁不加锁）

极品便携文件加锁程序锁