这是我参与11月更文挑战的第11天，活动详情查看：2021最后一次更文挑战

锁

根据加锁的范围，MySQL 中的锁可分为三类：

• 全局锁
• 表级锁
• 行锁

全局锁

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

表级锁

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

表锁

表锁使用场景：

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

其中表锁又分为表读锁和表写锁，命令分别是： 表读锁：lock tables t14 read; 表写锁：lock tables t14 write; 下面我们分别用实验验证表读锁和表写锁。

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

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

元数据锁

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

行锁

MySQL 5.5 之前的默认存储引擎是 MyISAM，5.5 之后改成了 InnoDB。InnoDB 后来居上最主要的原因就是：

• InnoDB 支持事务：适合在并发条件下要求数据一致的场景。
• InnoDB 支持行锁：有效降低由于删除或者更新导致的锁定。

两阶段锁

传统的关系型数据库加锁的一个原则是：两阶段锁原则。

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

innoDB行锁模式

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

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

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

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

行锁算法

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

事务隔离级别

MySQL 的 4 种隔离级别：

• Read uncommitted（读未提交）: 在该隔离级别，所有事务都可以看到其它未提交事务的执行结果。可能会出现脏读。
• Read Committed（读已提交，简称： RC）：一个事务只能看见已经提交事务所做的改变。因为同一事务的其它实例在该实例处理期间可能会有新的 commit，所以可能出现幻读。
• Repeatable Read（可重复读，简称：RR）：这是 MySQL 的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。消除了脏读、不可重复读，默认也不会出现幻读。
• Serializable（串行）：这是最高的隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。

这里解释一下脏读和幻读：

• 脏读：读取未提交的事务。
• 幻读：一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据。

RC 隔离级别下的行锁实验

没有索引的情况下，InnoDB 的当前读会对所有记录都加锁。所以在工作中应该特别注意 InnoDB 这一特性，否则可能会产生大量的锁冲突。

如果查询的条件是唯一索引，那么 SQL 需要在满足条件的唯一索引上加锁，并且会在对应的聚簇索引上加锁。

如果查询的条件是非唯一索引，那么 SQL 需要在满足条件的非唯一索引上都加上锁，并且会在它们对应的聚簇索引上加锁。

死锁

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

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

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

为什么会产生死锁

同一张表中

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

不同表之间

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

事务隔离级别

RR 隔离级别下，由于间隙锁导致死锁。

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

如何降低死锁概率

那么应该怎样降低出现死锁的概率呢？这里总结了如下一些经验：

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

分析死锁的方法

InnoDB 中，可以使用 SHOW INNODB STATUS 命令来查看最后一个死锁的信息。

总结：

本节聊了死锁相关的内容。通过具体实验列举了几种出现死锁的情况：

• 不同线程并发访问同一张表的多行数据，未按顺序访问导致死锁；
• 不同线程并发访问多个表时，未按顺序访问导致死锁；
• RR 隔离级别下，由于间隙锁导致死锁。

后面提供了几种降低死锁概率的方法。

由于死锁不能完全杜绝，因此，在最后提供了捕获死锁信息的方法，在工作中我们可以把死锁信息记录下来，如果出现频率过高，就应该考虑去优化程序了。