Mysql锁、隔离级别与事务MVCC详解

锁

类型分类	描述
读锁（共享锁）	阻塞写：当前session更新会报错，其他session更新会阻塞等待 不阻塞读：任意session都可以读
写锁（排它锁）	读和写都会阻塞：当前session所有操作都没问题，其他session的所有操作会阻塞等待

类型分类	描述
共享锁	（1）其他事务可以对该数据加共享锁，但不能加排他锁 （2）只能读，不能修改 （3）可通过查询语句后加上lock in share mode进行加锁
排它锁	（1）其他事务不能对该数据加任何的锁 （2）能读也能修改 （3）可通过查询语句后加上for update进行加锁

操作粒度分类	描述
行锁	（1）锁住一行数据，开销大，加锁慢，锁冲突概率低，并发度高，会出现死锁 （2） 针对索引字段加锁，不是针对记录，对非索引字段更新会升级为表锁 （3）其它session更新同一条记录会阻塞，更新不同记录不影响
表锁	（1）锁住整张表，开销小，加锁快，冲突概率高，并发度低，不会出现死锁 （2）该表任意更新都会阻塞

• 表锁相关sql

# 通过Name_locked字段查看表是否加锁
mysql> show open tables [FROM db_name] [LIKE 'pattern'];
# 表加读锁
mysql> lock table table_name read;
# 表加写锁
mysql> lock table table_name write;
# 删除表锁
mysql> unlock tables;

• 行锁相关sql

# lock in share mode给id为2的数据加共享锁
select * from user where id = 2 lock in share mode;
# for update给id为2的数据加排它锁
select * from user where id = 2 for update;

• 间隙锁
  在可重复读隔离级别下才会生效

假设有表数据id分别为1，2，3，4，10，11，15
间隙区间有（4,10）、（11,15）、（15，正无穷）三个
当执行sql

update user set name='1' where id>5 and id<13;

区间（4,15）的所有记录都会加上锁，其他session在这个区间也无法新增修改记录
如果是where id>4 and id<16，那区间（11，正无穷）的所有记录也都会加锁间隙锁
上述的（4,15）和（11，正无穷）也叫做临键锁

InnoDB和MyISAM区别

InnoDB	MyISAM
支持事务	——
支持行锁（给）和表锁	只支持表锁
查询（非串行）不会加锁	查询给所有涉及表加读锁
更新加行锁	更新给所有涉及表加写锁
索引和数据都放在ibd文件 聚集索引包含所有数据，查询不回表 二级索引放主键的值，sql没有覆盖索引查询需要回表	索引放在myi文件（存的索引和数据文件地址），数据放在myd文件，查询需要回表

• 锁分析

# 查看锁的情况
show status like 'innodb_row_lock%';

字段	含义
Innodb_row_lock_current_waits	当前正在等待锁定的数量
Innodb_row_lock_time	从系统启动到现在锁定总时间长度
Innodb_row_lock_time_avg	每次等待所花平均时间
Innodb_row_lock_time_max	从系统启动到现在等待最长的一次所花时间
Innodb_row_lock_waits	系统启动后到现在总共等待的次数
如果时间过长，次数过多，需要查看

我们可以构造一个环境，开启两个窗口，开始两个事物（begin命令不是事物的起点，在执行第一个修改操作语句时事物才真正的启动，才会向mysql申请事物id）,执行下面语句

mysql> begin;
mysql> select * from user where id=1 for update;

第二个执行的窗口会阻塞等待，这时我们来查看一下

• 查看事务

mysql> select * from INFORMATION_SCHEMA.INNODB_TRX;

可以看见有几个等待的锁，进程号是多少（可以强制kill掉），还可以看到等待锁的sql是哪个

• 查看锁

mysql> select * from INFORMATION_SCHEMA.INNODB_LOCKS;
# mysql8版本的放在performance_schema.data_locks中
mysql> select * from performance_schema.data_locks;

可查看到锁类型，锁数据量

• 查看锁等待

mysql> select * from INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
# mysql8版本的放在performance_schema.data_lock_waits中
mysql> select * from performance_schema.data_lock_waits;

• 查看锁等待详细信息

mysql> show engine innodb status\G;

