全局锁

  1. 全局锁：对整个数据库实例加锁
  2. 加全局读锁：FLUSH TABLES WITH READ LOCK，阻塞其他线程的下列语句
    • 数据更新语句（增删改）
    • 数据定义语句（建表、修改表结构）
    • 更新类事务的提交语句
  3. 主动解锁：UNLOCK TABLES
  4. 典型使用场景：全库逻辑备份
    • 把整库每个表都SELECT出来，然后存成文本
  5. 缺点
    • 如果在主库上执行逻辑备份，备份期间不能执行更新操作，导致业务停摆
    • 如果在备库上执行逻辑备份，备份期间从库不能执行由主库同步过来的binlog，导致主从延时
  6. 备份加全局锁的必要性
    • 保证全局视图是逻辑一致的

mysqldump

  1. --single-transaction
    • 导数据之前启动一个事务，确保拿到_一致性视图_
    • 由于MVCC的支持，在这个过程中是可以正常更新数据的
  2. 需要存储引擎支持_RR的事务隔离级别_
    • MyISAM不支持事务，如果备份过程中有更新，总是能取到最新的数据，破坏了备份的一致性
    • 因此MyISAM只能依赖于FLUSH TABLES WITH READ LOCK，不能使用--single-transaction
  3. 针对全库逻辑备份的场景，--single-transaction只适用于所有的表都使用了事务引擎的库
    • 如果有的表使用了不支持事务的存储引擎，那么只能依赖于FLUSH TABLES WITH READ LOCK
    • 这是MyISAM被InnoDB替代的一个重要原因
  4. 在逻辑备份时，如果全部库都使用InnoDB，建议使用--single-transaction参数，对应用更加友好

逻辑备份 + DDL

在备库用--single-transaction做逻辑备份的过程中，由主库的binlog传来了一个针对小表t1的DDL语句

备份关键语句
1
2
3
4
5
6
7
8
9
10
11
12
13
# 备份过程中的关键语句
Q1:SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Q2:START TRANSACTION WITH CONSISTENT SNAPSHOT;
/* other tables */
Q3:SAVEPOINT sp;
/* 时刻 1 */
Q4:SHOW CREATE TABLE `t1`;
/* 时刻 2 */
Q5:SELECT * FROM `t1`;
/* 时刻 3 */
Q6:ROLLBACK TO SAVEPOINT sp;
/* 时刻 4 */
/* other tables */
  1. 备份开始时，为了确保RR的隔离级别，再设置一次（Q1）
  2. 启动事务，用WITH CONSISTENT SNAPSHOT确保可以得到一个一致性视图（Q2）
  3. 设置一个保存点（Q3）
  4. SHOW CREATE TABLE是为了拿到表结构（Q4）
  5. SELECT * FROM是正式导数据（Q5）
  6. ROLLBACK TO SAVEPOINT的作用是释放t1的MDL锁（Q6）
DDL到达时刻
  1. 时刻1：备份拿到的是DDL后的表结构
    • 现象为无影响
  2. 时刻2：Q5执行时，报异常：Table definition has changed, please retry transaction
    • 现象为mysqldump终止
  3. 在时刻2~时刻3之间（导数据期间）：mysqldump占据着t1的MDL读锁，因此binlog会被阻塞，直到Q6结束
    • 现象为主从延时
  4. 时刻4：mysqldump释放MDL读锁，备份拿到的是DDL前的表结构
    • 现象为无影响

readonly

  1. SET GLOBAL READONLY=true也能让全库进入只读状态，推荐使用FLUSH TABLES WITH READ LOCK
  2. 在有些系统中，readonly的值会被用来做其他逻辑，因此修改global变量的方式影响面会比较大
  3. 异常处理机制不同
    • 执行FLUSH TABLES WITH READ LOCK命令后，客户端发生异常，MySQL会自动释放全局锁
    • 执行SET GLOBAL READONLY=true命令后，客户端发生异常，MySQL会一直保持readonly状态

表级锁

表锁

  1. 表锁：LOCK TABLES ... READ/WRITE
  2. 解锁
    • 主动解锁：UNLOCK TABLES
    • 自动解锁：客户端发生异常，断开连接
  3. LOCK TABLES除了会限制其他线程的读写外，也会限制本线程接下来的操作
    • 线程A执行LOCK TABLES t1 READ, t2 WRITE，在线程A执行UNLOCK TABLES之前
    • 其他线程允许的操作：读t1
    • 线程A允许的操作：读t1，读写t2，同样_不允许写t1_
  4. InnoDB支持行锁，所以一般不使用LOCK TABLES来进行并发控制

元数据锁（MDL）

  1. MDL是隐式使用的，在访问一个表的时候会被自动加上
  2. MDL的作用：保证读写的正确性，从MySQL 5.5引入
    • 防止DDL与DML的并发冲突
  3. MDL读锁 + MDL写锁
    • 对一个表做增删改查操作（DML）的时候，加MDL读锁
    • 对表结构做变更操作（DDL）的时候，加MDL写锁
    • 关系
      • 读锁之间不互斥：多线程可以并发对同一张表进行增删改查
      • 读写锁之间，写锁之间互斥：用于保证变更表结构操作的安全性

