mysql 锁相关

最新推荐文章于 2023-04-15 22:09:16 发布

凉兮～

最新推荐文章于 2023-04-15 22:09:16 发布

阅读量98

点赞数

分类专栏：数据库

本文链接：https://blog.csdn.net/qq_39061115/article/details/115153052

版权

数据库专栏收录该内容

5 篇文章 0 订阅

订阅专栏

mysql 数据隔离级别

基础
- 设置全局的隔离级别时，会同步修改当前会话的隔离级别；修改会话隔离级别时，对全局无影响
- 查看当前会话隔离级别
  - SELECT @@tx_isolation;
- 设置当前会话隔离级别
  - set session transaction isolation level repeatable read;
- 查看系统当前隔离级别
  - select @@global.tx_isolation;
- 设计当前系统隔离级别
  - set global transaction isolation level repeatable read;
读未提交
- 在该隔离级别，所有事务都可以看到其他未提交事务的执行结果
- 本隔离级别很少用于实际应用，因为它的性能也不比其他级别好多少。
- 读取未提交的数据，也被称之为脏读（Dirty Read）。
- set session transaction isolation level read uncommitted;
- session 设置为该级别时，即使其他session的隔离级别更高，也能在当前session中读到其他事务提交的数据;
读已提交
- 大多数数据库系统的默认隔离级别如 oracle （但不是MySQL默认的）
- 一个事务只能看见已经提交事务所做的改变
- 会造成一次事务内，读取到的数据不一致，也称为不可重复读
- 一次事务内，可能有另一个事务操作了这组数据并进行了提交
- set session transaction isolation level read committed;
可重读
- MySQL的默认事务隔离级别
- 事务A在读到一条数据之后，此时事务B对该数据进行了修改并提交，那么事务A再读该数据，读到的还是原来的内容
- 通过多版本并发控制实现
- 会出现幻读问题
- set session transaction isolation level repeatable read;
- 影响
  - 查询无影响；一次事务中，读到的数据不会受到其他事务的影响
  - 增/删/改
    - 如果加锁成功，会执行修改
    - 加锁失败，则等待
- 针对同一条数据进行修改，会出现锁问题；先获取到的锁释放后，后续的修改操作才可以被执行
可串行化
- 最高的隔离级别
- 它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题
- 它是在每个读的数据行上加上共享锁。
- 在这个级别，可能导致大量的超时现象和锁竞争。
- 最开始的事务未提交时，其他事务只能查询，不能执行插入修改操作

事务

基础
- 四大特性
  - 原子性事务是数据库的逻辑工作单位，事务中包含的各操作要么都做，要么都不做
  - 一致性事务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态
  - 隔离性一个事务的执行不能其它事务干扰
  - 持久性指一个事务一旦提交，它对数据库中的数据的改变就应该是永久性的
- 启动方式
  - 显示启动事务语句，begin或者start transaction。配套的提交语句是commit，回滚语句为rollback
  - set autocommit=0，这个命令会将这个线程的更新自动提交掉
    - 这意味着如果你只执行一个select语句，这个事务就启动了，并且不会自动提交。
    - 这个事务持续存在知道你主动执行commit或rollback语句，或者链接断开
    - show VARIABLES like ‘autocommit’;
  - 事务加锁只有在commit或者rollback时才会被释放
查询当前有多少事务在运行
- select * from information_schema.innodb_trx;
  - 仅启动事务，如begin 方式；该语句不会查询出内容；
  - 只有开启事务并进行操作之后，才会显示

mysql 锁的实现原理

MySQL 事务隔离其实是依靠锁来实现的
读未提交不加任何锁，所以会有问题
加锁
- MySql只有在RR的隔离级别下才有gap lock和next-key lock
- 加锁规则
  - 规则1:加锁的基本单位是next-key lock(前开后闭区间)
  - 规则2:查找过程中访问到的对象才会加锁
  - 索引上的等值查询，给唯一索引加锁的时候，next-key lock退化为Record Lock（行锁）
  - 索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock退化为Gap Lock（间隙锁）
    - 该间隙锁为值可能所在区间的索引间隙，开区间
  - 索引上的范围查询会访问到不满足条件的第一个值为止
  - 普通索引
    - 等值查询
      - 能查到
        会在对应记录的主键加排他锁
        加记录锁
        加间隙锁
      - 查不到
        加间隙锁
    - 范围查询
      - 能查到
        值在索引区间所有都加x锁
        在查到的值的上加记录锁（每一个值）
      - 查不到
  - 唯一索引上
  - 插入
    - 插入插入意向锁
    - 插入临建锁锁定插入行，以及插入行下一个值之间的区间（等同闭合区间）
