MySQL锁定机制

Published on 2016 - 06 - 06

MySQL锁定机制简介

数据库锁定机制简单来说就是数据库为了保证数据的一致性而使各种共享资源在被并发访问时变得有序所设计的一种规则。对于任何一种数据库来说都需要有相应的锁定机制,所以MySQL自然也不能例外。MySQL数据库由于其自身架构的特点,存在多种数据存储引擎,每种存储引擎所针对的应用场景特点都不太一样,为了满足各自特定应用场景的需求,每种存储引擎的锁定机制都是为各自所面对的特定场景而优化设计的,所以各存储引擎的锁定机制也有较大区别。

总地来说,MySQL各存储引擎使用了三种类型(级别)的锁定机制:行级锁定、页级锁定和表级锁定。下面我们先分析一下MySQL这三种锁定的特点和各自的优劣所在。

行级锁定(row-level)

行级锁定最大的特点就是锁定对象的颗粒度很小,它是目前各大数据库管理软件所实现的锁定颗粒度最小的。由于锁定颗粒度很小,发生锁定资源争用的概率也最小,能够给予应用程序尽可能大的并发处理能力,从而提高一些需要高并发应用系统的整体性能。

虽然能够在并发处理能力上面有较大的优势,但是行级锁定也因此带来了不少弊端。由于锁定资源的颗粒度很小,所以每次获取锁和释放锁须要的操作就更多,带来的消耗自然也就更大了。此外,行级锁定也最容易发生死锁。

表级锁定(table-level)

和行级锁定相反,表级别的锁定是MySQL各存储引擎中最大颗粒度的锁定机制。该锁定机制最大的特点是实现逻辑非常简单,带来的系统处理成本最小,所以获取锁和释放锁的速度很快。由于表级锁一次会将整个表锁定,所以可以很好地避免困扰我们的死锁问题。

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

页级锁定(page-level)

页级锁定是MySQL中比较独特的一种锁定级别,在其他数据库管理软件中并不太常见。页级锁定的特点是锁定颗粒度介于行级锁定与表级锁之间,所以获取锁定所需要的资源开销,以及所能提供的并发处理能力同样也介于上面二者之间。另外,页级锁定和行级锁定一样,会发生死锁。

综述

在数据库实现资源锁定的过程中,随着锁定资源颗粒度的减小,锁定相同数据量的数据须要消耗的内存数量越来越多,实现算法也会越来越复杂。不过,随着锁定资源颗粒度的减小,应用程序的访问请求遇到锁等待的可能性也会随之降低,系统整体并发度也随之提升。

在MySQL数据库中,使用表级锁定的主要是MyISAM、Memory、CSV等一些非事务性存储引擎,而使用行级锁定的主要是InnoDB存储引擎和NDB Cluster存储引擎,页级锁定主要是BerkeleyDB存储引擎的锁定方式。

MySQL的如此锁定机制主要是由于其历史决定的。在最初,MySQL希望设计一种完全独立于各种存储引擎的锁定机制,而且在早期的MySQL数据库中,MySQL存储引擎(MyISAM和Momery)的设计是建立在“任何表在同一时刻都只允许单个线程对其访问(包括读)”这样的假设之上。但是,随着MySQL的不断完善,系统的不断改进,在MySQL 3.23版本开发的时候,MySQL开发人员不得不修正之前的假设。因为他们发现一个线程正在读某个表的时候,另一个线程是可以对该表进行INSERT操作的,只不过只能INSERT到数据文件的最尾部。这也就是MySQL从3.23版本开始提供的Concurrent Insert。

在出现Concurrent Insert之后,MySQL的开发人员不得不修改之前系统中的锁定实现功能,但仅仅是增加了对Concurrent Insert的支持,并没有改动整体架构。可是在不久之后,随着BerkeleyDB存储引擎的引入,之前的锁定机制遇到了更大的挑战。因为BerkeleyDB存储引擎并没有MyISAM和Memory存储引擎同一时刻只允许单一线程访问某一个表的限制,而是将这个单线程访问限制的颗粒度缩小到了单个Page,这又一次迫使MySQL开发人员不得不再一次修改锁定机制的实现。

