Tag: InnoDB Lock
image.png

全局锁

Flush tables with read lock（FTWRL）【不推荐使用，使用mysqldump来备份，原理是mvcc】

全局加读锁（全局只读）
用 unlock tables 释放锁
获取到FTWRL的线程， 后续没有sql语句，30秒超时、线程主动退出、执行unlock tables语句，会自动释放这个锁。

FTWRL 与 MVCC

在有MVCC事务支持的引擎下，不需要使用FTWRL。
备份数据启动一个事务就可以了（事务隔离级别：可重复读）

  • mysqldump —single-transaction也是启动一个事务（此选项会将隔离级别设置为：REPEATABLE READ。并且随后再执行一条START TRANSACTION语句）

但MyISAM没有事务支持就要用

FTWRL 和 set global readonly = true

  • set global readonly 可能用了做其他逻辑判断
  • readonly 对super用户权限无效
  • readonly会直接报错，不会阻塞。
  • FTWRL阻塞
  • FTWRL退出会自动释放锁

MySQL全局锁和表锁的深入理解Mysql脚本之家
FTWRL和READONLY的区别 狐狸教程

表级锁

  • 表锁
  • 元数据锁（Meta Data Lock，MDL）

    表锁【lock tables xxx read/write】

    lock tables … read/write
    show open tables 查看锁
    unlock tables 释放锁

lock table 会加MDL读锁。

lock tables和unlock tables，这都是在服务器层（MySQL Server层）实现的，和存储引擎无关
一般支持行锁的情况不需要使用表锁。
innodb也可以使用lock tables

MyISAM 在执行查询语句（SELECT）前，会自动给涉及的表加读锁，在执行更新操作（UPDATE、DELETE、INSERT 等）前，会自动给涉及的表加写锁，这个过程并不需要用户干预，因此，用户一般不需要直接用 LOCK TABLE 命令给 MyISAM 表显式加锁。

innodb 使用表锁的方法

  • UNLOCK TABLES会隐含地提交事务
  • COMMIT 或 ROLLBACK 并不能释放用 LOCK TABLES 加的表级锁 ```sql SET AUTOCOMMIT=0; # 对 InnoDB 表加锁时要注意，要将 AUTOCOMMIT 设为 0，否则MySQL 不会给表加锁； LOCK TABLES t1 WRITE, t2 READ, …; [do something with tables t1 and t2 here]; COMMIT; #不要用 UNLOCK TABLES 释放表锁，因为 UNLOCK TABLES会隐含地提交事务；

UNLOCK TABLES;#COMMIT 或 ROLLBACK 并不能释放用 LOCK TABLES 加的表级锁，必须用UNLOCK TABLES 释放表锁。 ```

元数据锁（Meta Data Lock，MDL）

lock table 会加MDL读锁。
自动添加

  • 增删改查 加 MDL 读锁读锁读锁
  • 修改表结构 加 MDL写锁
    在加MDL写锁后，之后的增删改查也阻塞。
    例如：
  • image.png

myisam 表上更新一行，那么会加MDL读锁和表的写锁；

如何安全的给线上小表加字段？

  • information_schema.innodb_trx表查看当前长事务（kill掉长事务）

对于请求频繁的表

  • alter table 语句设定等待时间（MariaDB有相关命令）

online DDL【online就是不阻塞其他操作，不推荐使用】

image.png
alter 语句在启动的时候需要获取 MDL 写锁，但是这个写锁在真正拷贝数据之前就退化成读锁了。（不会长时间阻塞后面的操作）
MySQL Online DDL 探究 — Everyday

什么操作不支持online DDL？

增加列，增加索引都是可以的。
MySQL :: MySQL 5.7 Reference Manual :: 14.13.1 Online DDL Operations
image.png

DDL的问题【不推荐使用】

image.png
image.png

image.png
原理：

  1. 【原数据】insert select limit 1000 拷贝原数据到新表

image.png
忽略错误（因为增量数据先插入了）

  1. 【增量数据】通过触发器

image.png
replace来确保数据一致性（全量数据转移过来慢，增量数据使用replace，如果有就删除后插入，没有就直接插入）

意向锁

InnoDB内部的表锁：

  • 意向共享锁（IS）
  • 意向排它锁（IX）

innodb在加行锁的时候都会先加相对应的意向锁（表锁）。
MySQL锁总结 - 图10

可以看出意向锁之间不互相影响（都放过让他们去行锁竞争），只影响表锁。

百度上那些文章，没头没尾的来个冲突兼容表格出来，mysql官方文档上给出这个表格，表格上面有这么一句“Table-level lock type compatibility is summarized in the following matrix.”即“表级锁定类型的兼容性总结在下面的矩阵表格中。”，注意，是表级锁定类型，也就是说其中的X，IX，S，IS都是指表级锁类型，不是想当然的X和S就是行级锁定。 InnoDB 的意向锁有什么作用？ - 大王叫我来巡山的回答 - 知乎 https://www.zhihu.com/question/51513268/answer/147733422

也就是说在innodb 行的读写操作在意向锁这个级别，是不互斥的。
互斥的是和lock tables … read/write 加的表锁 互斥。
也就是说在innodb 行的读写操作在意向锁这个级别，是不互斥的。
互斥的是和lock tables … read/write 加的表锁 互斥。
也就是说在innodb 行的读写操作在意向锁这个级别，是不互斥的。
互斥的是和lock tables … read/write 加的表锁 互斥。

为什么要意向锁？直接加表锁不就行了？

