MySQL性能调优与架构设计(六)—— MySQL数据库锁定机制

前言

1. 在说锁定机制之前，有必要理解下并发与并行的基本概念。
2. 并发是指一台处理器上同时处理多个任务，并行是指多个处理器同时处理多个任务，如hadoop分布式集群。
3. 通俗的讲，并发就是不同线程同时干一件事情，并行就是不同线程同时干不同的事情。
4. 所以并发编程的目标是充分的利用处理器的每一个核，以达到最高的性能。
5. 那么并发必然会牵扯到应用系统中资源的竞争。
6. 详细的关于并行与并发可以参考一篇文章来理解：https://blog.csdn.net/qq_3329...
7. 在高并发的情况下，为了保证数据的完整一致性，任何一个数据库都有锁定机制。
8. 锁定机制的优劣直接影响了一个数据库的并发处理能力和性能。
9. 本章将对mysql中两种使用最为频繁的存储引擎MyISAM和InnoDB各自的锁定机制进行较为详细的分析。

MySQL锁定机制简介

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

行级锁定（row-level）

1. 行级锁，一般是指排它锁，即被锁定行不可进行修改、删除，只可以被其他会话select。
2. 排他锁又称为写锁，简称X锁，顾名思义，排他锁就是不能与其他锁并存，如一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的其他锁。
3. 行级锁定最大的特点就是锁定对象的颗粒度很小，也是目前各大数据库管理软件所实现的锁定颗粒度最小的。
4. 由于锁定颗粒度很小，所以发生锁定资源竞争的概率也小，能够给予应用程序尽可能大的并发处理能力提高一些需要高并发应用系统的整体性能。
5. 虽然在并发处理能力上面有较大的优势，但是行级锁定也因此带来了不少弊端。
6. 由于锁定资源的颗粒度很小，所以每次获取锁和释放锁需要做的事情也很多，带来的消耗自然也就更大了。
7. 此外，行级锁定也最容易发生死锁。

表级锁定（table-level）

1. 表级锁，直接锁定整张表，在你锁定期间，其他进程无法对该表进行写操作。如果你是写锁，则其他进程则读也不允许。
2. 和行级锁定相反，表级别的锁定是mysql各存储引擎中最大颗粒度的锁定机制。
3. 该锁定机制最大的特点就是实现逻辑非常简单，带来的系统负面影响最小。所以获取锁和释放锁的速度很快。
4. 由于表级锁一次会将整个表锁定，所以可以很好的避免困扰我们的死锁问题。
5. 当然，锁定颗粒度大带来的负面影响就是出现资源争用的概率也会很高，致使并发度大打折扣。

页级锁定（page-level）

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

小结

1. 在数据库实现资源锁定的过程中，随着锁定资源颗粒度的减小，锁定相同数据量的数据所需要消耗的内存数量是越来越多，实现算法也会越来越复杂。
2. 随着锁定资源颗粒度的减小，应用程序的访问请求遇到锁等待的可能性也会随之降低，系统整体并发度页随之提升。
3. 在MySQL中，使用表级锁定的是MyISAM、MEmory、CSv等一些非事务型存储引擎，而使用行级锁的主要是InnoDB存储引擎和NDB Cluster存储引擎，页级锁定主要是BerkeleyDB存储引擎的锁定方式。

各种锁定机制分析

表级锁定

1. MySQL的表级锁定主要分为两种类型，一种是写锁定，一种是读锁定。
2. 在MySQL中，主要是通过四个队列来维护这两种锁定，两个存放正在锁定中的读写锁定信息，另两个存放等待中的读写锁定信息：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)
3. 当前持有读锁的所有线程的相关信息都能够在Current read-lock queue (lock->read) 中找到，队列中的信息按照获取到锁的时间依次存放。而正在等待读锁的信息则存放在Pending read-lock queue 里面，另外两个存放写锁信息的队列也按照相同的规则来存放信息。

表级读、写锁定

1. 读锁定：

   （1）一个新的客户端请求在申请读锁资源的时候，需要满足两个条件：
       【1】请求锁定的资源没有写锁定
       【2】写锁定等待队列Pending write-lock queue中没有更高优先级的写锁定等待
   （2）如果满足来上述两个条件之后，该请求会立即通过，并将相关的信息存入Current read-lock queue 中，而如果上面两个条件中有一个不满足，都会被迫进入等待队列Pending read-lock queue中等待资源的释放。