新存储引擎的引入,导致锁定机制不能满足要求,让MySQL的人意识到已经不可能实现一种完全独立的满足各种存储引擎要求的锁定实现机制。如果因为锁定机制的拙劣实现而导致存储引擎的整体性能下降,肯定会严重打击存储引擎提供者的积极性,这是MySQL公司非常不愿意看到的,因为这完全不符合MySQL的战略发展思路。所以工程师不得不放弃了设计初衷,在锁定实现机制中作出修改,允许存储引擎改变MySQL通过接口传入的锁定类型而自行决定该怎样锁定数据。

各种锁定机制分析

在整体了解了MySQL锁定机制之后,这一节将详细分析MySQL自身提供的表锁定机制和其他存储引擎实现的行锁定机制,并通过MyISAM存储引擎和InnoDB存储引擎实例演示。

表级锁定

MySQL的表级锁定主要分为两种类型,一种是读锁定,另一种是写锁定。在MySQL中,主要通过4个队列来维护这两种锁定:两个存放当前正在锁定的读和写锁定信息,另外两个存放等待中的读写锁定信息,如下:

  • Current read-lock queue (lock->read)
  • Pending read-lock queue (lock->read_wait)
  • Current write-lock queue (lock->write)
  • Pending write-lock queue (lock->write_wait)

当前持有读锁的所有线程相关信息都能够在Current read-lock queue中找到,队列中的信息按照获取到锁的时间顺序依序存放。而正在等待锁定资源的信息则存放在Pending read-lock queue里,另外两个存放写锁信息的队列也按照上面相同规则来存放信息。

虽然对于使用者来说MySQL展现出来的锁定(表锁定)只有读锁定和写锁定这两种类型,但是在MySQL内部实现中却有多达11种锁定类型,由系统中一个枚举量(thr_lock_type)定义,各值描述如表1所示:

读锁定

一个新的客户端请求在申请获取读锁定资源的时候,需要满足两个条件:

  1. 请求锁定的资源当前没有被写锁定;
  2. 写锁定等待队列(Pending write-lock queue)中没有更高优先级的写锁定在等待。 如果满足了上面两个条件之后,该请求会立即通过,并将相关的信息存入Current read-lock queue中,而如果上面两个条件中的任何一个没有满足,都会被迫进入等待队列Pending read-lock queue中等待资源的释放。

写锁定

当客户端请求写锁定的时候,MySQL首先检查在Current write-lock queue是否已经有锁定相同资源的信息存在。

如果Current write-lock queue没有,则再检查Pending write-lock queue,如果在Pending write-lock queue中找到了,自己也须要进入等待队列并暂停自身线程等待锁定资源。反之,如果Pending write-lock queue为空,则再检测Current read-lock queue,如果有锁定存在,则同样须要进入Pending write-lock queue等待。当然,也可能遇到以下这两种特殊情况:

  1. 请求锁定的类型为WRITE_DELAYED;
  2. 请求锁定的类型为WRITE_CONCURRENT_INSERT或是TL_WRITE_ALLOW_ WRITE,同时Current read lock是READ_NO_INSERT的锁定类型。

当遇到这两种特殊情况的时候,写锁定会立即获得而进入Current write-lock queue中。如果第一次检测时Current write-lock queue中已经有锁定相同资源的写锁定存在,那么就只能进入等待队列等待相应资源锁定的释放了。

读请求和写等待队列中的写锁请求的优先级规则主要是由以下规则决定的:

  1. 除了READ_HIGH_PRIORITY的读锁定之外,Pending write-lock queue中的WRITE写锁定能够阻塞所有其他的读锁定;
  2. READ_HIGH_PRIORITY读锁定的请求能够阻塞所有Pending write-lock queue中的写锁定;
  3. 除了WRITE写锁定之外,Pending write-lock queue中的其他任何写锁定都比读锁定的优先级低。

写锁定出现在Current write-lock queue之后,会阻塞除了以下情况下的所有其他锁定请求:

  1. 在某些存储引擎允许的情况下,可以允许一个WRITE_CONCURRENT_INSERT写锁定请求;
  2. 写锁定为WRITE_ALLOW_WRITE的时候,允许除了WRITE_ONLY之外的所有读和写锁定请求;
  3. 写锁定为WRITE_ALLOW_READ的时候,允许除了READ_NO_INSERT之外的所有读锁定请求;
  4. 写锁定为WRITE_DELAYED的时候,允许除了READ_NO_INSERT之外的所有读锁定请求;
  5. 写锁定为WRITE_CONCURRENT_INSERT的时候,允许除了READ_NO_INSERT之外的所有读锁定请求。

