读书笔记: 数据库与MySql(3)

本文将主要介绍MySql数据库的锁机制，内容主要出自《MySql性能调优与架构优化》。

---

MySql数据库锁定机制

为了保证数据的一致性，任何一个数据库都存在锁定机制。锁定机制的优劣直接影响到一个数据库系统的并发处理能力和性能，所以锁定机制的实现也就成为了各种数据库的核心技术之一。

MySql锁定机制简介

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

MySql数据库由于其自身架构的特点，存在多种数据存储引擎，每种存储引擎所针对的应用场景特点都不太一样，为了满足各自特定应用场景的需求，每种存储引擎的锁定机制都是为各自所面对的特定场景而优化设计的，所以各引擎的锁定机制也有较大区别。

总的来说，MySql各存储引擎使用了三种类型（级别）的锁定机制：行级锁定，页级锁定和表级锁定。

行级锁定（row-level）

行级锁定的粒度最小，也是目前各大数据库软件所实现的锁定粒度最小的。由于锁定粒度小，发生锁争用的概率也小，能够给予应用程序尽可能大的并发处理能力。有优点自然有缺点：由于锁定资源的粒度很小，所以每次获取锁和释放锁需要做的事情也更多，带来的消耗更大。此外，也最容易发生死锁。

表级锁定（table-level）

表级锁定是MySql各存储引擎中最大粒度的锁定机制。特点是实现逻辑非常简单，带来的系统消耗也最小，加锁和释放锁速度很快。由于表级锁定一次锁定整个表，所以可以避免死锁。但是锁定粒度大会导致锁定资源争用的概率高，影响数据库的并发处理能力。

页级锁定（page-level）

页级锁定是MySql中比较独特的一种锁定级别，在其他数据库管理软件中也不是太常见。锁定粒度介于行级锁定和表级锁定之间，相应地，并发处理能力也在两者之间，也会发生死锁。

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

在MySql中，使用表级锁定的主要是MyISAM，Memory和CSV等一些非事务性存储引擎，使用行级锁定的主要是Innodb和NDB Cluster存储引擎，使用页级锁定的主要是BerkeleyDb存储引擎。

锁定机制分析

MySql最初的设想是，提供一种独立于各种引擎之外的锁定机制，其初始设计就是表锁定机制。当然，后来随着各种不同引擎的接入，发现独立的锁定机制是行不通的，于是就开放给各个引擎自己来实现了。

表级锁定

表级锁定主要分为两种类型，一种是读锁定，一种是写锁定。在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)

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

一个新的客户端请求在申请获取读锁定资源的时候，需要满足以下条件：
（1）请求锁定的资源当前没有被写锁定；
（2）写锁定等待队列中没有更高优先级的写锁定等待；
如果满足了上面两个条件之后，该请求会被立即通过，并将相关的信息存入Current read-lock queue中，不满足就会被迫进入读锁定等待队列。

一个新的客户端请求在申请获取写锁定资源的时候，需要满足以下条件：
（1）请求锁定的资源既没有被写锁定，也没有被读锁定；
（2）请求锁定的资源不在写锁定等待队列中。
当满足这两个条件时，请求会被立即通过，相关信息将存入Current write-lock queue中，否则进入写锁定等待队列。

事实上，mysql内部有许多其他的锁定方式，并不仅仅只有单纯的共享读锁定和互斥写锁定。在某些特殊的锁定方式下，申请获取写锁定也会被立即通过。这是因为mysql内部知道该如何处理，而不产生并发错误。

MyISAM引擎使用的锁定机制完全是MySql提供的表级锁定实现。

行级锁定

行级锁定不是MySql自己实现的锁定方式，而是由其他存储引擎自己实现的，如Innodb存储引擎，以及MySql分布式存储引擎NDB Cluster等都实现了行级锁定。

Innodb是目前事务性存储引擎中使用最为广泛的存储引擎，所以这里主要分析一下Innodb的锁定特性。

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

共享锁(S)和排他锁(X)也叫读共享锁和写互斥锁，读与读之间是共享的，其他均是互斥的。当一个事务以加锁的方式读一行时，另一个事务不能对这一行进行更改。当然，因为在很多情况下，MySql中的读都是通过MVCC来实现的快照读，不需要加锁，所以读的同时并不影响写操作。具体可参考博主另一篇介绍隔离性的文章的最后一节。

意向锁
意向锁是表级别的锁（只是表级别锁，但不是表锁，注意概念），意向锁是在行锁的基础上产生的，表示事务有意对表中的某些行加锁。
意向锁分为意向共享锁(IS)和意向排他锁(IX)。事务要获得某些行的共享锁(S锁)之前，必须先获得表的IS锁，要获得排他锁(X锁)之前，必须先获得表的IX锁。

在MySql官网上有这样一张表：

	共享锁(S)	排他锁(X)	意向共享锁(IS)	意向排他锁(IX)
共享锁(S)	兼容	冲突	兼容	冲突
排他锁(X)	冲突	冲突	冲突	冲突
意向共享锁(IS)	兼容	冲突	兼容	兼容
意向排他锁(IX)	冲突	冲突	兼容	兼容

S锁和X锁之间的共存关系很好理解，只有读-读是可以共存的。而意向锁与行锁之间的共存关系怎么理解呢？如排他锁X与意向排他锁IX之间的互斥关系。下面举例说明：

