理解MySQL锁和事务，看这篇如何？

作者：微信小助手

发布时间：2019-08-27T18:43:29

“

本文希望帮助读者更加深刻地理解 MySQL 中的锁和事务，从而在业务系统开发过程中更好地优化与数据库的交互。


图片来自 Pexels


锁的分类及特性


数据库锁定机制简单来说，就是数据库为了保证数据的一致性，而使各种共享资源在被并发访问时变得有序所设计的一种规则。

对于任何一种数据库来说都需要有相应的锁定机制，所以 MySQL 自然也不能例外。


MySQL 数据库由于其自身架构的特点，存在多种数据存储引擎，每种存储引擎所针对的应用场景特点都不太一样。


为了满足各自特定应用场景的需求，每种存储引擎的锁定机制都是为各自所面对的特定场景而优化设计，所以各存储引擎的锁定机制也有较大区别。


MySQL 各存储引擎使用了三种类型（级别）的锁定机制：

  • 表级锁定

  • 行级锁定

  • 页级锁定


表级锁定（table-level）


表级别的锁定是 MySQL 各存储引擎中最大颗粒度的锁定机制。该锁定机制最大的特点是实现逻辑非常简单，带来的系统负面影响最小。


所以获取锁和释放锁的速度很快。由于表级锁定一次会将整个表锁定，所以可以很好的避免困扰我们的死锁问题。


当然，锁定颗粒度大所带来最大的负面影响就是出现锁定资源争用的概率也会最高，致使并大度大打折扣。


使用表级锁定的主要是 MyISAM，MEMORY，CSV 等一些非事务性存储引擎。　　


行级锁定（row-level）


行级锁定最大的特点就是锁定对象的颗粒度很小，也是目前各大数据库管理软件所实现的锁定颗粒度最小的。


由于锁定颗粒度很小，所以发生锁定资源争用的概率也最小，能够给予应用程序尽可能大的并发处理能力而提高一些需要高并发应用系统的整体性能。


虽然能够在并发处理能力上面有较大的优势，但是行级锁定也因此带来了不少弊端。


由于锁定资源的颗粒度很小，所以每次获取锁和释放锁需要做的事情也更多，带来的消耗自然也就更大了。


此外，行级锁定也最容易发生死锁。使用行级锁定的主要是 InnoDB 存储引擎。


页级锁定（page-level）


页级锁定是 MySQL 中比较独特的一种锁定级别，在其他数据库管理软件中也并不是太常见。


页级锁定的特点是锁定颗粒度介于行级锁定与表级锁之间，所以获取锁定所需要的资源开销，以及所能提供的并发处理能力也同样是介于上面二者之间。另外，页级锁定和行级锁定一样，会发生死锁。


在数据库实现资源锁定的过程中，随着锁定资源颗粒度的减小，锁定相同数据量的数据所需要消耗的内存数量是越来越多的，实现算法也会越来越复杂。


不过，随着锁定资源颗粒度的减小，应用程序的访问请求遇到锁等待的可能性也会随之降低，系统整体并发度也随之提升。使用页级锁定的主要是 BerkeleyDB 存储引擎。


总的来说，MySQL 这三种锁的特性可大致归纳如下：

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

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

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


适用：从锁的角度来说，表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如 Web 应用。


而行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理（OLTP）系统。　　


表级锁定（MyISAM 举例）


由于 MyISAM 存储引擎使用的锁定机制完全是由 MySQL 提供的表级锁定实现，所以下面我们将以 MyISAM 存储引擎作为示例存储引擎。


MySQL 表级锁的锁模式


MySQL 的表级锁有两种模式：

  • 表共享读锁（Table Read Lock）

  • 表独占写锁（Table Write Lock）


锁模式的兼容性：

  • 对 MyISAM 表的读操作，不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求。

  • 对 MyISAM 表的写操作，则会阻塞其他用户对同一表的读和写操作。

  • MyISAM 表的读操作与写操作之间，以及写操作之间是串行的。当一个线程获得对一个表的写锁后，只有持有锁的线程可以对表进行更新操作。其他线程的读、写操作都会等待，直到锁被释放为止。


总结：表锁，读锁会阻塞写，不会阻塞读。而写锁则会把读写都阻塞。


如何加表锁


