MySQL 中的锁

banjming
636 阅读8分钟

这是我参与8月更文挑战的第3天，活动详情查看：8月更文挑战

MySql 可重复读隔离级别 MVCC 快照读存在幻读读问题?

  • 加锁 是解决幻读最好方式

MySQL 锁 分类在这里插入图片描述

读写锁和意向锁

读锁

共享锁 (S 锁）

顾名思义， 持有共享锁的多个进程可以同时进入保护空间，因为可以共享锁定的资源， 通常在读取数据前加锁，以实现多个对数据读取进程可以相互并发执行不被阻塞，因此常被称为读锁。虽然叫共享锁，但是实际上，innoDB 通过 MVCC 机制实现了无需加锁即可避免读写冲突，在可重复读读级别下，普通读取是不加锁的。但 select ... lock in share mode 会在行加共享锁。

排他锁（X锁）

排他锁与共享锁不一样， 一旦加了排他锁，其他任何加锁的请求都会被阻塞，排他锁通常是数据写之前加锁，便于各个写操作之间保持互斥，因此也叫做 “写锁”。

意向锁

只有读写锁可以实现读写过程中的锁定问题， 考虑一个场景，一个事务通过 select ... lock in share mode 对某一行加共享锁，此时另外一个事务对这一行要加排他锁，那么这个事务会阻塞等待。但是如果另外一个事务是要给全表加排他锁，那么就要遍历表所有的记录，查看每一条记录的加锁状态，才能决定是否能够成功加锁，这效率是很低的。

解决办法很简单，就是当我们需要对某一行进行加锁时， 将整个表标记为 ”某些行已经加了锁“对状态，那么另外一个事务对整个表加锁操作，就不需要遍历每一行了，

意向锁，指的就是标记“某些行已经加了共享锁”，所有的共享锁加锁前，都要对表加一意向共享锁，加排他锁之前，对表加意向排他锁，意向锁不互斥。

意向锁和排他锁的加锁时机

  • S 锁前先加 IS 锁
  • X 锁前先加 IX 锁

全局锁

如何进行全库备份

当我们要备份一个银行系统数据库，当我们完成 A 账户备份后，此时从 A 账户转一笔资金到 B 账户刚好发生，如果此时我们正好备份 B 账户，会发现 B 账户多出一笔钱，必须要在备份的时候，账户不能再写入了，静态备份才最安全。

解决办法
  • 事务-建立- 一致性视图 mysqldump - single -transaction
  • 全局锁 数据库实例
  • 设置全局读，set global readonly=true
方案选择
  • myISAM 不支持事务
  • 全局只读一旦设置永久生效，如果忘记还原配置，存在安全隐患
  • 有写数据库是图通过判断 readonly 配置来判断当前时主库还是从库， 更改配置造成无法预期的后果
全局锁的加锁& 解锁
flush tables with read lock // 加锁
unlock tables // 解锁

如其他会话对表加了表锁，那么另外一个会话加全局锁的请求会被阻塞等待锁释放，如果当前会话对某个表加了表锁，或者当前再事务中，那么加全局锁的请求会失败。

一旦全局锁加锁成功，会关闭当前已经打开的所有表，此后，该实例会变成只读，所有对数据库的 update, insert ,delete，加排他锁，表结构修改操作都会被阻塞。

当链接断开时，全局锁自动释放。

表级锁

分类

  1. 表锁
  2. 元数据锁 -- MDL (meta data lock)

表锁

lock tables <tablename> read -- 表级别共享锁
lock tables <tablename> write -- 加表级别排他锁
unlock tables <tablename> -- 解锁

MDL 锁

为保证 DDL 语句与增删改查语句执行的院子性，不需要显示使用， 任何语句的执行都与 MDL 排他锁互斥，因此 DDL 操作过程中可能造成加锁时间过长。

MySQL 5.6 对表结构修改流程进行了优化

  1. 获取 MDL 排他锁 -- 防止其他语句正在执行中
  2. 降级成 MDL 共享锁 -- 防止加锁后影响数据的读取（“降级”包含了加共享锁与解排它锁）
  3. 执行 DDL
  4. 获取 MDL 排他锁 -- 最终完成阶段需要保证没有语句在执行
  5. 释放 MDL 共享锁
  6. 释放 MDL 排它锁

MDL 锁存在的问题

  1. 一个语句在执行（如慢查询，长事务）
  2. DDL 语句 加 MDL 排他锁阻塞等等。
  3. 此后所有增删改查，事务开启操作都会陷入阻塞。

解决方案

增加超市参数或者干脆变成非阻塞：

ALTER TABLE table_name NOWAIT add column ... -- 非阻塞式， 一旦获取不到 MDL 排他锁就直接返回
ALTER TABLE table_name WAIT N add column .... -- 最大超时 N 秒

行级锁

Innodb 引擎实现了行级锁，与只支持表级别锁 MyISAM 相比，能够减少锁冲突 用好行级别锁， 减少锁冲突， 可以有效提升 MYSQL 的执行性能。

  • Innodb 行级锁的加锁是自动进行的，我们可以通过某些 SQL 语句触发， 但是不能自由加解锁。
  • 如果在事务中某些行或区间被加锁，那么只有事务结束时， 才会自动进行解锁。
  • innodb 通过 MVCC 实现了在可重复读事务隔离级别下不加锁实现快照读的机制，所有行级别锁都不会影响到其他事务的快照读

行级别锁的分类

  • 记录锁 - - 锁定某行
  • 间隙锁 - - 锁定某个区间
  • 临键锁 - - 锁定左开右闭的一段区间

加锁场景

  1. select ... lock in share mode 共享锁
  2. select ... for update 排他锁
  3. insert 排他锁
  4. update 排他锁
  5. delete 排他锁

记录锁

记录锁时针对某写行进行加锁，防止改行被其他操作修改或者删除。 对于不存在的记录，InnoDB 同样允许对其进行加锁，存储引擎首先创建一个隐藏的聚集所有，然后将其记录为锁定状态。

select * from test where dix_filed = 2
select * from test where dix_field in (2,3,4)

间隙锁 （gap lock)

