06、07 | 全局锁、表锁和行锁

以下内容出自《MySQL 实战 45 讲》

06、07 | 全局锁、表锁和行锁

MySQL 中的锁大致分为全局锁、表锁和行锁。

全局锁

全局锁就是对整个数据库实例加锁。

加全局锁命令：Flush tables with read lock（FTWRL），执行该命令会让整个库处于只读状态。

Flush tables with read lock;

使用场景： 做全库逻辑备份。

有了 MVCC 的支持，为什么还需要 FTWRL ?

一致性读是存储引擎提供的，但不是所有引擎都支持事务。

还有一种方式可以实现全库只读。

set global  read_only  = true;

但建议使用 FTWRL 来完成全库只读。原因如下：

• 在有些系统中， readonly 的值被用来做其他逻辑。如判断一个库是备库还是主库。
• 异常处理机制上存在差异。
  • 如果执行 FTWRL 命令之后由于客户端异常断开，MySQL 会自动释放这个全局锁。
  • 整库设置为 readonly 之后，如果客户端发生异常，则数据库会一直会保持 readonly 状态，风险较高。

表级锁

表锁一般是在数据库引擎不支持行锁的时候才会被用到的。

MySQL 中表级锁有两种：

• 表锁
• 元数据锁（meta data lock,MDL)

表锁

语法:

加锁：lock table ... read/write；

释放锁：

• 执行：unlock tables;

• 客户端断开时主动释放

-- 对 user 表加读锁
lock tables user read;
-- 对 userinfo 表加写锁
lock tables userinfo write;
-- 释放锁
unlock tables;

特点：

表锁类型	当前线程	其他线程
读锁	只能读取，不能修改	只能读取，不能修改
写锁	能读写	不能读，更不能写

MDL 锁

MDL 不需要显式使用，在访问一个表的时候会被自动加上。

作用：保证读写的正确性。用于隔离 DML 和 DDL 操作之间的干扰。

DML 操作需要加 MDL 读锁。

DDL 操作需要加 MDL 写锁。

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

行锁

MySQL 的行锁是在引擎层由各个引擎自己实现的。并不是所有的引擎都支持行锁。

行锁就是针对数据库表中行记录的锁。

两阶段锁协议

在 InnoDB 事务中，行锁是在需要的时候加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议。

知道了这个规则，在实际使用中，如果事务中需要锁多个行，要把最可能造成锁冲突，最可能影响并发度的锁尽量往后放。

死锁与死锁检测

当并发系统中不同线程出现循环资源依赖，涉及的线程都在等待别的线程释放资源时，就会导致这几个线程进入无限等待的状态，称谓死锁。

出现死锁后的两种策略：

• 直接进入等待，直到超时。超时时间可以通过 innodb_lock_wait_timeout 来设置。

  show variables like 'innodb_lock_wait_timeout';
  

• 发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑。（默认开启）

  show variables like 'innodb_deadlock_detect';
  

在 InnoDB 中， innodb_lock_wait_timeout 默认值为 50s，意味着如果采用第一个策略，出现死锁后，第一个被锁住的线程要超过 50s 才会超时退出，其他线程才有可能继续执行。对于在线服务来说，这个等待时间往往无法接受。但是，如果把这个时间设置为一个很小的值，会出现很多误伤。所以，正常情况下，我们一般采用主动死锁检测策略。

对于所有事务都更新同一行的场景。每个新来的被堵住的线程，都要判断会不会由于自己的加入导致了死锁，这是一个时间复杂度为 O(n) 的操作。假设有 1000 个并发线程要同时更新同一行，那么死锁检测操作就是 100 万这个量级的。在这期间要消耗大量的 CPU资源。此时现象就是 CPU 利用率很高，但是每秒执行不了几个事务。

如何解决热点行更新导致的性能问题呢？

问题的症结在于，死锁检测要耗费大量的 CPU 资源。

减少死锁的主要方向，就是控制访问相同资源的并发事务量。

• 确保业务一定不会出现死锁，临时把死锁检测关掉。
• 控制并发度，这样死锁检测的成本很低。
• 将一行改成逻辑上的多行来减少锁冲突。