加字段的问题

  1. session A先启动，对表t加上一个MDL读锁
  2. session B需要的也是MDL读锁，不互斥，可以正常执行
  3. session C需要的是MDL写锁，session A的事务还未提交，持有的MDL读锁还未释放，session C会被阻塞
  4. session D只需要申请MDL读锁，但同样会被session C阻塞
    • 所有对表的增删改查都需要先申请MDL读锁，此时表现为完全不可读写
    • 如果该表上的查询比较频繁，而且客户端恰好有重试机制（超时后再起一个session去请求）
      • 那么数据库的线程很快就会被占满
  5. 事务中的MDL锁，在语句开始执行时申请，但会等到整个事务提交后再释放

解决办法

  1. 首先要解决长事务的影响，因为只要事务不提交，就会一直占用相关的MDL锁
    • INFORMATION_SCHEMA.INNODB_TRX中的trx_started字段
    • 在做DDL变更之前，首先确认是否长事务在执行，如果有则先kill掉这个长事务
  2. 如果需要执行DDL的表是热点表，请求很频繁，kill长事务未必管用，因为很快就会有新的请求
    • ALTER TALE语句设定等待时间，就算拿不到MDL写锁也不至于长时间阻塞后面的业务语句
    • 目前MariaDB和AliSQL支持该功能
1
ALTER TABLE T [WAIT [n]|NO_WAIT] ADD f INT

关系

  1. 线程A在MyISAM表上更新一行数据，那么会加MDL读锁和表的写锁
  2. 线程B在同一个MyISAM表上更新另外一行数据，那么也会加MDL读锁和表的写锁
    • 线程B加MDL读锁成功，但加表的写锁失败
    • 表现：线程B被线程A阻塞
  3. 引申：如果有多种锁，必须全部锁不互斥才能并行，只要有一个锁互斥，就得等

行锁

  1. MySQL的行锁是在存储引擎层实现的
  2. MyISAM不支持行锁，而InnoDB支持行锁，这是InnoDB替代MyISAM的一个重要原因

两阶段锁

id为表t的主键，事务B的update语句会被阻塞，直到事务A执行commit之后，事务B才能继续执行

  1. 两阶段锁
    • 在InnoDB事务中，行锁是在需要的时候加上
    • 但并不是在不需要了就立刻释放，而是要等待事务结束后才释放
  2. 如果事务需要锁定多行，要就把最可能造成锁冲突和影响并发度的锁尽可能往后放

电影票业务

  1. 顾客A在电影院B购买电影票，涉及以下操作（同一事务）
    • update：从顾客A的账户余额中扣除电影票价
    • update：给电影院B的账户余额增加电影票价
    • insert：记录一条交易日志
  2. 假设此时顾客C也要在电影院B买票的，两个事务冲突的部分就是第2个语句（同一个电影院账户）
    • 所有操作所需要的行锁都是在事务结束的时候才会释放
    • 将第2个语句放在最后，能最大程度地减少事务之间的锁等待，提升并发度

死锁

假设电影院做活动，在活动开始的时候，CPU消耗接近100%，但整个库每秒执行不到100个事务

  1. 事务A在等待事务B释放id=2的行锁，事务B在等待事务A释放id=1的行锁，导致死锁
  2. 当出现死锁后，有2种处理策略
    • 等待，直至超时（不推荐）
      • 业务有损：业务会出现大量超时
    • 死锁检测（推荐）
      • 业务无损：业务设计不会将死锁当成严重错误，当出现死锁时可采取：_事务回滚+业务重试_

等待

  1. 由参数innodb_lock_wait_timeout控制（MySQL 5.7.15引入）
  2. 默认是50s，对于在线服务来说是无法接受的
  3. 但也不能设置成很小的值，因为如果实际上并不是死锁，只是简单的锁等待，会出现很多误伤

死锁检测（推荐）

  1. 发现死锁后，主动回滚锁链条中的某一事务，让其他事务继续执行
    • 需要设置参数innodb_deadlock_detect
  2. 触发死锁检测：要加锁访问的行上有锁
    • 一致性读不会加锁
  3. 死锁检测并不需要扫描所有事务
    • 某个时刻，事务等待状态为：事务B等待事务A，事务D等待事务C
    • 新来事务E，事务E需要等待D，那么只会判断事务CDE是否会形成死锁
  4. CPU消耗高
    • 每个新来的线程发现自己要加锁访问的行上有锁
      • 会去判断会不会由于自己的加入而导致死锁，总体时间复杂度为**O(N^2)**
    • 假设有1000个并发线程，最坏情况下死锁检测的操作量级为100W（1000^2）
  5. 解决方法
    • 如果业务能确保一定不会出现死锁，可以临时关闭死锁检测，但存在一定的风险（超时）
    • 控制并发度，如果并发下降，那么死锁检测的成本就会降低，这需要在数据库服务端实现
      • 如果有中间件，可以在中间件实现
      • 如果能修改MySQL源码，可以在MySQL内部实现
    • 设计上的优化
      • 将一行改成逻辑上的多行来减少锁冲突
1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> SHOW VARIABLES LIKE '%innodb_deadlock_detect%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| innodb_deadlock_detect | ON    |
+------------------------+-------+

mysql> SHOW VARIABLES LIKE '%innodb_lock_wait_timeout%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 30    |
+--------------------------+-------+

更新无锁引字段

1
2
# name字段没有索引
UPDATE t SET t.name='abc' WHERE t.name='cde'

InnoDB内部会根据聚簇索引，_逐行扫描，逐行加锁，事务提交后统一释放锁_

参考资料

《MySQL实战45讲》