mysql事务-锁_select innodb trx

接之前的事务系列

mysql事务-innodb中的redolog详解

innodb中的undolog 详解

mysql事务-MVCC

通过前面几篇文章，我们知道，并发产生的事务，基本上会有写-写，读-写或写-读，也是由于隔离级别的不同，可能会导致脏读、幻读等问题。

在这篇（mysql事务-MVCC）文章中，我们了解到读操作通过多版本并发控制（MVCC）来解决不一致的问题。

写操作可以通过加锁来解决。

先简单的梳理下并发时，更新记录时的加锁机制。

到此，我们挂起。

加锁 带来的问题

• 脏读

• 脏页：缓冲池已修改，还未刷新到磁盘；

• 脏数据 事务未commit时；

• 脏读 读到未提交的数据

• 不可重复读，一个事务内多次读取同一数据集合，另一个事务提交了

• 丢失更新，电商购物，用户取消和发货同时进行，发货未看最新状态

• 阻塞 锁释放过慢会导致

共享锁(Shared Lock) ：

• InnoDB存储引擎；

• 读锁(S锁)；

• 其他事务可以读，但不可以写；

• 共享，别人获取了S锁，他人是可以再获取S锁，但是不能再获取该记录的X锁

• 获取S锁 select … lock in share mode;

独占锁（Exclusive Lock）

• InnoDB存储引擎；

• 写锁（X锁）；

• 排他，其他事务不可以读，也不可以写；

• 对于update、delete、insert语句，InnoDB自动给数据集加X锁；

• 对于select语句获取x锁， select … for update，这样只有自己才能更新数据，其他事务无法更新;

• 优先级比读锁高；

元数据锁（Metadata lock）

msyql使用DML来管理对数据库对象的并发访问，并确保数据一致性。在5.5.3以前，当一个会话在主库执行DML操作还没有提交时，另一个会话对同一个对象执行了DDL操作，如DROP，mysql的binglog是基于事务提交的先后顺序进行记录的，因此在slave上就出现了先drop，再insert的情况。

解决以下问题：

• 事务隔离问题，dml操作后，数据结果不一致，无法满足重复读的需求；

• 数据复制问题（binlog同步）

常见DML锁场景：

• 当前有执行DML操作（DML未执行完成）时，执行DDL操作；

• 当前有对标长时间查询或使用mysqldump、mysqlpump时，执行DDL会堵住；

• 显示或者隐式开启事务后未提交或回滚，比如查询完成后未提交或者回滚，DDL会被堵住；

• 表上有失败的查询事务，比如查询不存在的列，语句失败返回，但是事务没有提交，此时DDL仍然会被堵住；

悲观锁

假定会发生冲突，屏蔽一些可能违反数据完整性的操作。

• 每次拿数据，都会认为别人会修改；

• 所以每次拿数据都会上锁（行锁、表锁等）

乐观锁

假设不会发生并发冲突，在提交操作时检查是否违反了数据完整性。

• 不能解决脏读（通过隔离级别可以）；

• 每次拿数据，认为别人不会改，所以不上锁；

• 更新时判断一下在此期间别人有没有更新这个数据；

• 适用于多读的应用；

锁比较

表锁

• 开销小，加锁快；

• 不会出现死锁

• 力度大，并发冲突概率高；

• 并发度低；

• 适用于读多，写少的场景

行锁

• 开销大，加锁慢；

• 锁逐步获取，有死锁的可能性；

• 力度小、并发冲突小；

• 并发度高；

• 适用于并发更新量少的场景；

页面锁

• BDB存储引擎

• 介于表锁和行锁之间

• 有出现死锁的可能性；

• 并发度一般；

表锁

表共享锁（S锁）

• MyISAM、MEMORY、MERGE 这些存储引擎

• 读锁（S锁）

• 其他事务可以读，但不可以写；

• 共享

• low-priority-updates 参数设置锁优先级

• 自动加锁

表独占锁（X锁）

• MyISAM、MEMORY、MERGE 这些存储引擎

• 写锁（X锁）

• 排他

• 开销小，加锁快

• 自动加锁

意向共享锁（IS）

• InnoDB存储引擎

• 给数据行加共享锁，必须先获取该锁

• 隐式锁（InnoDB会根据隔离级别帮我们做，不需要我们关心）

意向独占锁（IX）

• InnoDB存储引擎

• 给数据行加独占锁，必须先获取该锁

• 隐式锁（InnoDB会根据隔离级别帮我们做，不需要我们关心）

行锁

• InnoDB给索引项加锁实现行锁（必须用索引查）；

• 执行计划里用了索引才算（有时候索引会不起作用）；

间隙锁（GAP）

• 条件范围检索数据，对这个范围进行加锁，可能有些数据不连贯，产生了间隙，这些间隙也会加锁；

• 适用于批量插入，锁定一段空间，主键自增；

• 防止幻读；

• 满足恢复和复制的需要；

• 恢复和复制都是要保证顺序的，在这个事务没有完成之前，别的事务是不能动的；

next-key Lock

既能锁住当前记录，又能阻止其他是在在该记录之前的间隙中插入记录。官方名称为 LOCK_ORDINARY 可以理解为一个行锁+一个间隙锁组成。

