自存笔记，学习MySQL实战45讲

第五讲：全局锁和表级锁

全局锁

  • 一种将整个数据库实例锁定的机制
  • 会组织其他事务对数据库的读写访问
  • 一般在逻辑备份中使用

实现

  • 在InnoDB中可以使用 mysqldump 的 —single-transaction 选项，结合 InnoDB 的 MVCC 机制实现一致性备份，无需全局锁

    1
    mysqldump -u root -p --all-databases --single-transaction > backup.sql

  • 在不支持事务的引擎中

    1
    2
    3
    FLUSH TABLES WITH READ LOCK;
    -- 执行备份或其他需要全局一致性的操作
    UNLOCK TABLES;
1
2
3
SET GLOBAL READ_ONLY=1;
-- 执行操作
SET GLOBAL READ_ONLY=0;
 **建议使用FTWRL**

表级锁

读锁可兼容读锁，写锁阻塞所有锁

表锁

  • 锁定粒度为整张表
  • 在存储引擎层加锁
  • 不仅会对表的写/读加锁，获取到锁的线程也只能对限定的表做限定的写/读操作
1
LOCK TABLES table_name READ/WRITE

元数据锁

  • 在表结构发生变更时，保护表元数据（结构、权限等）
  • 在server层
  • MySQL会在执行SQL语句时隐式获取队形的MDL锁，无需手动操作
    • 对一个表做增删改查时，加MDL读锁
    • 对表结构变更时，加MDL写锁

对热点表DDL变更的挑战

  • 热点表：数据量小但是请求频繁
  • DDL变更：设计库表结构修改，create/drop等

问题

  • MDL锁阻塞：长事务持有MDL读锁，导致DDL所需的MDL写锁无法获取，造成阻塞
  • 传统方案局限
    • KILL事务：仅能暂时释放锁，但是热点表的新请求会立即持有锁，无法根治
    • 强制阻塞DDL：一直等待直到获取锁，导致请求大量超时，影响可用性

解决方案

NOWAIT/WAIT n（MariaDB/AliSQL 支持）

  • ALTER TABLE … NOWAIT
    • 立即尝试获取 MDL 写锁，若当前无法获取（存在活跃读锁），则直接报错返回，不等待
  • ALTER TABLE … WAIT n
    • 尝试获取 MDL 写锁，最多等待n秒，若超时仍未获取锁，则报错返回。

第七讲：行锁

行锁

  • 锁定表中单行记录
  • 需要就加上了，等到事务结束才释放
  • 若事务中要锁多个行，把最可能影响并发度的锁往后放

类型

共享锁(Shared Lock, S锁)

  • 就是读锁
  • 允许并发读
1
SELECT ... FROM table WHERE id=1 LOCK IN SHARE MODE; -- 显式加S锁

排他锁（Exclusive Lock， X锁）

  • 与其他任何锁互斥
  • INSERT、UPDATE、DELETE自动加X锁

死锁和死锁检测

死锁

  • 多事务执行过程中，因争夺互斥资源而形成的循环等待链，导致事务无法继续执行，若无外部干预，则会一直阻塞下去

典型场景

  • 事务 A：更新账户 1（ID=1）的余额（加排他锁 X 锁），再尝试更新账户 2（ID=2）的余额（请求 X 锁）
  • 事务 B：更新账户 2（ID=2）的余额（加 X 锁），再尝试更新账户 1（ID=1）的余额（请求 X 锁）

结果：

  • 事务 A 持有 ID=1 的 X 锁，请求 ID=2 的 X 锁（被事务 B 持有）
  • 事务 B 持有 ID=2 的 X 锁，请求 ID=1 的 X 锁（被事务 A 持有）

形成循环等待链，导致死锁。

解决策略

  • 直接等待到超时，超市时间可以通过参数innodb_lock_wait_timeout来设置
  • 死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续运行

死锁检测问题

死锁检测会占用大量cpu资源，存在热点行会影响性能问题，cpu利用率高但事务执行少

  • 临时关闭死锁检测，可能会出现超时问题
  • 在数据库服务端做并发控制，对相同行的更新，在进入引擎之前排队