• 释放锁

# 释放锁，trx_mysql_thread_id可以从INNODB_TRX表里查看到是70
mysql> kill trx_mysql_thread_id

• 死锁

多数情况mysql可以自动检测死锁并回滚产生死锁的那个事务，有些情况没法自动检测，可以通过

mysql> show engine innodb status\G;

查询死锁日志，手动kill调事物

隔离级别

• 查看隔离级别

mysql> show variables like 'transaction_isolation';

• 设置隔离级别

# 设置为不可重复读
mysql> set transaction_isolation='REPEATABLE-READ';

隔离级别	描述
读未提交 (READ-UNCOMMITTED)	事务A能读到事务B未提交的事务
读已提交 (READ-COMMITTED)	事务A能读到事务B已提交的事务
可重复读（默认） (REPEATABLE-READ)	事务A用相同查询语句，不同时间查询数据都是一样的 通过MVCC机制来实现，有一个版本号的概念，查询语句不会更新版本号，所以多次查询结果一样（insert、update和delete会更新版本号）
可串行化 (SERIALIZABLE)	事务A查询的时候会给查询行加上行锁，如果是范围查询，会给相应的间隙区间都加上锁，所以事务B在范围内插入的时候会阻塞，等待事务A释放锁，避免了A查询到B的新增数据，从而产生幻读

• 并发问题

问题	描述
脏读	一个事务A读到了另一个事务B已经修改但是未提交的数据，如果事务B回滚，事务A读的数据就会不一致
不可重复读	事务A在用相同查询语句查询的时候，结果不一致，不符合隔离性
幻读	事务A用相同查询语句查询时，读到了事务B已提交的新数据，不符合隔离性

• 隔离级别解决问题

隔离级别	脏读	不可重复读	幻读
读未提交	未解决	未解决	未解决
读已提交	已解决	未解决	未解决
可重复读	已解决	已解决	未解决
可串行化	已解决	已解决	已解决

MVCC机制

MVCC全称Multi-Version Concurrency Control（多版本并发控制）

在读已提交和可重复读隔离级别下都实现了MVCC机制

• undo日志版本链

对于一行数据，undo日志会存储一行数据，还要加上两个字段trx_id(事务id)和roll_pointer(回滚指针)
如user表的一行数据

id	name	update_time	trx_id	roll_pointer
1	a	2021-07-23 11:11:18	100	地址
每次事务修改以后，都会生成一条新的undo日志（事务id是依次增加的，但是在undo版本链里不一定是连续的指向，有可能后开启的事务先提交了），例如，修改name为b
id	name	update_time	trx_id	roll_pointer
----	------	----	—	—
1	b	2021-07-23 11:11:18	101	事务id为100的数据的地址
trx_id记录事务的id，roll_pointer里面记录的是上一次事务生成的undo日志的地址
相当于是一个单向链表，通过roll_pointer字段进行关联，没次新的事务修改以后，新的undo日志插入在链表的头结点，指向原来的头结点
注：对应删除操作，也会生成一个新的undo日志，只是会在头信息（record header）里的（deleted_flag）标记位写上true，表示已经删除，查询时候不返回数据

• read view（一致性视图）机制

• read-view组成
  read-view：（[所有未提交事务的id数组]，已创建的最大事务id）
  例如：[100,101,105],200
  其中100,101,105是未提交事务的id，200是当前已创建的最大事务的id
  注：数组里最小的事务100为min_id，已创建的最大事务id为max_id
• read-view生成

可重复读	读已提交
每个事务开启，执行任何查询sql都会生成当前事务的read-view，直到事务结束也不会变化	每个查询sql都会重新生成read-view

• read-view对比规则
  每次查询的时候，我们获取到undo日志版本链的第一条数据的trx_id，然后拿到read-view中进行对比

对比	结果
trx_id < min_id	表示已提交的事务，可见，返回数据
trx_id > max_id	表示未来启动的事务，不可见，继续对比undo日志版本链下一条数据
min_id <= trx_id <= max_id	在read-view的未提交事务数组里，不可见，继续对比undo日志下一条（如果是当前事务id，可见，返回）
	不在read-view的未提交事务数组里，表示已经提交事务，可见，返回数据