谈谈 MySQL 锁机制

为什么要学习锁机制

锁是计算机协调多个进程或线程并发访问某一资源的机制。

因为数据也是一种供许多用户共享的资源，如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素，所以进一步学习 MySQL，就需要去了解它的锁机制。

本文主要记录学习了 MyISAM 和 InnoDB 这两个存储引擎，而且更加关注的是 InnoDB（因为经常用😁）

MySQL 锁概述：

相对其他数据库而言，MySQL 的锁机制比较简单，其最显著的特点是不同的存储引擎支持不同的锁机制。

比如，MyISAM 和 MEMORY 存储引擎采用的是表级锁（table-level locking）；BDB 存储引擎采用的是页面锁（page-level locking），但也支持表级锁；InnoDB 存储引擎既支持行级锁（row-level locking），也支持表级锁，但默认情况下是采用行级锁。

MySQL 这 3 种锁的特性可大致从开销、加锁速度、死锁、粒度、并发性能几点进行归纳。

①：表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高,并发度最低。

②：行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高。

③：页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

从上述特点可见，很难笼统地说哪种锁更好，只能就具体应用的特点来说哪种锁更合适！仅从锁的角度来说：表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如 Web 应用；而行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理（OLTP）系统。由于 BDB 已经被 InnoDB 取代，即将成为历史（所以现在基本都在使用 InnoDB 存储引擎）。

MyISAM 存储引擎

MyISAM 存储引擎只支持表锁，这也是 MySQL 开始几个版本中唯一支持的锁类型。

MySQL 表级锁

查询表锁争用情况

mysql> show status like 'table%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Table_locks_immediate      | 4     |
| Table_locks_waited         | 0     |
| Table_open_cache_hits      | 4     |
| Table_open_cache_misses    | 8     |
| Table_open_cache_overflows | 0     |
+----------------------------+-------+
5 rows in set (0.00 sec)
复制

复制

如果 Table_locks_waited 的值比较高，则说明存在着较严重的表级锁争用情况。

MySQL 的表级锁的两种模式

• 表共享读锁（Table Read Lock）

• 表独占写锁（Table Write Lock）

MySQL 中的表锁兼容性：

也就是说，在 MyISAM 读模式下，不会阻塞其它用户的同一表读操作，但是会阻塞写操作；而在写模式下，会同时阻塞其它用户同一表的读写操作。

测试 MyISAM 的写锁模式

新建一个 user 表，引擎是 MyISAM：

mysql> desc user;
+---------+-------------+------+-----+---------+----------------+
| Field   | Type        | Null | Key | Default | Extra          |
+---------+-------------+------+-----+---------+----------------+
| id      | int(11)     | NO   | PRI | NULL    | auto_increment |
| name    | varchar(20) | YES  |     | NULL    |                |
| age     | int(3)      | YES  |     | NULL    |                |
| address | varchar(60) | YES  |     | NULL    |                |
+---------+-------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)
复制

可以看出，通过 lock table user write 将 user 表锁住后，其它用户进行对该表操作时，都会被阻塞。

测试 MyISAM 读锁

在用 LOCK TABLES 给表显式加表锁时，必须同时取得所有涉及到表的锁，并且MySQL 不支持锁升级。也就是说，在执行 LOCK TABLES 后，只能访问显式加锁的这些表，不能访问未加锁的表；同时，如果加的是读锁，那么只能执行查询操作，而不能执行更新操作。

MyISAM 支持并发插入

MyISAM 表的读和写是串行的，但这是就总体而言的。在一定条件下，MyISAM表也支持查询和插入操作的并发进行。MyISAM 存储引擎有一个系统变量 concurrent_insert，专门用以控制其并发插入的行为，其值分别可以为 0、1 或 2。

• 当 concurrent_insert 设置为 0 时，不允许并发插入。
  
  

• 当 concurrent_insert 设置为 1 时，如果 MyISAM 表中没有空洞（即表的中间没有被删除的行），MyISAM 允许在一个进程读表的同时，另一个进程从表尾插入记录。这也是 MySQL 的默认设置。
  
  

• 当 concurrent_insert 设置为 2 时，无论 MyISAM 表中有没有空洞，都允许在表尾并发插入记录。

MyISAM 的锁调度