假设有两个事务A和B，A通过唯一索引列来更新一行数据，那么需要在更新的行上加排他锁，而由于在加排他锁之前系统会自动加上意向排他锁，因此A更新这行数据时会产生IX锁和X锁，此时B事务通过唯一索引来更新另一行数据，同理会产生IX锁和X锁。如果按表中的共存关系，是不是说B事务在加IX锁时会阻塞呢？并不是这样。表中IX和X互斥，针对的是同一行。意向锁是在行锁的基础上产生的，与行锁有很强的关联关系。如果事务B的IX锁是针对事务A锁住的同一行而产生的（也就是说B后续的X锁是为了跟A锁住同一行），这种情况下才是互斥的，否则是没有问题的。

这与我们直觉中不同事务可以同时更新表中不同的行，但不能同时更新同一行是相符合的。为了理解起来方便，可以直接忽略掉意向锁，分析S锁和X锁即可。

意向锁的作用
从并发的角度来看，行锁似乎已经满足了读和写的并发操作要求。那么为什么还需要意向锁呢？意向锁是为了解决表锁和行锁并存的问题而出现的。

注意上面的栗子中，两个事务更新数据行时，是根据唯一索引列来检索的。事实上，在MySql中，行锁是加在索引上的，如果没有索引，就不会使用行锁，而是使用表锁。假如在事务A和事务B根据索引来更新不同行的时候，此时又有一个事务C来更新另外一行，无索引情况下加表锁。那么InnoDB怎么判断表锁是否可以加锁成功？很简单，通过意向锁判断即可。事务A和事务B对数据行的更新会产生两个IX锁，事务C发现表上已经有了IX锁，说明已经或者即将有X锁来对表中的行加锁，于是表写锁就被阻塞，不能成功加锁。如果没有意向锁，那就需要一行一行的检查，看表中是否存在行锁，然后才能判断表锁能不能加锁成功，这样的话就比较耗费时间和性能了。

行锁的实现方式
与oracle中行锁直接锁定物理记录不同，Innodb的锁定是通过在指向数据记录的第一个索引键之前和最后一个索引键之后的空域空间上标记锁定信息而实现的，这种锁定实现方式被称为“NEXT-KEY locking”（间隙锁）。

在Query执行过程中通过范围查找的话，会锁定整个范围内所有的索引键值，即使这个键值并不存在。间隙锁有个致命的弱点，就是当锁定一个范围键值之后，即使某些不存在的键值也会被无辜的锁定，而造成在锁定的时候无法插入锁定键值范围内的任何数据。在某些场景下这可能会对性能造成很大的危害。而Innodb给出的解释是为了阻止幻读的出现，所以选择用间隙锁来实现锁定。

通过索引实现锁定的方式还存在其他几个较大的性能隐患：

• 当Query无法利用索引的时候，Innodb会放弃使用行级锁定而改用表级锁定，造成并发性能的降低。
• 当Query使用的索引并不包含所有过滤条件时，数据检索使用的索引键所执行的数据可能有部分并不属于该Query的结果集的行列，但是也会被锁定。如 select … from table where id < 10 and name = ‘test’ lock in share mode，虽然最终结果集可能只有一条数据，但却会锁住id<10这个范围的全部索引。
• 当Query在使用索引定位数据的时候，如果使用的索引键一样但访问的数据行不同的时候（索引只是过滤条件的一部分），一样会被锁定。

Innodb实现了未提交读，提交读，可重复读和序列化四种事务隔离级别。同时为了保证数据在事务中的一致性，实现了多版本数据访问，具体可参考博主另一篇介绍隔离性的文章。

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

锁优化与检测

Innodb 行锁优化建议

Innodb存储引擎由于实现了行级锁定，虽然在锁定机制方面所带来的性能损耗可能比表级锁定要高一些，但是在整体并发处理能力方面要远远优于MyISAM的表级锁定的。当系统的并发量较高的时候，Innodb的整体性能与MyISAM相比就会有比较明显的优势了。但是Innodb的行级锁定也有其脆弱的一面，当使用不当的时候可能会导致Innodb性能大大降低。

要想合理利用Innodb的行级锁定，需要做好以下工作：

a) 尽可能让所有的数据检索都通过索引来完成，从而避免Innodb因为无法通过索引键加锁而升级为表级锁定。

b) 合理设计索引，让Innodb在索引键上面加锁的时候尽可能准确，尽可能缩小锁定范围，避免造成不必要的锁定而影响其他query的执行。

c) 尽可能减少基于范围的数据检索过滤条件，避免因为间隙带来的负面影响而锁定了不该锁定的记录。

d) 尽量控制事务的大小，减少锁定的资源量和锁定时间长度。

e) 在业务环境允许的情况下，尽量使用较低界别的事务隔离，以减少MySql因为事先事务隔离级别而带来的附加成本；

针对死锁，有以下建议：

a) 类似业务模块中，尽可能按照相同的访问顺序来访问，防止产生死锁。

b) 在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生效率。

c) 对于非常容易产生死锁的业务部分，可以尝试使用升级锁定粒度，通过表级锁定来减少死锁产生的概率。

系统锁定争用情况查询

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

mysql> show status like 'table%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Table_locks_immediate | 100 |
| Table_locks_waited | 0 |
+-----------------------+-------+

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

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

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

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

mysql> show status like 'innodb_row_lock%';
+-------------------------------+--------+
| Variable_name | Value |
+-------------------------------+--------+
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 599255 |
| Innodb_row_lock_time_avg | 213 |
| Innodb_row_lock_time_max | 6878 |
| Innodb_row_lock_waits | 2810 |

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

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

• 通过创建Innodb Monitor 表来打开Innodb 的monitor 功能：
  mysql> create table innodb_monitor(a int) engine=innodb;
• 然后通过使用“SHOW INNODB STATUS”查看细节信息；

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