随着MySQL存储引擎的不断发展,目前MySQL自身提供的锁定机制已经没有办法满足需求了,很多存储引擎都在MySQL提供的锁定机制之上做了存储引擎的扩展和改造。

MyISAM存储引擎可以说是对MySQL提供的锁定机制所实现的表级锁定依赖最大的一种存储引擎了,虽然MyISAM存储引擎并没有在自身增加其他的锁定机制,但是为了更好的支持相关特性,MySQL在原有锁定机制的基础上为了支持其Concurrent Insert的特性进行了相应的实现改造。

而其他几种支持事务的存储存储引擎,如InnoDB、NDB Cluster,以及Berkeley DB存储引擎则是让MySQL将锁定的处理直接交给存储引擎来处理,在MySQL中仅持有WRITE_ALLOW_ WRITE类型的锁定。

由于MyISAM存储引擎使用的锁定机制完全是由MySQL提供的表级锁定实现的,所以下面将以MyISAM存储引擎作为示例存储引擎,来实例演示表级锁定的一些基本特性。为了让示例更加直观,我将使用显式给表加锁来演示,如表2所示:

行级锁定

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

InnoDB锁定模式及实现机制

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

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

当一个事务需要给某个资源加锁的时候,如果遇到一个共享锁正锁定着自己需要的资源,它会再加一个共享锁,不过不能加排他锁。但是,如果遇到须要锁定的资源已经被一个排他锁占有,则只能等待该锁定释放资源之后它才能获取锁定资源并添加自己的锁定。而意向锁的作用就是当一个事务在须要获取资源锁定的时候,如果遇到需要的资源已经被排他锁占用,该事务可以在锁定行的表上添加一个合适的意向锁。如果需要一个共享锁,那么就在表上面添加一个意向共享锁。而如果需要的是在某行(或某些行)上面添加一个排他锁,则先在表上面添加一个意向排他锁。意向共享锁可以同时并存多个,但是意向排他锁只能有一个存在。所以,可以说InnoDB的锁定模式实际上可以分为4种:共享锁(S)、排他锁(X)、意向共享锁(IS)和意向排他锁(IX),我们可以通过表3来总结上面这4种所的共存逻辑关系。

虽然InnoDB的锁定机制和Oracle有不少相近的地方,但是两者的实现却是截然不同的。总的来说,Oracle锁定数据主要是通过在须要锁定的某行记录所在的物理Block上的事务槽上面添加锁定信息,而InnoDB的锁定则是通过在指向数据记录的第一个索引键之前和最后一个索引键之后的空域空间标记锁定信息实现的。InnoDB的这种锁定实现方式被称为“NEXT-KEY locking”(间隙锁),因为Query在执行过程中通过范围查找,它会锁定整个范围内所有的索引键值,即使这个键值并不存在。

间隙锁有一个比较致命的弱点,就是在锁定一个范围键值之后,即使某些不存在的键值也会被无辜的锁定,这会造成在锁定的时候无法插入锁定键值范围内的任何数据。在某些场景下这可能会对性能造成很大的危害。而InnoDB给出的解释是为了阻止幻读的出现,所以他们选择了间隙锁。

除了间隙锁给InnoDB带来性能的负面影响,通过索引实现锁定的方式还存在其他几个较大的性能隐患:

  • 当Query无法利用索引的时候,InnoDB会放弃使用行级别锁定而改用表级别的锁定,造成并发性能的降低;
  • 当Quuery使用的索引并不包含所有过滤条件时,数据检索使用到的索引键中的数据可能有部分并不属于该Query的结果集行列,但是也会被锁定,因为间隙锁锁定的是一个范围,而不是具体的索引键;
  • 当Query在使用索引定位数据的时候,如果使用的索引键一样但访问的数据行不同(索引只是过滤条件的一部分),它们一样会被锁定。

InnoDB各事务隔离级别下锁定及死锁