MyISAM 存储引擎的读锁和写锁是互斥的，读写操作是串行的。 但它认为写锁的优先级比读锁高，所以即使读请求先到锁等待队列，写请求后到，写锁也会插到读锁请求之前！

这也正是 MyISAM 表不太适合于有大量更新操作和查询操作应用的原因，因为，大量的更新操作会造成查询操作很难获得读锁，从而可能永远阻塞。 可以通过一些设置来调节 MyISAM 的调度行为。

• 通过指定启动参数 low-priority-updates，使 MyISAM 引擎默认给予读请求以优先的权利。
  
  

• 通过执行命令 SET LOW_PRIORITY_UPDATES=1，使该连接发出的更新请求优先级降低。
  
  

• 通过指定 INSERT、UPDATE、DELETE 语句的 LOW_PRIORITY 属性，降低该语句的优先级。

虽然上面 3 种方法都是要么更新优先，要么查询优先的方法，但还是可以用其来解决查询相对重要的应用（如用户登录系统）中，读锁等待严重的问题。

另外，MySQL 也提供了一种折中的办法来调节读写冲突，即给系统参数 max_write_lock_count 设置一个合适的值，当一个表的读锁达到这个值后，MySQL 就暂时将写请求的优先级降低，给读进程一定获得锁的机会。

上面已经讨论了写优先调度机制带来的问题和解决办法。这里还要强调一点：一些需要长时间运行的查询操作，也会使写进程“饿死”！因此，应用中应尽量避免出现长时间运行的查询操作。

不要总想用一条 SELECT 语句来解决问题，因为这种看似巧妙的 SQL 语句，往往比较复杂，执行时间较长，在可能的情况下可以通过使用中间表等措施对 SQL 语句做一定的 “分解”，使每一步查询都能在较短时间完成，从而减少锁冲突。如果复杂查询不可避免，应尽量安排在数据库空闲时段执行，比如一些定期统计可以安排在夜间执行。

InnoDB

InnoDB 与 MyISAM 的最大不同有两点：一是支持事务（TRANSACTION）；二是采用了行级锁。行级锁与表级锁本来就有许多不同之处，另外，事务的引入也带来了一些新问题。

事务概念

学习 Spring 的时候，一般通过注解 @Transitional 就能启动 spring 的事务管理，在 MySQL 中也同样支持事务的四个原则 ACID：

• A(Atomicity)原子性：事务是一个原子操作单元，其对数据的修改，要么全都执行，要么全都不执行。
  
  

• C(Consistent)一致性：在事务开始和完成时，数据都必须保持一致状态。这意味着所有相关的数据规则都必须应用于事务的修改，以保持数据的完整性；事务结束时，所有的内部数据结构（如 B 树索引或双向链表）也都必须是正确的。
  
  

• I(Isolation)隔离性：数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的 “独立” 环境执行。这意味着事务处理过程中的中间状态对外部是不可见的，反之亦然。
  
  

• D(Durable)持久性：事务完成之后，它对于数据的修改是永久性的，即使出现系统故障也能够保持。
  

并发事务处理带来的问题

相对于串行处理来说，并发事务处理能大大增加数据库资源的利用率，提高数据库系统的事务吞吐量，从而可以支持更多的用户。但并发事务处理也会带来一些问题，主要包括以下几种情况。

• 更新丢失（Last update）：A 和 B 同时对一行数据进行处理，A 修改后进行保存，然后 B 修改后进行保存，这样 A 的更新被覆盖了，相当于发生丢失更新的问题。所以可以在 A 事务未结束前，B 不能访问该记录，这样就能避免更新丢失的问题。
  
  

• 脏读（Dirty Reads）：A 事务在对一条记录做修改，但还未提交，这条记录处于不一致的状态；这时，B 事务也来读同一条记录，这时如果没有加控制，B 读了未修改前的数据，并根据该数据进行进一步处理，就会产生未提交的数据依赖关系。这种现象叫做“脏读”
  
  

• 不可重复读（Non-Repeatable Reads）：B 事务在读取某些数据后的某个时间，再次读取以前读过的数据，却发现其读出的数据已经发生了改变（被更新或者删除了，例如 A 事务修改了）。这种现象叫做“不可重复读”。
  
  

• 幻读（Phantom Reads)：A 事务按照相同查询条件，重新读取之前检索过得内容，却发现其它事务插入或修改其查询条件的新数据，这种现象就叫”幻读“。
  