InnoDB锁的内存结构

type_mode(32 bit)

• lock_mode （低4位） 锁模式

• LOCK_LS: 0 意向共享锁

• LOCK_IX: 1 意向独占锁

• LOCK_S: 2 共享锁

• LOCK_X: 3 独占锁

• LOCK_AUTO_INC: 4 AUTO_INC锁

• lock_type (5~8位) 锁类型

• LOCK_TABLE: 16 占第5bit，标记为1，表示表级锁

• LOCK_REC: 32 占第6bit，标记为1，表示行级锁

• rec_lock_type 行锁的具体类型

• LOCK_ORDINARY: 0 表示next-key锁；

• LOCK_GAP: 512 第10bit为1，表示gap锁；

• LOCK_REC_NOT_GAP: 1024 第11bit为1

• LOCK_INSERT_INTENTION: 2048 第12位bit为1， 表示插入意向锁；

• LOCK_WAIT: 256 第9位bit为1，表示is_waiting为true获取锁失败，为0 表示is_waiting为false，获取锁成功

sql语句加锁分析

普通SELECT语句

在不同的隔离级别下，普通的select语句有不同的状态

• 在READ UNCOMMITTED隔离级别下， 不加锁，直接读最新版本，会出现脏读、幻读和不可重复读；

• 在READ COMMITTED 隔离级别下，不加锁，每次查询都会生成一个ReadView,避免了脏读，但没法避免幻读和不可重复读；

• 在REPEATABLE READ隔离级别下，不加锁，在第一次执行select时生成ReadView, 就避免了脏读、幻读和不可重复读；

带锁的SELECT语句

• UPDATE … 执行的条件 ,内部为select

• DELETE … 执行的条件，内部为select

• select … LOCK IN SHARE MODE;

• select … for update;

事务的一些查询辅助命令

mysql> select * from innodb_trx\G          ### 只显示了当前运行的innodb事务
mysql> select * from innodb_locks\G        ### 直接反映了锁的一些情况
mysql> select * from innodb_lock_waits\G    ### 事务量大时，直观反映当前事务的等待
1
2
3

表结构

CREATE TABLE `t_demo` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `mobile` varchar(11) COLLATE utf8_bin NOT NULL COMMENT '手机号码',
  `name` varchar(255) COLLATE utf8_bin DEFAULT NULL COMMENT '昵称',
  `age` int(4) DEFAULT NULL COMMENT '年龄',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `unq_idx_mobile` (`mobile`),
  KEY `idx_nick_name` (`name`),
  KEY `idx_create_mobile` (`mobile`,`create_time`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;


INSERT INTO `test`.`t_demo` (`id`, `mobile`, `name`, `age`, `create_time`) VALUES (1, '13600000000', 'yxk', 21, '2021-06-29 12:21:02');
INSERT INTO `test`.`t_demo` (`id`, `mobile`, `name`, `age`, `create_time`) VALUES (3, '15600000000', 'yxkong', 20, '2021-07-01 12:21:35');
INSERT INTO `test`.`t_demo` (`id`, `mobile`, `name`, `age`, `create_time`) VALUES (5, '15500000000', 'yxk', 23, '2021-07-01 12:22:20');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

RC= Read Committed

RR = Repeatable Read

S = Seralizable

select * from t_demo where id= 1;
1

在Seralizable隔离级别下:

• 加读锁

• MVCC并发控制降级为Lock_Base CC；

其他隔离级别：不加锁，使用mvcc并发控制；

delete from t_demo where id= 1;
1

id是主键 RC和RR隔离级别 此sql只会在id=1这条记录上加X锁。

delete from t_demo where mobile= '13600000000';
1

mobile是唯一索引 在RC和RR隔离级别下，这条sql会加两把X锁

• 对mobile为’13600000000’的unique索引加X锁；

• 对聚簇索引id=1的记录加X锁；

delete from t_demo where name= 'yxk';
1

nike_name是二级非唯一索引 在RC隔离级别下

• 对name='yxk’的所有记录加X锁

• 以及对应的聚簇索引上加锁；

在RR隔离级别下：

• 定位到第一条满足条件的记录，对该记录加X锁；

• 加上GAP锁，然后加对应聚簇缩影的X锁；

• 返回

• 然后读取下一条，重复操作；

• 直到找到第一个不满足条件的记录，此时不需要加X锁，只需要GAP锁；

delete from t_demo where age= 20;
1

在RC隔离级别下：

• 聚簇索引全表扫描过滤；

• 每条记录都会被加X锁，如果不满足条件会立即释放X锁；

在RR隔离级别下：

• 聚簇索引全表扫描；

• 所有记录都会加X锁；

• 稍有的GAP锁住；

• 杜绝所有的并发更新/删除/插入操作；

delete from t_demo where create_time>'2021-06-30 00:00:00' and create_time<'2021-07-04 00:00:00' and mobile='15500000000' and age=20;
1

在RR隔离级别下：

• 先根据create_time确定好范围

• 针对范围内的数据进行扫描；

• 范围内的数据都会加X锁

• 到看索引的第二条数据，会锁住id 1~3之间的间隙；

• 不符合条件，释放X锁；

• 依次往后执行