1. 全局锁和表锁什么场景会用到？

MySQL 中，锁就是协调多个用户或者客户端并发访问某一资源的机制，保证数据并发访问时的一致性、有效性。

可以分为：全局锁、表锁和行锁。

1.1 全局锁

全局锁会关闭所有打开的锁，并使用全局读锁锁定所有表。

命令：

FLUSH TABLES WITH READ LOCK; 简称：FTWRL

UNLOCK TABLES;

当 session1 执行 FTWRL 后，本线程 session1 和其它线程 session2 都可以查询，本线程和其它线程都不能更新。

原因是：当执行 FTWRL 后，所有的表都变成只读状态，数据更新或者字段更新将会被阻塞。

全局锁通常使用的场景：

全局锁一般用在整个库（包含非事务引擎表）做备份（mysqldump 或者 xtrabackup）时。也就是说，在整个备份过程中，整个库都是只读的，其实这样风险挺大的。如果是在主库备份，会导致业务不能修改数据；而如果是在从库备份，就会导致主从延迟。好在 mysqldump 包含一个参数 --single-transaction，可以在一个事务中创建一致性快照，然后进行所有表的备份。因此增加这个参数的情况下，备份期间可以进行数据修改。但是需要所有表都是事务引擎表。所以这也是建议使用 InnoDB 存储引擎的原因之一。而对于 xtrabackup，可以分开备份 InnoDB 和 MyISAM，或者不执行 --master-data，可以避免使用全局锁。

1.2 表级锁

表级锁有两种：表锁和元数据锁。

1.表锁

表锁使用场景：

  • 事务需要更新某张大表的大部分或全部数据。如果使用默认的行锁，不仅事务执行效率低，而且可能造成其它事务长时间锁等待和锁冲突，这种情况下可以考虑使用表锁来提高事务执行速度；
  • 事务涉及多个表，比较复杂，可能会引起死锁，导致大量事务回滚，可以考虑表锁避免死锁。

表锁有分为表读锁和表写锁，命令如下：

表读锁：lock tables t14 read;

表写锁：lock tables t14 write;

总结：

对表执行 lock tables xxx read （表读锁）时，本线程和其它线程可以读，本线程写会报错，其它线程写会等待。

对表执行 lock tables xxx write （表写锁）时，本线程可以读写，其它线程读写都会阻塞。

2.元数据锁

在 MySQL 中，DDL 是不属于事务范畴的。如果事务和 DDL 并行执行同一张表时，可能会出现事务特性被破坏、binlog 顺序错乱等 bug（比如 bug#989）。为了解决这类问题，从 MySQL 5.5.3 开始，引入了元数据锁（Metadata Locking，简称：MDL 锁）（这段内容参考《淘宝数据库内核月报》MySQL · 特性分析 · MDL 实现分析）。

从上面我们知道，MDL 锁的出现解决了同一张表上事务和 DDL 并行执行时可能导致数据不一致的问题。

但是，我们在工作中，很多情况需要考虑 MDL 的存在，否则可能导致长时间锁等待甚至连接被打满的情况。

因此对于开发来说，在工作中应该尽量避免慢查询、尽量保证事务及时提交、避免大事务等，当然对于 DBA 来说，也应该尽量避免在业务高峰执行 DDL 操作。

1.3 行锁: InnoDB替代MyISAM的重要原因

1.两阶段锁

锁操作分为两个阶段，加锁阶段和解锁阶段，并且保证加锁阶段和解锁阶段不相交。

2.InnoDB 行锁模式

InnoDB 实现了以下两种类型的行锁：

  • 共享锁（S）：允许一个事务去读一行，阻止其它事务获得相同数据集的排他锁；
  • 排他锁（X）：允许获得排他锁的事务更新数据，阻止其它事务取得相同数据集的 共享读锁和排他写锁。

对于普通 select 语句，InnoDB 不会加任何锁，事务可以通过以下语句显式地给记录集加共享锁或排他锁：

  • 共享锁（S）：select * from table_name where … lock in share mode;
  • 排他锁（X）：select * from table_name where … for update。

3.InnoDB 行锁算法

三种算法：

  • Record Lock：单个记录上的索引加锁。
  • Gap Lock：间隙锁，对索引项之间的间隙加锁，但不包括记录本身。
  • Next-Key Lock：Record Lock + Gap Lock，锁定一个范围，并且锁定记录本身。

InnoDB 行锁实现特点意味着：如果不通过索引条件检索数据，那么 InnoDB 将对表中所有记录加锁，实际效果跟表锁一样。

4.事务隔离级别

见下一专题。

2.间隙锁的意义

本节讲解了 RC 隔离级别出现幻读的情况，而 RR 下，通过 Next-Key Lock 解决了当前读下的幻读现象（通过MVCC解决了快照读的幻读现象），但是 RR 隔离级别相对于RC，锁的范围可能更大了，特别是对没有索引的字段进行当前读（比如增、删、改或者 select … for update）时，会阻塞除快照读以外所有的并发 SQL。而后面我们又聊了以唯一索引作为条件的当前读不会用到 GAP 锁，因为根据唯一索引查询最多就一条记录，而且相同索引记录的值，一定不会再新增。

3.为什会出现死锁？

3.1 认识死锁

死锁是指两个或多个事务在同一个资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环的现象。

InnoDB 中解决死锁问题有两种方式：

  • 检测到死锁的循环依赖，立即返回一个错误（这个报错内容请看下面的实验），将参数 innodb_deadlock_detect 设置为 on 表示开启这个逻辑；
  • 等查询的时间达到锁等待超时的设定后放弃锁请求。这个超时时间由 innodb_lock_wait_timeout 来控制。默认是 50 秒。

3.2 为什么会产生死锁

同一张表中：

对于多个程序并发访问同一张表时，如果事先确保每个线程按固定顺序来处理记录，可以降低死锁的概率。

不同表之间：

不同程序并发访问多个表时，应尽量约定以相同的顺序来访问表，可大大降低并发操作不同表时死锁发生的概率。

事务隔离级别：

回顾上一节第 2 部分 <RR 隔离级别下的非唯一索引查询>，可以知道 SQL3 需要等 待 a=2 获得的间隙锁，而 SQL4 需要等待 a=1 获得的间隙锁，两个 session 互相 等待对方释放资源，就进入了死锁状态。

类似这种情况，可以考虑将隔离级别改成 RC（这里各位读者可以尝试在 RC 隔离级 别下，做上面的实验），降低死锁的概率（当然根据上一节所讲到的，RC 隔离级别可 能会导致幻读，因此需要确定是否可以改成 RC。）

3.3 如何降低死锁概率

这里总结了如下一些经验：

  • 更新 SQL 的 where 条件尽量用索引；
  • 基于 primary 或 unique key 更新数据；
  • 减少范围更新，尤其非主键、非唯一索引上的范围更新；
  • 加锁顺序一致，尽可能一次性锁定所有需要行；
  • 将 RR 隔离级别调整为 RC 隔离级别。

3.4 分析死锁的方法

InnoDB 中，可以使用 SHOW ENGINE INNODB STATUS 命令来查看最后一个死锁的信息。我们可以尝试用以下这个命令获取一些死锁信息，如下：

show engine innodb status

img

另外设置 innodb_print_all_deadlocks = on 可以在 err log 中记录全部死锁信息。

因此我们可以通过上面两种方式捕获死锁信息，从而进行优化。