本地煮鸡:8080

# 锁

为了解决并发操作可能会出现的问题，MySQL 提供了锁机制。根据加锁范围，分为全局锁、表级锁和行级锁。

# 全局锁

使用命令 FLUSH TABLES WITH READ LOCK 让整个库处于只读状态。之后其他线程的 DDL(数据定义语言，包括创建表、修改表等)、DML(数据操作语言，包括增、删、改)和更新类的事务的提交语句会被阻塞。
使用 UNLOCK TABLES 解锁。

# 使用场景

当在进行全库逻辑备份（所有记录查找并保存到文本）时，可以考虑使用全局锁。

  • 必要性
    可能会发生数据不一致。在不加全局锁的情形下，因为不同表之间执行顺序不同备份时间就不同。若一张表在备份时间差内进行了更新操作，则它会与已经备份的关联表数据不一致。

    一个有限制条件的解决方案

    可以利用可重复读的事务来解决。在支持事务的引擎里，比如 InnoDB 提供了逻辑备份工具 mysqldump. 使用参数 -single-transaction 来使用它时会在导入数据前启动一个事务，保证视图的一致性。且由于多版本并发控制 （MVCC），在备份过程中可以进行更新操作。

  • 缺点
    • 在主库备份时，FTWRL 会使得主库不能执行更新操作，业务停摆；
    • 若是读写分离的主从库模式，从库备份时，从库不能执行主库同步的 binglog 导致主从延迟。

关于设置全局只读的解决方法

使用 set global readonly=true 也可以使得整个库处于只读的状态，但会带来一些问题：

  • 在一些主从库中，会使用 readonly 作为逻辑判断是否是从库；
  • 当客户端发生异常断开时，readonly 不会改变库的状态，依旧保持只读会导致库处于长时间不可写状态。 FTWRL 在发生断开时会自动释放全局锁，让库恢复可以正常更新的状态。

# 表级锁

表锁分为两种：表锁和元数据锁。

# 表锁

使用 LOCK TABLES $table READ/WRITE 来对表进行锁定。它使用 UNLOCK TABLES 主动解锁或在客户端断开连接时自动释放锁。它不仅限制别的线程的读或写，也限制本线程的操作：

  • 线程 A 对某表添加读锁，线程 A 和其他线程都不能对表进行写操作。同时线程 A 不可以读写其他未加锁的表；
  • 线程 A 对某表添加写锁，线程 A 可以对表进行写操作，其他线程对表读写都阻塞；

理解为共享读锁，独占写锁。表锁在没有更细粒度的锁出现前，是最常用的并发处理机制。

# 元数据锁 (Metadata Lock) MySQL 5.5+

元数据锁会在一个表被访问时自动加锁而不需要显示使用，是一个 Server 层的锁。它可以保证读写的正确性。MDL 在语句开始时申请，事务结束后释放。

  • 当一个表在进行增删改查时会添加读锁，此时所有线程都可以正常读取元数据，不影响增删改查。（读锁之间不互斥，多个线程可以对同一张表进行增删改查）
  • 当一个表正在进行结构更改操作时加写锁，只有拥有锁的线程可以读写元数据，其他线程不能执行任何操作。（写锁与读锁、写锁与写锁互斥，保证结构变更安全性）

申请 MDL 的操作会形成一个队列，其中写锁优先级高于读锁。所以当一个写锁处于阻塞时，当前和后续所有操作都会被阻塞。所以情况如：事务 A 的查询操作触发 MDL 读锁，事务 B 包含 DDL 语句被阻塞，事务 C 有查询语句，但是因为优先级也被阻塞。意味着后续任何操作都会被阻塞。
当一个事务拿到 MDL 后，只有当事务结束时才会释放锁。如果事务包含 DDL，这样它会在 DDL 执行前隐式提交事务以保障 DDL 处于一个单独的事务中，这时也会释放 MDL。

解决方案

  • 解决长事务。查询 information_schema 库中的 innodb_trx 表，若要执行 DDL 的表正好在长事务，可以暂停 DDL 或者 kill 长事务。
  • 如果目标表是热点表，就在 DDL 中设置超时时间，在规定时间内拿到 MDL 写锁即执行，拿不到则放弃。后续重试。
ALTER TABLE tbl_name NOWAIT add column ...
ALTER TABLE tbl_name WAIT N add column ...
1
2

# 行级锁

行级锁由引擎实现，MyISAM 不支持行级锁。对于同一行记录，当事务 A 和事务 B 都想更新它时，必须等事务 A 操作完成后 B 才执行。

# 两段锁

行锁在需要时被加，事务结束后释放。当一个事务需要锁定多个行时，应当吧最可能造成冲突和影响并发度的锁向后放，从而减少一次操作中锁定共享数据的时间，提升效率。

# 死锁检测

当不同线程出现循环资源依赖时，每个线程都在等其他线程释放资源，进入死锁状态。为了避免死锁，有两种方法：

  • 通过设置参数 innodb_lock_wait_timeout 来设置超时，如果等待超过预定时间则放弃。超时时间不容易控制，默认为 50 秒，但一般对于在线服务来说这个时间；如果设置过短，对于短等待事务不合理；
  • 通过设置参数 innodb_deadlock_detect 为 on 来开启死锁检测，当发现死锁后主动回滚其中一个事务，让其他事务继续。死锁检测的操作是 级别的。会造成 CPU 利用率高。
    解决方案：
    • 如果能保证业务不会出现死锁，临时关闭死锁检测。但可能带来大量超时，业务有损。
    • 控制并发度。编写中间件，或者修改 MySQL 源码，使得同行更新在进入引擎前进行排队。
    • 业务进行逻辑分段。

← 参考文献或资料 →