2. 写锁定：

   （1）当客户端请求写锁定的时候，MySQL首先会检查Current write-lock queue中是否已经有锁定相同资源的信息存在。
   （2）如果Current write-lock queue中没有，则再检查Pending write-lock queue；
   （3）如果Pending write-lock queue中找到了，自己也需要进入等待队列并暂停自身线程等待锁定资源。
   （4）反之，如果Pending write-lock queue为空，则再检测Current read-lock queue，如果有锁定存在，则同样需要进入Pending write-lock queue等待。
   （5）有两种特殊情况，会立即获得锁而进入Current write-lock queue中：
       【1】请求锁定的类型为WRITE_DELAYED；
       【2】请求锁定类型为WRITE_CONCURRENT_INSERT或者TL_WRITE_ALLOW_WRITE，同时Current read lock是READ_NO_INSERT的锁定类型。 
   （6）如果一开始就检测到Current write-lock queue中已经存在了锁定相同资源的写锁定存在，那么就只能进入等待队列等待相应资源锁定的释放。

3. 虽然对于我们这些使用者来说，mysql展现出来的只有读锁和写锁，实际上在MySQL内部实现中却有多达11种锁定类型，有系统中一个枚举量（thr_lock_type）定义。具体的类型可以另外查询。

4. 读请求和写等待队列中的写锁请求的优先级规则主要为以下规则决定：

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

5. 写锁定出现在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之外的所有读锁定请求

行级锁定- Innodb锁定模式及实现机制

1. Innodb的行级锁定分为两种类型，共享锁和排他锁。
2. 在锁定机制的实现过程中为了让行级锁定和表级锁定共存，Innodb也同样使用了意向锁（表级锁定）的概念，也就有了意向共享锁和意向排他锁这两种。
3. 当一个事务需要给自己需要的某个资源加锁的时候，如果遇到一个共享锁正锁定着自己需要的资源，自己可以再加一个共享锁，不过不能加排他锁。
4. 但是，如果遇到自己需要锁定的资源已经被一个排他锁占有之后，则只能等待该锁定释放资源之后自己才能获取锁定资源并添加自己的锁定。
5. 而意向锁的作用就是当一个事务在需要获取资源锁定的时候，如果遇到自己需要的资源已经被排他锁占用的时候，该事务可以需要锁定行的表上面添加一个合适的意向锁。
6. 如果自己需要一个共享锁，那么就在表上面添加一个意向共享锁。
7. 而如果自己需要的是某行上面添加一个排他锁的花话，则先在表上面添加一个意向排他锁。
8. 意向共享锁可以同时并存多个，但是意向排他锁同时只能有一个存在。
9. 所以，可以说Innodb的锁定模式实际上可以分为四种：共享锁（S），排他锁（X），意向共享锁（IS），意向排他锁（IX）。我们可以通过以下表格来总结上面这四种的共存逻辑关系：
   
10. 虽然Innodb的锁定机制和Oracle有不少相近的地方，但是两者的实现却截然不同。
11. 总的来说，Oracle锁定数据是通过需要锁定的某行记录所在的物理block上的事务槽上表级锁定信息，而Innodb的锁定则是通过在指定数据记录的第一个索引键之前和最后一个索引键之后的空域空间上标记锁定信息而实现的。
12. Innodb的这种锁定实现方式被称为“NEXT-KEY locking”（间隙锁），因为query执行过程中通过范围查找的话，他会锁定整个范围内所有的索引键值，即使这个键值并不存在。
13. 间隙锁有一个致命弱点，就是当锁定一个范围键值之后，即使某些不存在的键值也会被锁定，而造成在锁定的时候无法插入锁定建值范围内的任何数据。在某些场景下这可能会对性能造成很大的危害，而Innodb给出的解释是为了阻止幻读的出现，所以他们选择间隙锁来实现锁定。

14. 除了间隙锁给Innodb带来性能的负面影响之外，通过索引实现锁定的方式还存在其他几个较大的性能隐患：

    （1）当query无法利用索引的时候，Innodb会放弃使用行级锁定而该用表级锁定，造成并发性能的降低；
    （2）当query使用的索引并不包含所有过滤条件的时候，数据检索使用到的索引键所指向的数据可能有部分并不属于该query的结果集的行列，但是也会被锁定，因为间隙锁锁定的是一个范围，而不是具体的索引键；
    （3）当query使用索引定位数据的时候，如果使用的索引键一样但访问的数据行不同的时候（索引只是过滤条件的一部分），一样会被锁定。

行级锁定 - Innodb各事务隔离级别下锁定及死锁

1. Innodb实现的在ISO／ANSI SQL92规范中锁定义的Read UnCommited，Read Commited，Repeatable Read和Serializable这四种事务隔离级别。同时，为了保证数据在事务中的一致性，实现了多版本数据访问。
2. 之前我们已经介绍过，行级锁定肯定会带来死锁问题，Innodb也不例外。

3. 在Innodb中当系统检测到死锁产生之后是如何来处理的？

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

合理利用锁机制优化MySQL

MyISAM表锁优化建议