理解：意向锁是innodb自己的表锁。意向锁的作用

  1. 阻塞其他加表锁（阻塞：lock tables..write）。
  2. innodb自己的读写操作在表这个级别的锁，不互相阻塞

    START TRANSACTION WITH consistent snapshot【直接建立snapshot】

    start transaction = begin

    你想要达到将 start transaction 作为事务开始的时间点，那么我们必须使用：START TRANSACTION WITH consistent snapshot

SELECT … LOCK IN SHARE MODE和SELECT … FOR UPDATE

普通的innodb读，都是快照读。
快照读不加锁，当前读加读锁
SELECT … LOCK IN SHARE MODE和SELECT … FOR UPDATE 都是当前读，不会阻塞快照读

SELECT … LOCK IN SHARE MODE加的IS锁（意向共享锁）
SELECT … FOR UPDATE 加的IX锁（意向排他锁），更严格，阻塞了select…lock in share mode的查询方式

也就是SHARE MODE、FOR UPDATE不但加了各自的意向锁，还加了行读锁，行写锁。

select * froml where c=8 for update;
image.png

间隙锁【读锁】【可重复读】

间隙锁也是加载某个记录上的。
只对扫描到的所有行加锁，只能保证update安全，不能保证insert安全。所以引入间隙锁。

  • 间隙锁是读锁，防止在间隙中插入新值。
  • 间隙锁在可重复读隔离级别下才会生效。

    间隙锁是用来防止幻读的么？

    幻读是读到新insert的行。
    快照读不会有幻读，当前读才有幻读。
    rr解决幻读分两种
  1. 当前读：通过 间隙锁（next-key） 来解决部分幻读（例如，select for update？解决幻读？）
    1. rr不能完全禁止幻读（先快照读，再当前读）

image.png

  1. 快照读：通过mvcc来解决幻读
    Innodb 中 RR 隔离级别能否防止幻读？ · Issue #42 · Yhzhtk/note

next-key lock

间隙锁 + 行锁（记录锁） = next-key lock
左开右闭区间。
next-key lock是加锁的基本单元。

为什么next-key lock是左开右闭？

image.png

间隙锁会引入死锁的问题【重要】【重要】

会引入死锁：
MySQL锁总结 - 图15
解决死锁问题（不少公司在用）：

  1. 将隔离级别设为读已提交【放弃解决幻读，放弃间隙锁】
  2. 解决数据日志不一致问题：把binlog设置为row。

    避免间隙锁死锁问题解决方法：

    1. 避免更新或者删除不存在的记录，虽然更新存在的记录也会产生间隙锁，但是间隙锁锁住的范围会更小；更新不存在的记录会锁住意想不到的区间范围，极其容易导致死锁问题
    2. 设置为主键唯一索引，这样就不存在间隙锁了。（等值查询+唯一索引=行锁）

间隙锁加锁逻辑

MySQL锁总结 - 图16
等值搜索 唯一索引 退化行锁
等值搜索 非唯一索引 退化间隙锁
不等值搜索 唯一索引 bug，到下一个不满足条件的next key lock
image.png

行锁（记录锁）

innodb有行锁，myisam只有表锁

  • 共享锁（S）
  • 排它锁（X）

InnoDB行锁是通过给索引上的索引项加锁来实现!
只有通过索引条件检索数据，InnoDB 才使用行级锁，否则，全表扫描InnoDB 将使用表锁（应该是多个next key lock连起来相当于表锁）。
虽然多个session是访问不同行的记录， 但是如果是使用相同的索引键， 是会出现锁冲突的

如果使用的是没有索引的字段，比如update class_teacher set teacher_id=7 where class_name=‘初三八班（即使没有匹配到任何数据）’,那么会给全表加入gap锁。（rr） Innodb中的事务隔离级别和锁的关系 - 美团技术团队

update t set t.name=’abc’ where t.name=’cde’; name字段没有索引，所以锁整个表。 但加个limit update t set t.name=’abc’ where t.name=’cde’ limit 1; 就会走主键索引？

order by 和 limit 结合使用，如果where 字段，order by字段都是索引，那么有limit索引会使用order by字段所在的索引（默认主键索引？），没有limit会使用where 条件的索引；

有些情况update即使有索引，但优化器不走索引。可以使用FORCE INDEX。

锁对象

image.png
一个页一个锁对象。锁对象通过bitmap来锁行。

唯一索引duplicate key检查加S锁【会死锁】

MySQL死锁案例_唯一索引 - 简书
对于唯一索引的插入，需要在插入前进行duplicate key的检查，所以需要申请加上S lock，【为什么是s锁？】

  • key已存在获取s锁
  • RC隔离级别

image.png
解决方法：

抱歉，没早点把这么全面的InnoDB锁机制发给你 - MySQL - dbaplus社群

插入意向锁Insert Intention Lock（行锁）【为了提升insert并发能力】

想在某个区间插入锁，单现在处于等待其他gap锁释放。
image.png

  • 不同于上面的意向锁
  • 插入意向锁 需等待其他行锁完成。
  • 插入意向锁互相不阻塞。？
  • 插入意向锁 和gap一样加在区间后面的记录上。

    隐式锁

    image.png
    延迟生成锁结构的用处。相当于偏向锁？

    锁查看

    检查阻塞死锁

死锁与死锁检测

MySQL锁总结 - 图25
死锁后怎么办

  1. 等超时
    通过innodb_lock_wait_timeout来设置（默认50s）
  2. 发起死锁检测（主要），检测到就回滚
    innodb_deadlock_detect 设置为on（默认on）

虽然能避免死锁，但检测死锁也需要消耗cpu。

锁超时两个都不回滚，死锁会回滚其中一个

8.0

for update nowait【报错】

image.png

for update skip locked【跳过】

跳过已经加锁的记录
image.png