MySQL阅读笔记——17.锁

17.1 锁介绍以及基本类型

多个未提交事务对数据改动，通过锁排队执行，防止 脏写，在事务中修改记录时，先看内存中是否存在相关联的 锁结构 如果没有则创建一个与修改记录相关联的 锁结构 （InnoDB引擎一切操作都是事务）

解决 脏读、不可重复读、幻读 有两种方案：

  1. 读操作利用MVCC+写操作加锁：通过ReadView找到符合版本记录，查询语句 只能读取到ReadView之前的记录已提交的记录修改，ReadView之前未未提交或者之后才开启的事务是看不到的，修改语句 是针对最新版本，读历史版本和修改最新版不冲突

  2. 读、写加锁：不允许读旧版本记录，每次都要读取到最新的记录则要对读写操作都加锁，防止幻读可以加 间隙锁

MVCC也称为快照度，不会对表中任何记录加锁

采用MVCC只是通过 版本链 控制可见性，读写不冲突；而通过 加锁 读写需要排队执行，特殊业务情况下需要加锁执行，如：一个事务需要读取旧值递增，需要获取最新值，如果前一个事务在读取旧值之后，递增之前，另外一个事务修改了该旧值，如果前一个事务不可见（可重复读隔离级别下）当前一个事务提交时会出现不一致现象

MySQL中锁分类

  1. 共享锁：Shared Locks，简称S锁，事务读取记录时，先获取S锁。S锁可重入，允许别的事务获取S锁，但是不能获取X锁，如果别的事务获取X锁则会阻塞等待前一个事务 提交 或者 回滚

    # 为读取记录加上S锁，加锁后是当前读，并不会遵循MVCC,其他事务做过修改立即可见
    # 如果查询是覆盖索引，则S锁只会锁覆盖索引不会锁定聚集索引（主键索引）
    select ... lock in share mode;

  2. 独占锁：Exclusive Locks，简称X锁，事务改动记录时，先获取X锁，X锁是排他锁，别的事务再获取X锁会阻塞，直到前一个事务 提交 或者 回滚

    # 为读取记录加上X锁，加锁后是当前读，并不会遵循MVCC,其他事务做过修改立即可见
    # 即使使用了覆盖缩影也会连同聚集索引（主键一起加X锁）
    select ... for update;

X锁和S锁又可分为表级和行级别，为表加S锁，则表中记录或者表不能有X锁；为表加X锁，表中记录和表不能有X锁和S锁，为表中记录行加X锁或者S锁的时候会同时加上表级别 意向锁 （IS：意向S锁、IX：意向X锁，意向锁并没有实际作用，只是为事务加表锁时候作为一个判断依据）MyISAM、MEMORY不支持事务的表引擎只支持表锁，InnoDB同时支持表锁和行锁。

在InnoDB引擎中进行alter table、drop table 等DDL操作时候会在表级别上加上 元数据锁 （历史遗留问题）

# 添加表S锁
lock table <表名> read;
# 添加表X锁
lock table <表名> write;
# 释放锁
unlock tables;

 

MySQL中针对不同的操作，分别进行了不同的加锁处理：

  1. DELETE：定位记录位置，然后加上X锁，执行删除标记过程（delete mark）

  2. UPDATE：

    1. 未修改主键，并且更新列存储空间大小未变，则定位位置，获取X锁后直接修改

    2. 未修改主键，更新列存储空间大小改变，定位位置，获取X锁，删除记录（直接加入垃圾链表），插入新记录（插入记录有 隐式锁）

    3. 修改记录主键，先DELETE，在INSERT

  3. INSERT：通过 隐式锁 保证新插入的记录在提交前不被其他事务访问到

17.2 其他锁类型

表级别自增锁 实现原理（即：用AUTO_INCREMENT修饰的列）：

  1. 采用 AUTO-INC锁：插入记录时AUTO_INCREMENT修饰的列分配递增值，插入过程中其他插入事务阻塞，插入语句完成后释放锁（锁表）

  2. 采用 轻量级锁：生成AUTO_INCREMENT列值时获取轻量级锁，生成后就释放锁，并不等到插入语句完成

通过innodb_autoinc_lock_mode可以设置为自增变量赋值的方式，0则一律采用 AUTO-INC锁，2则一律采用 轻量级锁，1则混合使用。 ​ 通过insert ... select、replace ... select、load data等语句不能确定要插入数据的具体行数会采用 AUTO-INC锁 的方式在插入过程中锁表，如果插入之前能够确定条数则通过 轻量级锁 对自增字段赋值避免锁表。

行间隙锁：

给记录加了间隙锁，则不允许在当前记录id之前和上条记录之间的位置插入记录，如果是最后一条记录则在supermum上加上间隙锁就可以防止在记录末尾之后插入数据，如果向同时给记录加上普通锁（X锁或者S锁）则可以加上 Next-Key Locks

插入意向锁：

一个事务中对某些记录加上 间隙锁，此时另一事物在锁定的间隙中插入数据，会进行阻塞并生成一个锁结构称为 插入意向锁 （LOCK_INSERT_INTENTION），插入意向锁 不会阻塞其他事物继续加 任何类型锁

隐式锁：

事务插入记录时候可以不加锁，但是其他事务进行操作时（加S锁或者X锁）会为新增记录创建一个X锁，然后自己再创建一个锁结构进入等待状态

  1. 插入聚集索引记录，trx_id 记录当前事务id，其他事物如果想加锁，则会查看记录的 trx_id 如果是活跃事务则帮助当前事务创建X锁后，自己进入等待状态

  2. 插入二级索引记录（二级索引没有 trx_id 隐藏列），二级索引页面的 Page Header 部分的 PAGE_MAX_TRX_ID 属性记录了对当前页面进行改动的最大事务id，如果此id比当前当前活跃事务最小id小，表名对该页面修改的事务都已经提交，否则就定位到二级索引记录 回表 查询聚集索引隐藏列

17.3 InnoDB锁结构

InnoDB中加锁就是对每一条记录关联一个 锁结构，可以多个记录对应一个 锁结构，但要满足下列条件：

  • 同一事物中的加锁操作

  • 被加锁记录在同一页面

  • 加锁类型相同

  • 等待状态相同

17.4 锁

多个事务互相等待其他事务释放资源，进入死锁状态，MySQL有两种解决策略：

  1. 直接阻塞，等待超时，通过innodb_lock_wait_timeout设置

  2. 死锁检测，主动回滚死锁链某一事务，通过innodb_deadlock_detect设置开启

死锁检测会消耗CPU资源，如果1000个事务修改统一行数据，则会有1000*1000的死锁检测，解决办法：

  1. 确定事务一定不会发生死锁的情况下，临时关闭死锁检测

  2. 服务器端控制并发度

    1. 中间件实现

    2. 修改MySQL源码，相同行更新在进入引擎层之前进行排队

    3. 细化数据粒度，将一行数据改成逻辑上多行，减少所冲突，比如：总金额有一个账户划分为多个账户

《高性能MySQL,第三版》错误：DDL加元数据锁而不是表锁（效果是表锁），行数据隐藏列没有版本号而是roll_point指针指向版本链

MySQL通过间隙锁或者MVCC解决幻读问题，DDL操作会锁表（元数据锁，类似表锁）并会自动提交事务

【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/leon618/p/13783369.html