锁类型
- 行锁：记录锁，间隙锁，临建锁，行级共享锁，行级排他锁
- 表锁：表记排他锁，表级共享锁，意向排他锁，意向共享锁元数据锁（MDL）
意向锁
- 添加排他锁，共享锁之前要先添加对应意向锁
- 意向锁和行级锁不会产生冲突；
- 用于添加表锁的时候，不用每一行去检查行级锁，提升效率
元数据锁（MDL）
- MDL锁主要作用是维护表元数据的数据一致性
- 支持事务的InnoDB引擎表和不支持事务的MyISAM引擎表，都会出现Metadata Lock Wait等待现象
- 元数据锁是server层的锁，表级锁；
- 解决问题
  - 事务隔离问题，比如在可重复隔离级别下，会话A在2次查询期间，会话B对表结构做了修改，两次查询结果就会不一致，无法满足可重复读的要求；
  - 数据复制的问题，比如会话A执行了多条更新语句期间，另外一个会话B做了表结构变更并且先提交，就会导致slave在重做时，先重做alter，再重做update时就会出现复制错误的现
- 每执行一条DML、DDL语句时都会申请MDL锁，DML操作需要MDL读锁，DDL操作需要MDL写锁（MDL加锁过程是系统自动控制，无法直接干预，读读共享，读写互斥，写写互斥）；
- 申请MDL锁的操作会形成一个队列，队列中写锁获取优先级高于读锁。一旦出现写锁等待，不但当前操作会被阻塞，同时还会阻塞后续该表的所有操作；
  - 一旦出现Metadata Lock Wait等待现象，后续所有对该表的访问都会阻塞在该等待上，导致连接堆积，业务受影响。
- 事务一旦申请到MDL锁后，直到事务执行完才会将锁释放。
  - 这里有种特殊情况如果事务中包含DDL操作，mysql会在DDL操作语句执行前，隐式提交commit，以保证该DDL语句操作作为一个单独的事务存在，同时也保证元数据排他锁的释放
行锁
- 加锁场景
  - select … lock in share mode
  - select … for update
  - insert
  - update
  - delete
  - 除了 select … lock in share mode 是加共享锁外，其他操作均为排它锁
- 记录锁
  - 行锁在 InnoDB 中是基于索引实现的
  - 即使该表上没有任何索引，那么innodb会在后台创建一个隐藏的聚集主键索引，那么锁住的就是这个隐藏的聚集主键索引。
  - 记录锁兼容间隙锁和插入意向锁
- 间隙锁；
  - 区间锁, 仅仅锁住一个索引区间（左开右闭合）。
  - 在索引记录之间的间隙中加锁，或者是在某一条索引记录之前或者之后加锁，并不包括该索引记录本身。比如在 1、2、3中，间隙锁的可能值有 (∞, 1]，(1, 2]，(2, ∞]
  - 间隙锁可用于防止幻读，保证索引间的不会被插入数据
  - 间隙锁可以重复加锁
  - 当事务持有间隙锁，其他事务可以对该段数据添加除插入意向锁意外的其他锁
  - 索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock退化为Gap Lock（间隙锁）
    - 该间隙锁为值可能所在区间的索引间隙，开区间
- 临键锁
  - record lock + gap lock, 左开右闭区间。
  - 默认情况下，innodb使用的锁为临建锁
  - 就是将键及其两边的的间隙加锁（向左扫描扫到第一个比给定参数小的值，向右扫描扫描到第一个比给定参数大的值，然后以此为界，构建一个区间）
  - 事务持有临键锁时，当前范围内的数据，只能获取间隙锁，不能再获取其他锁
- 插入意向锁
  - 插入意向锁是一种Gap锁，不是意向锁，在insert操作时产生。
    - 仅存在唯一索引时，会降级为记录锁
  - 多事务持有同一间隙，不会等待；只会检测数据行是否冲突
  - 用于提高并发插入的性能
  - 事务持有插入意向锁时，不会对其他锁（包括插入意向锁）产生影响
表锁；
- 自增锁
  - 发生涉及AUTO_INCREMENT列的事务性插入操作时产生
间隙锁；
- 间隙锁基于非唯一索引，它锁定一段范围内的索引记录

在这里插入图片描述

参考文档

多并发版本控制