MyISAM 在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行更新操作（UPDATE、DELETE、INSERT等）前，会自动给涉及的表加写锁。


这个过程并不需要用户干预，因此，用户一般不需要直接用 LOCK TABLE 命令给 MyISAM 表显式加锁。


显示加锁：

  • 共享读锁：lock table tableName read

  • 独占写锁：lock table tableName write

  • 同时加多锁：lock table t1 write，t2 read

  • 批量解锁：unlock tables


MyISAM 表锁优化建议


对于 MyISAM 存储引擎，虽然使用表级锁定在锁定实现的过程中比实现行级锁定或者页级锁定所带来的附加成本都要小，锁定本身所消耗的资源也是最少。


但是由于锁定的颗粒度比较大，所以造成锁定资源的争用情况也会比其他的锁定级别都要多，从而在较大程度上会降低并发处理能力。


所以，在优化 MyISAM 存储引擎锁定问题的时候，最关键的就是如何让其提高并发度。


由于锁定级别是不可能改变的了，所以我们首先需要尽可能让锁定的时间变短，然后就是让可能并发进行的操作尽可能的并发。


①查询表级锁争用情况


MySQL 内部有两组专门的状态变量记录系统内部锁资源争用情况： 
mysql> show status like 'table%';
+----------------------------+---------+
| Variable_name              | Value   |
+----------------------------+---------+
| Table_locks_immediate      | 100     |
| Table_locks_waited         | 11      |
+----------------------------+---------+

这里有两个状态变量记录 MySQL 内部表级锁定的情况，两个变量说明如下：

  • Table_locks_immediate：产生表级锁定的次数。

  • Table_locks_waited：出现表级锁定争用而发生等待的次数；此值越高则说明存在着越严重的表级锁争用情况。


此外，MyISAM 的读写锁调度是写优先，这也是 MyISAM 不适合做写为主表的存储引擎的原因。


因为写锁后，其他线程不能做任何操作，大量的更新会使查询很难得到锁，从而造成永久阻塞。


两个状态值都是从系统启动后开始记录，出现一次对应的事件则数量加 1。如果这里的 Table_locks_waited 状态值比较高，那么说明系统中表级锁定争用现象比较严重，就需要进一步分析为什么会有较多的锁定资源争用了。


②缩短锁定时间


如何让锁定时间尽可能的短呢？唯一的办法就是让我们的 Query 执行时间尽可能的短：

  • 尽量减少大的复杂 Query，将复杂 Query 分拆成几个小的 Query 分布进行。

  • 尽可能的建立足够高效的索引，让数据检索更迅速。

  • 尽量让 MyISAM 存储引擎的表只存放必要的信息，控制字段类型。

  • 利用合适的机会优化 MyISAM 表数据文件。


③分离能并行的操作


说到 MyISAM 的表锁，而且是读写互相阻塞的表锁，可能有些人会认为在 MyISAM 存储引擎的表上就只能是完全的串行化，没办法再并行了。


大家不要忘记了，MyISAM 的存储引擎还有一个非常有用的特性，那就是 Concurrent Insert（并发插入）的特性。


MyISAM 存储引擎有一个控制是否打开 Concurrent Insert 功能的参数选项：concurrent_insert，可以设置为 0，1 或者 2。


三个值的具体说明如下：

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

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

  • concurrent_insert=0，不允许并发插入。


可以利用 MyISAM 存储引擎的并发插入特性，来解决应用中对同一表查询和插入的锁争用。


例如，将 concurrent_insert 系统变量设为 2，总是允许并发插入；同时，通过定期在系统空闲时段执行 OPTIMIZE TABLE 语句来整理空间碎片，收回因删除记录而产生的中间空洞。


④合理利用读写优先级


MyISAM 存储引擎的读写是互相阻塞的，那么，一个进程请求某个 MyISAM 表的读锁，同时另一个进程也请求同一表的写锁，MySQL 如何处理呢？


答案是写进程先获得锁。不仅如此，即使读请求先到锁等待队列，写请求后到，写锁也会插到读锁请求之前。


这是因为 MySQL 的表级锁定对于读和写是有不同优先级设定的，默认情况下是写优先级要大于读优先级。


所以，如果我们可以根据各自系统环境的差异决定读与写的优先级：


