Mysql系列_高级_04_Mysql锁机制

锁分类

• 从性能上分为乐观锁(用版本对比来实现)和悲观锁
• 从对数据库操作的类型分，分为读锁和写锁(都属于悲观锁)
  ①读锁（共享锁，S锁(Shared)）：针对同一份数据，多个读操作可以同时进行而不会互相影响
  ②写锁（排它锁，X锁(eXclusive)）：当前写操作没有完成前，它会阻断其他写锁和读锁
• 从对数据操作的粒度分，分为表锁和行锁

表锁

每次操作锁住整张表。开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低； 一般用在整表数据迁移的场景。

基本操作

#手动增加表锁：lock table 表名称 read(write),表名称2 read(write);
lock table t1 
#查看表上加过的锁
show open tables;
#删除表锁
unlock tables;

行锁

本质
每次操作锁住一行数据。开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度最高。

注意点：

• InnoDB支持事务和行级锁，但是MYISAM不支持
• InnoDB的行锁是针对索引加的锁，不是针对记录加的锁。并且该索引不能失效，否则都会从行锁升级为表锁
• 如果对非索引字段更新，行锁可能会变表锁
• 锁定某一行还可以用lock in share mode(共享锁) 和for update(排它锁)

行锁分析

#通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况
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:系统启动后到现在总共等待的次数

锁查看管理
事务A执行示例

事务B执行示例，事务A比事务B先执行

# 查看锁
select * from INFORMATION_SCHEMA.INNODB_LOCKS; --mysql8.0版本前
select * from PERFORMANCE_SCHEMA.DATA_LOCKS; --mysql8.0版本后

# 查看锁等待
select * from INFORMATION_SCHEMA.INNODB_LOCK_WAITS; --mysql8.0版本前
select * from PERFORMANCE_SCHEMA.DATA_LOCK_WAITS; --mysql8.0版本后

通过查看锁和锁等待可以发现等待锁的事务ID为 148568 和占用锁的事务ID为148567，此时，可以通过show engine innodb status\G;(在cmd下执行，Navicat下执行会报错)来查看对应锁执行的sql，进行优化sql，如果事务十分紧急又复杂，可以通过查看事务信息，然后kill事务来解决锁占用

# 查看事务ID trx_mysql_thread_id
select * from INFORMATION_SCHEMA.INNODB_TRX;
kill trx_mysql_thread_id

间隙锁(Gap Lock)与临键锁(Next-key Locks)

间隙锁是Mysql用于锁两个值之间的间隙的锁。
临键锁是行锁与间隙锁的组合。

对于以上示例表，共有id字段三个间隙(2,9)，(9,15)，(15,正无穷大)，如果事务A执行update t2 set a=5 where id>5 and id<13，那么id在(2, 15]的记录都会被加上锁，其他事务不能在这个区间中做删除和插入操作，此时(3,20]的整个区间可以叫做临键锁，是由行锁和间隙锁组合而成的锁。

锁优化

• 尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁
• 合理设计索引，尽量缩小锁的范围
• 尽可能减少检索条件范围，避免间隙锁
• 尽量控制事务大小，减少锁定资源量和时间长度，涉及事务加锁的sql尽量放在事务最后执行
• 尽可能低级别事务隔离

MVCC原理

MVCC本质
MVCC是Mysql隔离性选择读已提交和可重复读采取的多版本并发控制机制

undo日志版本链
undo日志版本链是指一行数据被多个事务依次修改过后，在每个事务修改完后，Mysql会保留修改前的数据undo回滚日志，并且用两个隐藏字段trx_id和roll_pointer把这些undo日志串联起来形成一个历史记录版本链

示例sql执行：

对应生产undo日志版本链：

详解：在可重复读隔离级别，当事务开启，执行任何查询sql时会生成当前事务的一致性视图read-view，该视图在事务结束 之前都不会变化(如果是读已提交隔离级别在每次执行查询sql时都会重新生成)，这个视图由执行查询时所有未提交事 务id数组（数组里最小的id为min_id）和已创建的最大事务id（max_id）组成，事务里的任何sql查询结果需要从对应 版本链里的最新数据开始逐条跟read-view做比对从而得到最终的快照结果。

版本链比对规则：
①如果 row 的 trx_id 落在绿色部分( trx_id<min_id )，表示这个版本是已提交的事务生成的，这个数据是可见的；
② 如果 row 的 trx_id 落在红色部分( trx_id>max_id )，表示这个版本是由将来启动的事务生成的，是不可见的(若 row 的 trx_id 就是当前自己的事务是可见的）；
③如果 row 的 trx_id 落在黄色部分(min_id <=trx_id<= max_id)，那就包括两种情况：

• 若 row 的 trx_id 在视图数组中，表示这个版本是由还没提交的事务生成的，不可见(若 row 的 trx_id 就是当前自 己的事务是可见的)；
• 若 row 的 trx_id 不在视图数组中，表示这个版本是已经提交了的事务生成的，可见。

对于删除的情况可以认为是update的特殊情况，会将版本链上最新的数据复制一份，然后将trx_id修改成删除操作的 trx_id，同时在该条记录的头信息（record header）里的（deleted_flag）标记位写上true，来表示当前记录已经被 删除，在查询时按照上面的规则查到对应的记录如果delete_flag标记位为true，意味着记录已被删除，则不返回数据。