MVCC在MySQL InnoDB中的实现主要是为了提高数据库并发性能，用更好的方式去处理读-写冲突，做到即使有读写冲突时，也能做到不加锁，非阻塞并发读
读取类型
- 当前读
  - 像select lock in share mode(共享锁), select for update ; update, insert ,delete(排他锁)这些操作都是一种当前读，为什么叫当前读？就是它读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。
- 快照读
  - 像不加锁的select操作就是快照读，即不加锁的非阻塞读；
  - 快照读的前提是隔离级别不是串行级别，串行级别下的快照读会退化成当前读；
  - 之所以出现快照读的情况，是基于提高并发性能的考虑;
  - 快照读的实现是基于多版本并发控制，即MVCC,可以认为MVCC是行锁的一个变种，但它在很多情况下，避免了加锁操作，降低了开销；既然是基于多版本，即快照读可能读到的并不一定是数据的最新版本，而有可能是之前的历史版本
- 实现原理
  - 依赖记录中的三个隐藏字段（创建时间戳（事务号），删除时间戳，回滚日志地址），undo日志，Read View 来实现的
    - 数据的创建会把当前事务号写入插入时间戳
    - 每条数据的更新会以标记删除，在删除时间戳写上当前事务号；
    - 新插入一条数据，插入时间戳写上当前事务号；
    - 每条数据存在多个版本。
  - 查询
    - 只查事务号比当前事务号小，删除时间比当前时间大的数据；
    - 插入时间比当前时间大或删除时间比当前时间小的数据对当前事务不可见。确保只查数据库已提交事务数据。
InnoDB在每行记录后面保存两个隐藏的列来，分别保存了这个行的创建时间和行的删除时间。这里存储的并不是实际的时间值,而是系统版本号，当数据被修改时，版本号加1
在读取事务开始时，系统会给当前读事务一个版本号，事务会读取版本号<=当前版本号的数据
此时如果其他写事务修改了这条数据，那么这条数据的版本号就会加1，从而比当前读事务的版本号高，读事务自然而然的就读不到更新后数据了
注意
1. 在一个事务中，使用当前读（加锁读增删改）和使用快照读（不加锁）；读取到的数据会不一致
参考 https://www.jianshu.com/p/8845ddca3b23

命令行

变量

查看变量
- SHOW GLOBAL VARIABLES LIKE ‘%innodb_status%’;

锁状态详解

开启事务锁监控
- SET GLOBAL innodb_status_output=ON;
- SET GLOBAL innodb_status_output_locks=ON;
查看引擎状态
- SHOW ENGINE INNODB STATUS\G
介绍
- 会介绍当前事务的读视图范围
  - 如Trx read view will not see trx with id >= 2321, sees < 2321
- 表上加的锁
  - TABLE LOCK table world.test trx id 2323 lock mode IX
- 锁类型
  - 临建锁
    - RECORD LOCKS space id 32 page no 4 n bits 72 index PRIMARY of table world.test trx id 2323 lock_mode X
      - 主键加锁临键锁
    - 锁定的行数
      - 对主键加锁
        形如 Record lock, heap no 6 PHYSICAL RECORD:
        0: len 4; hex 80000001; asc ;; 第一行显示加锁的记录注解如此为1；
        十六进制 0: len 4; hex 80000001; asc ;;代表10
        该记录显示的顺序为记录的插入顺序；同堆结构；会出现 id大的值出现在id小的前面（指定主键插入场景
- 间隙锁
  - RECORD LOCKS space id 32 page no 5 n bits 72 index cc of table world.test trx id 2328 lock_mode X locks gap before rec
  - 会显示间隙右区间（主键 1，5，6，10；普通索引查询5）
    - 0: len 4; hex 80000006; asc ;; 代表锁定区间的最右边为6
- 记录锁
  - RECORD LOCKS space id 32 page no 4 n bits 80 index PRIMARY of table world.test trx id 2329 lock_mode X locks rec but not gap
  - 会显示锁定的行（查询5）
    - 0: len 4; hex 80000005; asc ;; 锁定5

凉兮～

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql 锁相关

mysql 数据隔离级别基础设置全局的隔离级别时，会同步修改当前会话的隔离级别；修改会话隔离级别时，对全局无影响查看当前会话隔离级别SELECT @@tx_isolation;设置当前会话隔离级别set session transaction isolation level repeatable read;查看系统当前隔离级别select @@global.tx_isolation;设计当前系统隔离级别set global transaction isolati
复制链接

扫一扫