事务的隔离级别

数据库的事务隔离越严格，并发副作用越小，但付出的代价也就越大，因为事务隔离实质上就是使事务在一定程度上 “串行化”进行，这显然与“并发”是矛盾的。同时，不同的应用对读一致性和事务隔离程度的要求也是不同的，比如许多应用对“不可重复读”和“幻读”并不敏感，可能更关心数据并发访问的能力。

4 种隔离级别比较

获取 InnoDB 行锁争用情况

检查 InnoDB_row_lock 状态变量来分析：

mysql> show status like 'InnoDB_row_lock%';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 0     |
| Innodb_row_lock_time          | 0     |
| Innodb_row_lock_time_avg      | 0     |
| Innodb_row_lock_time_max      | 0     |
| Innodb_row_lock_waits         | 0     |
+-------------------------------+-------+
5 rows in set (0.00 sec)
复制

复制

如果 InnoDB_row_lock_waits 和 InnoDB_row_lock_time_avg 的值比较高，表示锁争用情况比较严重。

InnoDB 的行锁模式以及加锁方法

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

• 共享锁(S)：允许一个事务去多一行，阻止其它事务获得相同数据集的排他锁。
  
  

• 排他锁(X): 允许获得排他锁的事务更新数据，阻止其它事务获得相同数据集的共享锁和排他写锁。

另外，为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB 还有两种内部使用的意向锁（Intention Locks），这两种意向锁都是表锁。（感觉与MyISAM 的表锁机制类似）

• 意向共享锁（IS）：事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的 IS 锁。
  
  

• 意向排他锁（IX）：事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的 IX 锁。
  

InnoDB 行锁模式兼容性列表:

如果一个事务请求的锁模式与当前的锁兼容，InnoDB 就将请求的锁授予该事务；反之，如果两者不兼容，该事务就要等待锁释放。 意向锁是 InnoDB 自动加的。

对于 UPDATE、DELETE 和 INSERT 语句，InnoDB 会自动给设计数据集加排他锁（X）；对于普通的 SELECT 语句，InnoDB 不会加锁。 可以通过以下语句显示给记录集加共享锁或排他锁：

• 共享锁（S）：SELECT * FROM TABLE_NAME WHERE ... LOCK IN SHARE MODE.
  
  

• 排他锁（X）：SELECT * FROM TABLE_NAME WHERE ... FOR UPDATE.

用 SELECT ... IN SHARE MODE 获得共享锁，主要用在需要数据依存关系时来确认某行记录是否存在，并确保没有人对这个记录进行 UPDATE 或者DELETE 操作。

但是如果当前事务也需要对该记录进行更新操作，则很有可能造成死锁，对于锁定行记录后需要进行更新操作的应用，应该使用 SELECT... FOR UPDATE 方式获得排他锁。

所以在使用共享锁模式下，查询完数据后不要进行更新操作，不然又可能会造成死锁；要更新数据，应该使用排他锁模式。

InnoDB 行锁实现方式

InnoDB 行锁是通过给索引上的索引项加锁来实现的，这一点 MySQL 与 Oracle 不同，后者是通过在数据块中对相应数据行加锁来实现的。

InnoDB 这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB 才使用行级锁，否则，InnoDB 将使用表锁！(这个问题遇到过，由于没加索引，行锁变表锁）

• 在不通过索引条件查询的时候，InnoDB确实使用的是表锁，而不是行锁。
  
  

• 由于 MySQL 的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然是访问不同行的记录，但是如果是使用相同的索引键，是会出现锁冲突的。
  
  

• 当表有多个索引的时候，不同的事务可以使用不同的索引锁定不同的行，另外，不论是使用主键索引、唯一索引或普通索引，InnoDB 都会使用行锁来对数据加锁。
  
  

• 即便在条件中使用了索引字段，但是否使用索引来检索数据是由 MySQL通过判断不同执行计划的代价来决定的，如果 MySQL 认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，这种情况下 InnoDB 将使用表锁，而不是行锁。
  
  

可以通过 explain 执行计划查看是否真正使用了索引。

间隙锁（Next-key 锁）

当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB 会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”，InnoDB 也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁（Next-Key锁）。

举个🌰：

假如 emp 表中只有 101 条记录，其 id 的值从 1~101，下面的 sql： select * from emp where id > 100 for update; 是范围条件查询，InnoDB 不仅会对符合条件的 id 值为101的记录加锁，也会对 id 大于101（并不存在的值）的“间隙”加锁。

结论：

很显然，在使用范围条件检索并锁定记录时，InnoDB 这种加锁机制会阻塞符合条件范围内键值的并发插入，这往往会造成严重的锁等待。因此，在实际应用开发中，尤其是并发插入比较多的应用，我们要尽量优化业务逻辑，尽量使用相等条件来访问更新数据，避免使用范围条件。

关于死锁（DeadLock)