记录锁锁定的事若干条记录，间隙锁规则锁的是若干索引间的间隙。每个间隙都是两端开放的区间间隙锁定存在的目的是为了防止在事务执行过程中，另一个事务对间隙的插入，能够避免幻读发生，在读已提交与读未提交隔离级别下， InnoDB 会自动禁用间隙锁。

间隙锁不互斥

多个事务可以同时对同一个间隙加锁，及时加的都是排他锁。 考虑另一种常见情况，事务 1 持有间隙锁 (1, 3)，事务 2 持有间隙锁 (3, 5)，此时将记录 3 删除，那么事务 1 与事务 2 持有的间隙锁都将变成 (1, 5)，如果强制间隙锁的互斥，那么这种情况下就会产生错误

临键锁

特殊的间隙锁，他的区间是前开后闭的

加锁场景
  1. 通过对主键或惟一键进行范围查询，会加大于查询范围前开后闭最小范围的临键锁 a. 例如主键有 1, 2, 3, 4, 5 五个取值，查询 id >= 2 and id <= 4，会加 (1, 4]
  2. 通过非主键或惟一键查询，会锁定对应索引记录及其之前的间隙
  3. 如果没有建立索引，那么在查询过程中实际上扫描的是全表，所以最终会锁全表，不过对于 select * from test where xxx limit 1 这样的语句来说，实际扫描在首次遇到匹配行即结束，所以会锁此行前所有间隙

死锁

场景

  1. 事务 A 中，执行 update test set k = k + 1 where id = 1; 会锁定 id 为 1 的记录
  2. 事务 B 中，执行 update test set k = k + 2 where id = 2; 会锁定 id 为 2 的记录
  3. 如果在事务 A 中执行 update test set k = k + 3 where id = 2; 同时在事务 B 中执行 update test set k = k + 4 where id = 1; 就会发生死锁

解决方案

  1. 设置超时,配置 innodb_lock_wait_timeout，默认为 50，即一个加锁请求在等待 50 秒后会自动返回加锁失败
存在的问题：
  1. 只支持秒级粒度
  2. 设置过小容易误伤

主动死锁检测

设置 innodb_deadlock_detect 为 on， innodb 会自动扫描，判断是否该语句会引起死锁，如果会，那么立即返回错误

存在的问题：
  • 需要遍历所有持有锁和等待锁的事务之间每两个是否存在死锁，时间复杂度过高，如果有 1000 个并发的线程同时更新同一行，主动死锁检测需要进行 100 万次对比，CPU 飙高

降低单条记录并发度

高并发场景下，拆分字段降低单条记录并发度 例如页面 UV 记录场景下，将一个页面的 UV 拆分为多行，每次增加 UV 都随机选择一行执行

  • 存在的问题 只适用于单调递增场景
avatar
文章
阅读
粉丝