通过执行命令 SET LOW_PRIORITY_UPDATES=1，使该连接读比写的优先级高。


如果我们的系统是一个以读为主，可以设置此参数，如果以写为主，则不用设置。


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


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


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


这里还要强调一点：一些需要长时间运行的查询操作，也会使写进程“饿死”。


因此，应用中应尽量避免出现长时间运行的查询操作，不要总想用一条 SELECT 语句来解决问题，因为这种看似巧妙的 SQL 语句，往往比较复杂，执行时间较长。


在可能的情况下可以通过使用中间表等措施对 SQL 语句做一定的“分解”，使每一步查询都能在较短时间完成，从而减少锁冲突。


如果复杂查询不可避免，应尽量安排在数据库空闲时段执行，比如一些定期统计可以安排在夜间执行。


InnoDB 默认采用行锁，在未使用索引字段查询时升级为表锁。MySQL 这样设计并不是给你挖坑。它有自己的设计目的。


即便你在条件中使用了索引字段，MySQL 会根据自身的执行计划，考虑是否使用索引（所以 explain 命令中会有 possible_key 和 key）。


如果 MySQL 认为全表扫描效率更高，它就不会使用索引，这种情况下 InnoDB 将使用表锁，而不是行锁。


因此，在分析锁冲突时，别忘了检查 SQL 的执行计划，以确认是否真正使用了索引。


关于执行计划，第一种情况：全表更新。事务需要更新大部分或全部数据，且表又比较大。


若使用行锁，会导致事务执行效率低，从而可能造成其他事务长时间锁等待和更多的锁冲突。


第二种情况：多表级联。事务涉及多个表，比较复杂的关联查询，很可能引起死锁，造成大量事务回滚。


这种情况若能一次性锁定事务涉及的表，从而可以避免死锁、减少数据库因事务回滚带来的开销。


行级锁定


行级锁定不是 MySQL 自己实现的锁定方式，而是由其他存储引擎自己所实现的，如广为大家所知的 InnoDB 存储引擎，以及 MySQL 的分布式存储引擎 NDB Cluster 等都是实现了行级锁定。


考虑到行级锁定均由各个存储引擎自行实现，而且具体实现也各有差别，而 InnoDB 是目前事务型存储引擎中使用最为广泛的存储引擎，所以这里我们就主要分析一下 InnoDB 的锁定特性。


InnoDB 锁定模式及实现机制


总的来说，InnoDB 的锁定机制和 Oracle 数据库有不少相似之处。InnoDB 的行级锁定同样分为两种类型，共享锁和排他锁，而在锁定机制的实现过程中为了让行级锁定和表级锁定共存，InnoDB 也同样使用了意向锁（表级锁定）的概念，也就有了意向共享锁和意向排他锁这两种。


当一个事务需要给自己需要的某个资源加锁的时候，如果遇到一个共享锁正锁定着自己需要的资源的时候，自己可以再加一个共享锁，不过不能加排他锁。


但是，如果遇到自己需要锁定的资源已经被一个排他锁占有之后，则只能等待该锁定释放资源之后自己才能获取锁定资源并添加自己的锁定。


而意向锁的作用就是当一个事务在需要获取资源锁定的时候，如果遇到自己需要的资源已经被排他锁占用的时候，该事务需要在锁定行的表上面添加一个合适的意向锁。


如果自己需要一个共享锁，那么就在表上面添加一个意向共享锁。而如果自己需要的是某行（或者某些行）上面添加一个排他锁的话，则先在表上面添加一个意向排他锁。


意向共享锁可以同时并存多个，但是意向排他锁同时只能有一个存在。


所以，可以说 InnoDB 的锁定模式实际上可以分为四种：

  • 共享锁（S）

  • 排他锁（X）

  • 意向共享锁（IS）

  • 意向排他锁（IX）


我们可以通过以下表格来总结上面这四种锁的共存逻辑关系：

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


意向锁是 InnoDB 自动加的，不需用户干预：

  • 对于 UPDATE、DELETE 和 INSERT 语句，InnoDB 会自动给涉及数据集加排他锁（X)。

  • 对于普通 SELECT 语句，InnoDB 不会加任何锁。


事务可以通过以下语句显示给记录集加共享锁或排他锁： 
共享锁（S）：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE
排他锁（X)：