上面知识点说过，MyISAM 表锁是 deadlock free 的，这是因为 MyISAM 总是一次获得所需的全部锁，要么全部满足，要么等待，因此不会出现死锁。

但在 InnoDB 中，除单个 SQL 组成的事务外，锁是逐步或得的，所以 InnoDB发生死锁是可能的。

举个🌰：

也就是我们死锁产生的条件，互相持有资源不释放，还有环形等待。

发生死锁后，InnoDB 一般都能自动检测到，并使一个事务释放锁并回退，另一个事务获得锁，继续完成事务。

但在涉及外部锁，或涉及表锁的情况下，InnoDB并不能完全自动检测到死锁，这需要通过设置锁等待超时参数 innodb_lock_wait_timeout 来解决。

需要说明的是，这个参数并不是只用来解决死锁问题，在并发访问比较高的情况下，如果大量事务因无法立即获得所需的锁而挂起，会占用大量计算机资源，造成严重性能问题，甚至拖跨数据库。我们通过设置合适的锁等待超时阈值，可以避免这种情况发生。

避免死锁的方法

1. 在应用中，如果不同的程序会并发存取多个表，应尽量约定以相同的顺序来访问表，这样可以大大降低产生死锁的机会。在下面的例子中，由于两个 session 访问两个表的顺序不同，发生死锁的机会就非常高！但如果以相同的顺序来访问，死锁就可以避免。
   
   

2. 在程序以批量方式处理数据的时候，如果事先对数据排序，保证每个线程按固定的顺序来处理记录，也可以大大降低出现死锁的可能。
   
   

3. 在事务中，如果要更新记录，应该直接申请足够级别的锁，即排他锁，而不应先申请共享锁，更新时再申请排他锁，因为当用户申请排他锁时，其他事务可能又已经获得了相同记录的共享锁，从而造成锁冲突，甚至死锁。
   
   

4. 在 REPEATABLE-READ 隔离级别下，如果两个线程同时对相同条件记录用 SELECT...FOR UPDATE 加排他锁，在没有符合该条件记录情况下，两个线程都会加锁成功。程序发现记录尚不存在，就试图插入一条新记录，如果两个线程都这么做，就会出现死锁。这种情况下，将隔离级别改成 READ COMMITTED，就可避免问题。
   
   

5. 当隔离级别为 READ COMMITTED 时，如果两个线程都先执行 SELECT...FOR UPDATE，判断是否存在符合条件的记录，如果没有，就插入记录。此时，只有一个线程能插入成功，另一个线程会出现锁等待，当第 1 个线程提交后，第 2 个线程会因主键重出错，但虽然这个线程出错了，却会获得一个排他锁！这时如果有第 3 个线程又来申请排他锁，也会出现死锁。
   
   

对于这种情况，可以直接做插入操作，然后再捕获主键重异常，或者在遇到主键重错误时，总是执行 ROLLBACK 释放获得的排他锁。

小结

这是一篇学习文章，关于 MySQL 的锁机制又多了几分了解，以后在写SQL和排查问题时候，尽量避免死锁和更快定位问题所在。

来源：掘金
原文：http://t.cn/AiCOuqnm
题图：来自谷歌图片搜索 
版权：本文版权归原作者所有
投稿：欢迎投稿，投稿邮箱: editor@hi-linux.com

推荐阅读

• 10 个构建和管理容器的技巧

• 谈谈互联网架构

• 从零开始搭建创业公司后台技术栈

• 浅谈集群、分布式、微服务的异同

• 史上最全的 Linux 运维工程师面试问答录
  

图说生活

这里将分享一些记录生活的日常随拍。

拍攝于：亚特兰蒂斯酒店，迪拜，2019