1. 对于MyISAM存储引擎，虽然使用表级锁定在锁定实现的过程中比实现行级锁定或者页级锁定所带来的附加成本都要小，锁定本身所消耗的资源也是最少，但是，由于锁定的颗粒度较大，所以造成锁定资源的争用情况也会比其他的锁定级别要多，从而在较大程度上会降低并发处理能力。
2. 所以，在优化MyISAM存储引擎锁定问题的时候，最关键的是如何让其提高并发度。
3. 由于锁定级别是不可能改变的了，所以我们首先需要尽可能让锁定的时间变短，然后就是让可能并发进行的操作尽可能的并发。

4. 缩短锁定时间

   （1）缩短锁定时间，说起来容易，实际做起来不简单。如何让锁定时间尽可能的缩短呢？唯一的办法就是让我们的query执行时间尽可能的短
   （2）尽量减少大的复杂query，将复杂query分拆成几个小的query分布进行中
   （3）尽可能的建立足够高效的索引，让数据检索更迅速
   （4）尽量让MyISAM存储引擎的表只存放必要的信息，控制字段类型
   （5）利用合适的机会优化MyISAM表数据文件

5. 分离能并行的操作

   （1）MyISAM表锁是读写互相阻塞的表锁，所以可能有些人会认为在MyISAM存储引擎的表上就只能完全的串行化，没办法并行了。
   （2）但是不要忘记，MyISAM的存储引擎还有一个非常有用的特性，那就是个Concurrent Insert（并发插入）的特性。
   （3）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。 

6. 合理利用读写优先级

   （1）在本章各种锁定分析一节中我们了解到，mysql的表级锁定对于读和写是有不同优先级的，默认情况下是写优先级大于读优先级。
   （2）所以我们可以根据各自系统环境的差异决定读与写的优先级。
   （3）如果我们的系统是一个以读为主，而且要优先保证查询性能的话，我们可以通过设置系统参数选项low_priority_updates=1，将写的优先级设置为比读优先级低，这样mysql就会尽量先处理读请求。
   （4）这里我们完全可以利用这个特性，将concurrent_insert参数设置为1，甚至如果数据被删除的可能性很小的时候，如果对暂时性的浪费少量空间并不是特别的在乎的话，将concurrent_insert参数设置为2都可以尝试。当然，数据文件中间留有空域空间，在浪费空间的时候，还会造成在查询的时候需要读取更多的数据，所以如果删除量不是很小的话，还是建议将concurrent_insert设置为1更为合适。 

InnoDB行级锁优化建议

1. InnoDB存储引擎由于实现了行级锁，虽然在锁定机制的实现方面所带来的性能损耗可能比表级锁定会更高一些，但是在整体并发处理能力方面要远远由于MyISAM的表级锁定。
2. 当系统并发量较高的时候，Innodb的整体性能和MyISAM相比就会有比较明显的优势了。

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

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

4. 由于InnoDB的行级锁定和事务性，所以肯定会产生死锁，建议：

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

系统锁定争用情况查询

1. 对于两种锁定级别，MySQL内部有两组专门的状态变量记录系统内部锁资源争用情况，我们先看看MySQL实现的表级锁定的争用状态变量：

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

   （1）Table_locks_immediate：产生表级锁定的次数； 
   （2）Table_locks_waited：出现表级锁定争用而发生等待的次数； 

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

4. 对于Innodb所使用的行级锁定，系统中是通过另外一组更为详细的状态变量来记录的，如下：

   mysql> show status like 'innodb_row_lock%';

   

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

   （1）Innodb_row_lock_current_waits：当前正在等待锁定的数量；
   （2）Innodb_row_lock_time：从系统启动到现在锁定总时间长度；
   （3）Innodb_row_lock_time_avg：每次等待所花平均时间； 
   （4）Innodb_row_lock_time_max：从系统启动到现在等待最常的一次所花的时间；
   （5）Innodb_row_lock_waits：系统启动后到现在总共等待的次数；

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

7. 此外，Innodb出了提供这五个系统状态变量之外，还提供的其他更为丰富的即时状态信息供我们分析使用。可以通过如下方法查看：

   （1）通过创建Innodb Monitor表来打开Innodb的monitor功能：
        mysql> create table innodb_monitor(a int) engine=innodb;
   
        Query OK, 0 rows affected (0.07 sec) 
   （2）然后通过使用“SHOW INNODB STATUS”查看细节信息（由于输出内容太多就不在此记录了）； 可能会有读者朋友问为什么要先创建一个叫innodb_monitor的表呢？因为创建该表实际上就是告诉Innodb我们开始要监控他的细节状态了，然后 Innodb就会将比较详细的事务以及锁定信息记录进入MySQL的error log中，以便我们后面做进一步分析使用。 

参考链接

https://www.cnblogs.com/jesse...