InnoDB实现在ISO/ANSI SQL92规范中所定义的Read UnCommitted、Read Committed、Repeatable Read和Serializable这4种事务隔离级别。同时,为了保证数据在事务中的一致性,它实现了多版本数据访问。

在InnoDB的事务管理和锁定机制中,有专门检测死锁的机制会在系统中产生死锁之后的很短时间内就检测到该死锁的存在。当InnoDB检测到系统中产生了死锁,InnoDB会通过相应的判断来选择产生死锁的两个事务中较小的那个来回滚,而让另外一个较大的事务成功完成。那InnoDB是以什么来为标准判定事务大小的呢?MySQL官方手册中也提到了这个问题,实际上当InnoDB发现死锁时,会计算出两个事务各自插入、更新或删除的数据量,从而来判定两个事务的大小。也就是说哪个事务所改变的记录条数越多,在死锁中就越不会被回滚掉。但是有一点须要注意的就是,当产生死锁的场景中不止涉及InnoDB存储引擎的时候,InnoDB是没办法检测到该死锁的,这时候就只能通过锁定超时限制来解决该死锁了。

InnoDB锁定机制的代码如示例代码1和表4所示:

合理利用锁机制优化MySQL

MyISAM表锁优化建议

对于MyISAM存储引擎,虽然使用表级锁定在锁定实现的过程中比实现行级锁定或页级锁所带来的附加成本都要小,锁定本身所消耗的资源也是最少的。但是由于锁定的颗粒度比较大,所以造成锁定资源的争用情况也会比其他的锁定级别都要多,从而在较大程度上会降低并发处理能力。

所以,在优化MyISAM存储引擎锁定问题的时候,最关键的就是如何让其提高并发度。由于锁定级别是不可能改变的了,所以首先须要尽可能地让锁定的时间变短,然后就是让可能并发进行的操作尽可能地并发。

缩短锁定时间

缩短锁定时间,短短几个字,说起来确实挺容易的,但实际做起来恐怕就并不那么简单了。如何让锁定时间尽可能地短呢?唯一的办法就是让Query的执行时间尽可能地短。

  1. 尽两减少大的复杂Query,将复杂Query分拆成几个小的Query分步进行;
  2. 尽可能地建立足够高效的索引,让数据检索更迅速;
  3. 尽量让MyISAM存储引擎的表只存放必要的信息,控制字段类型;
  4. 利用合适的机会优化MyISAM表数据文件。

分离能并行的操作

说到MyISAM的表锁,而且是读写互相阻塞的表锁,可能有些人会认为在MyISAM存储引擎的表上就只能是完全的串行化,没办法再并行了。大家不要忘记了,MyISAM的存储引擎还有一个非常有用的特性,那就是Concurrent Insert(并发插入)的特性。

MyISAM存储引擎有一个控制是否打开Concurrent Insert功能的参数选项:concurrent_insert,可以设置为0、1或2。三个值的具体说明如下:

  1. concurrent_insert=2,无论MyISAM存储引擎的表数据文件的中间部分是否存在因为删除数据而留下的空闲空间,都允许在数据文件尾部进行Concurrent Insert;
  2. concurrent_insert=1,当MyISAM存储引擎表数据文件中间不存在空闲空间的时候,可以从文件尾部进行Concurrent Insert; (3)concurrent_insert=0,无论MyISAM存储引擎的表数据文件的中间部分是否存在因为删除数据而留下的空闲空间,都不允许Concurrent Insert。

合理利用读写优先级

MySQL的表级锁定对于读和写是有不同优先级设定的,默认情况下是写优先级要大于读。所以,可以根据系统环境的差异决定读与写的优先级。如果系统是一个以读为主,而且要优先保证查询性能的话,可以通过设置系统参数选项low_priority_updates=1,将写的优先级设置为比读低,即告诉MySQL尽量先处理读请求。当然,如果系统须要有限保证数据写入的性能的话,则不用设置low_priority_updates参数了。

这里完全可以利用这个特性,将concurrent_insert参数设置为1,甚至如果数据被删除的可能性很小,如果对暂时性的浪费少量空间并不是特别在乎,将concurrent_insert参数设置为2都可以尝试。当然,数据文件中间留有空域空间,在浪费空间的时候,还会造成查询的时候须要读取更多的数据,所以如果删除量不是很小的话,还是建议将concurrent_insert设置为1。

