《MySQL 实战45讲》–笔记–锁

根据加锁的范围,MySQL 里面的锁大致可以分成全局锁、表级锁和行锁三类。

全局锁

全局锁是对整个数据库实例加锁。MySQL 提供的一个加全局锁的命令: Flush tables with read lock (FTWRL),让整个库处于只读状态。

全局锁的典型使用场景是做全库逻辑备份。

逻辑备份工具 mysqldump 使用参数 -single-transaction 的时候在导数据之前就会启动一个事务,来确保拿到一致性视图。

一致性读的前提是引擎要支持这个隔离级别的事务。

set global readonly=true 也可以让全库进入只读状态。但存在两个风险: readonly 的值可能被用来做其他逻辑,比如判等一个库是主库还是备库;设置 readonly 之后,如果客户端发生异常,则数据库会一直保持 readonly 状态。

在 slave 上,如果用户有超级权限的话,全库只读 readonly=true 是失效的。

FTWRL 执行完后如果客户端异常断开,MySQL 会自动释放这个全局锁,整个库回到可以正常更新的状态。

表级锁

MySQL 表级锁有两种:表锁和元数据锁(meta data lock, MDL)。

表锁的语法是: lock tables t1 read, t2 write; ,可以用 unlock tables 主动释放锁,也可以在客户端断开的时候自动释放。lock tables 语法除了限制其他线程的读写外,也限定了本线程接下来的操作对象,例如前面的语句限定了对 t1 只能进行读、 t2 可以读写,也不能访问其他表。

元数据锁 MDL 是自动加锁、释放锁的,当对一个表做 增删改查 操作的时候加 MDL 读锁,当对表结构做变更操作的时候,加 MDL 写锁。

事务中的 MDL 锁,在语句执行开始时申请,但是语句结束后并不会马上释放,而会等到整个事务提交后再释放。

MySQL online DDL 的过程:
1. 获取 MDL 写锁
2. 降级成 MDL 读锁
3. 真正做 DDL
4. 升级成 MDL 写锁
5. 释放 MDL 写锁

第3步占了 DDL 绝大部分时间,在这期间这个表可以正常读写数据,因此称为 online 。

上面步骤 1与2、4与5 之所以需要是因为 读锁与写锁、 写锁与写锁 都是互斥的,为了防止同一时间有两个 DDL 同时进行所必须的。
把写锁降级为读锁是为了让增删改查可以同时进行。4与5是因为还要做一些表结构修改。

行锁

两阶段锁协议:在 InnoDB 事务中,行锁是在需要的时候才加上去的,直到事务结束(提交或回滚)才释放。

如果事务中需要锁多个行,把最可能造成锁冲突、影响并发度的锁尽量往后放。(越往后,离释放锁的时间就越短,这样就缩短了持有关键锁的时间长度。)

出现死锁以后,有两种策略:

    1. 直接进入等待直到超时。超时时间可以通过参数 innodb_lock_wait_timeout 来设置,InnoDB 默认值是 50 秒。
    1. 发起死锁检测,发现死锁后,主动回滚死锁链条中的某一个事务,让其他事务得以继续进行。参数 innodb_deadlock_detect=on 表示开启这个逻辑。

死锁检测的复杂度:每个新来的被堵住的线程,都要判等会不会由于自己的加入导致了死锁,这个时间复杂度是 O(n) 。

避免死锁检测的思路:

  1. 临时关闭死锁检测;
  2. 控制并发度:可以把对同一个行锁的访问限制在一个很小的并发度,从而使检测成本很低。
    • 客户端控制:如果有多个客户端,效果不好;
    • 在中间件实现;
    • 在 MySQL 里面实现,对于相同行的更新,在进入引擎之前排队。
  3. 在业务逻辑上将一行改成多行来减少锁冲突。比如 JDK 的累加器 LongAddr ,内部维护了多个槽作为计数器。

InnoDB 的行级锁是通过索引加载的,即行锁是加在索引响应的行上的,如果对应的 update 语句没有走索引,则会加表锁。
对索引扫描得到的每一行加锁,在事务结束时释放。

CREATE TABLE `geek` (
  `a` int(11) NOT NULL,
  `b` int(11) NOT NULL,
  `c` int(11) NOT NULL,
  `d` int(11) NOT NULL,
  PRIMARY KEY (`a`,`b`),
  KEY `c` (`c`),
  KEY `ca` (`c`,`a`),
  KEY `cb` (`c`,`b`)
) ENGINE=InnoDB;

insert into geek (a, b, c, d) values (0, 1, 2, 3);
insert into geek (a, b, c, d) values (1, 2, 3, 4);

分别开两个命令行客户端,在第一个客户端执行下面到 for update 语句,

begin;
select * from geek where a = 0 and b = 1 for update;

然后在第2个客户端执行

select * from geek where a = 1 and b = 2 for update;

会发现立即就执行完了,说明主键为 (1, 2) 的记录并没有被第一个客户端锁住。

在第二个客户端继续执行sql

select * from geek where a = 0 and b = 1 for update;

会发现被阻塞,

在第一个客户端执行 commit 后会看到第二个客户端也马上执行完成,说明第一个客户端锁住的记录是在事务提交的时候释放的。

对于全表查询,即使表中只有很少的记录,也会加表锁,会导致插入、更新操作阻塞,如下:

虽然只更新一条记录,仍然会加表锁,导致其他更新阻塞或超时:

发表评论

电子邮件地址不会被公开。 必填项已用*标注

此站点使用Akismet来减少垃圾评论。了解我们如何处理您的评论数据