InnoDB行锁优化建议

InnoDB存储引擎由于实现了行级锁定,虽然在锁定机制的实现方面带来的性能损耗可能比表级锁定要更高一些,但是在整体并发处理能力方面是要远远优于MyISAM的表级锁定的。当系统并发量较高的时候,InnoDB的整体性能和MyISAM相比就会有比较明显的优势了。但是,InnoDB的行级锁定同样也有其脆弱的一面,当我们使用不当的时候,可能会让InnoDB的整体性能表现不仅不比MyISAM高,甚至可能会更差。

要想合理利用InnoDB的行级锁定,做到扬长避短,我们必须做好以下工作:

  1. 尽可能让所有的数据检索都通过索引来完成,从而避免InnoDB因为无法通过索引键加锁而升级为表级锁定;
  2. 合理设计索引,让InnoDB在索引键上面加锁的时候尽可能准确,尽可能地缩小锁定范围,避免造成不必要的锁定而影响其他Query的执行;
  3. 尽可能减少基于范围的数据检索过滤条件,避免因为间隙锁带来的负面影响而锁定了不该锁定的记录;
  4. 尽量控制事务的大小,减少锁定的资源量和锁定时间长度;
  5. 在业务环境允许的情况下,尽量使用较低级别的事务隔离,以减少MySQL因为实现事务隔离级别所带来的附加成本。

由于InnoDB的行级锁定和事务性,所以肯定会产生死锁,下面是一些比较常用的减少死锁产生概率的小建议,读者朋友可以根据各自的业务特点针对性的尝试:

  1. 类似业务模块中,尽可能按照相同的访问顺序来访问,防止产生死锁;
  2. 在同一个事务中,尽可能做到一次锁定需要的所有资源,减少死锁产生的概率;
  3. 对于非常容易产生死锁的业务部分,可以尝试使用升级锁定颗粒度,通过表级锁定来减少死锁产生的概率。

系统锁定争用情况查询

对于两种锁定级别,MySQL内部有两组专门的状态变量记录系统内部锁资源争用情况,我们先看看MySQL实现的表级锁定的争用状态变量,如示例代码2所示:

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

  • Table_locks_immediate:产生表级锁定的次数;
  • Table_locks_waited:出现表级锁定争用而发生等待的次数。

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

对于InnoDB所使用的行级锁定,系统是通过另外一组更为详细的状态变量来记录的,如示例代码3所示:

InnoDB的行级锁定状态变量不仅记录了锁定等待次数,还记录了锁定总时长、每次平均时长,以及最大时长,此外还有一个非累积状态量显示了当前正在等待锁定的数量。对各个状态量的说明如下:

  • Innodb_row_lock_current_waits:当前正在等待锁定的数量;
  • Innodb_row_lock_time:从系统启动到现在锁定总时间长度;
  • Innodb_row_lock_time_avg:每次等待所花平均时间;
  • Innodb_row_lock_time_max:从系统启动到现在等待最长的一次所花的时间;
  • Innodb_row_lock_waits:从系统启动到现在总共等待的次数。

对于这5个状态变量,比较重要的主要是Innodb_row_lock_time_avg(等待平均时长),Innodb_row_lock_waits(等待总次数)及Innodb_row_lock_time(等待总时长)这三项。尤其是当等待次数很高,而且每次等待时长也不小的时候,就须要分析系统中为什么会有如此多的等待,然后根据分析结果着手指定优化计划。

此外,InnoDB除了提供这5个系统状态变量,还提供了其他更为丰富的即时状态信息。可以通过如下方法查看。

  • 通过创建InnoDB Monitor表来打开InnoDB的monitor功能,如示例代码4所示。

  • 然后通过使用“SHOW INNODB STATUS”查看细节信息(由于输出内容太多就不在此记录了)。

可能有读者会问为什么要先创建一个叫innodb_monitor的表呢?因为创建该表实际上就是告诉InnoDB我们要开始监控它的细节状态了,然后InnoDB就会将比较详细的事务及锁定信息记录至MySQL的error log中,以便后面做进一步分析使用。

参考文档