Mysql数据库锁定机制详细介绍
前言
为了保证数据的一致完整性，任何一个数据库都存在锁定机制。锁定机制的优劣直接应想到一个数据库系统的并发处理能力和性能，所以锁定机制的实现也就成为了各种数据库的核心技术之一。本章将对MySQL中两种使用最为频繁的存储引擎MyISAM和Innodb各自的锁定机制进行较为详细的分析。
MySQL锁定机制简介
数据库锁定机制简单来说就是数据库为了保证数据的一致性而使各种共享资源在被并发访问访问变得有序所设计的一种规则。对于任何一种数据库来说都需要有相应的锁定机制，所以MySQL自然也不能例外。MySQL数据库由于其自身架构的特点，存在多种数据存储引擎，每种存储引擎所针对的应用场景特点都不太一样，为了满足各自特定应用场景的需求，每种存储引擎的锁定机制都是为各自所面对的特定场景而优化设计，所以各存储引擎的锁定机制也有较大区别。
总的来说，MySQL各存储引擎使用了三种类型（级别）的锁定机制：行级锁定，页级锁定和表级锁定。下面我们先分析一下MySQL这三种锁定的特点和各自的优劣所在。
行级锁定（row-level）
行级锁定最大的特点就是锁定对象的颗粒度很小，也是目前各大数据库管理软件所实现的锁定颗粒度最小的。由于锁定颗粒度很小，所以发生锁定资源争用的概率也最小，能够给予应用程序尽可能大的并发处理能力而提高一些需要高并发应用系统的整体性能。
虽然能够在并发处理能力上面有较大的优势，但是行级锁定也因此带来了不少弊端。由于锁定资源的颗粒度很小，所以每次获取锁和释放锁需要做的事情也更多，带来的消耗自然也就更大了。此外，行级锁定也最容易发生死锁。
表级锁定（table-level）
和行级锁定相反，表级别的锁定是MySQL各存储引擎中最大颗粒度的锁定机制。该锁定机制最大的特点是实现逻辑非常简单，带来的系统负面影响最小。所以获取锁和释放锁的速度很快。由于表级锁一次会将整个表锁定，所以可以很好的避免困扰我们的死锁问题。
当然，锁定颗粒度大所带来最大的负面影响就是出现锁定资源争用的概率也会最高，致使并大度大打折扣。
页级锁定（page-level）
页级锁定是MySQL中比较独特的一种锁定级别，在其他数据库管理软件中也并不是太常见。页级锁定的特点是锁定颗粒度介于行级锁定与表级锁之间，所以获取锁定所需要的资源开销，以及所能提供的并发处理能力也同样是介于上面二者之间。另外，页级锁定和行级锁定一样，会发生死锁。
在数据库实现资源锁定的过程中，随着锁定资源颗粒度的减小，锁定相同数据量的数据所需要消耗的内存数量是越来越多的，实现算法也会越来越复杂。不过，随着锁定资源颗粒度的减小，应用程序的访问请求遇到锁等待的可能性也会随之降低，系统整体并发度也随之提升。
在MySQL数据库中，使用表级锁定的主要是MyISAM，Memory，CSV等一些非事务性存储引擎，而使用行级锁定的主要是Innodb存储引擎和NDBCluster存储引擎，页级锁定主要是BerkeleyDB存储引擎的锁定方式。
MySQL的如此的锁定机制主要是由于其最初的历史所决定的。在最初，MySQL希望设计一种完全独立于各种存储引擎的锁定机制，而且在早期的MySQL数据库中，MySQL的存储引擎（MyISAM和Momery）的设计是建立在“任何表在同一时刻都只允许单个线程对其访问（包括读）”这样的假设之上。但是，随着MySQL的不断完善，系统的不断改进，在MySQL3.23版本开发的时候，MySQL开发人员不得不修正之前的假设。因为他们发现一个线程正在读某个表的时候，另一个线程是可以对该表进行insert操作的，只不过只能INSERT到数据文件的最尾部。这也就是从MySQL从3.23版本开始提供的我们所说的ConcurrentInsert。
当出现ConcurrentInsert之后，MySQL的开发人员不得不修改之前系统中的锁定实现功能，但是仅仅只是增加了对ConcurrentInsert的支持，并没有改动整体架构。可是在不久之后，随着BerkeleyDB存储引擎的引入，之前的锁定机制遇到了更大的挑战。因为BerkeleyDB存储引擎并没有MyISAM和Memory存储引擎同一时刻只允许单一线程访问某一个表的限制，而是将这个单线程访问限制的颗粒度缩小到了单个page，这又一次迫使MySQL开发人员不得不再一次修改锁定机制的实现。
由于新的存储引擎的引入，导致锁定机制不能满足要求，让MySQL的人意识到已经不可能实现一种完全独立的满足各种存储引擎要求的锁定实现机制。如果因为锁定机制的拙劣实现而导致存储引擎的整体性能的下降，肯定会严重打击存储引擎提供者的积极性，这是MySQL公司非常不愿意看到的，因为这完全不符合MySQL的战略发展思路。所以工程师们不得不放弃了最初的设计初衷，在锁定实现机制中作出修改，允许存储引擎自己改变MySQL通过接口传入的锁定类型而自行决定该怎样锁定数据。
表级锁定
MySQL的表级锁定主要分为两种类型，一种是读锁定，另一种是写锁定。在MySQL中，主要通过四个队列来维护这两种锁定：两个存放当前正在锁定中的读和写锁定信息，另外两个存放等待中的读写锁定信息，如下：
Currentread-lockqueue(lock->read) Pendingread-lockqueue(lock->read_wait) Currentwrite-lockqueue(lock->write) Pendingwrite-lockqueue(lock->write_wait)
当前持有读锁的所有线程的相关信息都能够在Currentread-lockqueue中找到，队列中的信息按照获取到锁的时间依序存放。而正在等待锁定资源的信息则存放在Pendingread-lockqueue里面，另外两个存放写锁信息的队列也按照上面相同规则来存放信息。
虽然对于我们这些使用者来说MySQL展现出来的锁定（表锁定）只有读锁定和写锁定这两种类型，但是在MySQL内部实现中却有多达11种锁定类型，由系统中一个枚举量（thr_lock_type）定义，各值描述如下：
锁定类型 说明 IGNORE 当发生锁请求的时候内部交互使用，在锁定结构和队列中并不会有任何信息存储 UNLOCK 释放锁定请求的交互用所类型 READ 普通读锁定 WRITE 普通写锁定 READ_WITH_SHARED_LOCKS 在Innodb中使用到，由如下方式产生如：SELECT...LOCKINSHAREMODE READ_HIGH_PRIORITY 高优先级读锁定 READ_NO_INSERT 不允许ConcurentInsert的锁定 WRITE_ALLOW_WRITE 这个类型实际上就是当由存储引擎自行处理锁定的时候，mysqld允许其他的线程再获取读或者写锁定，因为即使资源冲突，存储引擎自己也会知道怎么来处理 WRITE_ALLOW_READ 这种锁定发生在对表做DDL（ALTERTABLE...）的时候，MySQL可以允许其他线程获取读锁定，因为MySQL是通过重建整个表然后再RENAME而实现的该功能，所在整个过程原表仍然可以提供读服务 WRITE_CONCURRENT_INSERT 正在进行ConcurentInsert时候所使用的锁定方式，该锁定进行的时候，除了READ_NO_INSERT之外的其他任何读锁定请求都不会被阻塞 WRITE_DELAYED 在使用INSERTDELAYED时候的锁定类型 WRITE_LOW_PRIORITY 显示声明的低级别锁定方式，通过设置LOW_PRIORITY_UPDAT=1而产生 WRITE_ONLY 当在操作过程中某个锁定异常中断之后系统内部需要进行CLOSETABLE操作，在这个过程中出现的锁定类型就是WRITE_ONLY
读锁定
一个新的客户端请求在申请获取读锁定资源的时候，需要满足两个条件：
1、请求锁定的资源当前没有被写锁定；
2、写锁定等待队列（Pendingwrite-lockqueue）中没有更高优先级的写锁定等待；
如果满足了上面两个条件之后，该请求会被立即通过，并将相关的信息存入Currentread-lockqueue中，而如果上面两个条件中任何一个没有满足，都会被迫进入等待队列Pendingread-lockqueue中等待资源的释放。
写锁定
当客户端请求写锁定的时候，MySQL首先检查在Currentwrite-lockqueue是否已经有锁定相同资源的信息存在。
如果Currentwrite-lockqueue没有，则再检查Pendingwrite-lockqueue，如果在Pendingwrite-lockqueue中找到了，自己也需要进入等待队列并暂停自身线程等待锁定资源。反之，如果Pendingwrite-lockqueue为空，则再检测Currentread-lockqueue，如果有锁定存在，则同样需要进入Pendingwrite-lockqueue等待。当然，也可能遇到以下这两种特殊情况：
1.请求锁定的类型为WRITE_DELAYED;
2.请求锁定的类型为WRITE_CONCURRENT_INSERT或者是TL_WRITE_ALLOW_WRITE，同时Currentreadlock是READ_NO_INSERT的锁定类型。
当遇到这两种特殊情况的时候，写锁定会立即获得而进入Currentwrite-lockqueue中
如果刚开始第一次检测就Currentwrite-lockqueue中已经存在了锁定相同资源的写锁定存在，那么就只能进入等待队列等待相应资源锁定的释放了。
读请求和写等待队列中的写锁请求的优先级规则主要为以下规则决定：
1.除了READ_HIGH_PRIORITY的读锁定之外，Pendingwrite-lockqueue中的WRITE写锁定能够阻塞所有其他的读锁定；
2.READ_HIGH_PRIORITY读锁定的请求能够阻塞所有Pendingwrite-lockqueue中的写锁定；
3.除了WRITE写锁定之外，Pendingwrite-lockqueue中的其他任何写锁定都比读锁定的优先级低。
写锁定出现在Currentwrite-lockqueue之后，会阻塞除了以下情况下的所有其他锁定的请求：
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在原有锁定机制的基础上为了支持其ConcurrentInsert的特性而进行了相应的实现改造。
而其他几种支持事务的存储存储引擎，如Innodb，NDBCluster以及BerkeleyDB存储引擎则是让MySQL将锁定的处理直接交给存储引擎自己来处理，在MySQL中仅持有WRITE_ALLOW_WRITE类型的锁定。
由于MyISAM存储引擎使用的锁定机制完全是由MySQL提供的表级锁定实现，所以下面我们将以MyISAM存储引擎作为示例存储引擎，来实例演示表级锁定的一些基本特性。由于，为了让示例更加直观，我将使用显示给表加锁来演示：RITE_ALLOW_READ类型的写锁定。
刻 Sessiona Sessionb 行锁定基本演示 1 mysql>setautocommit=0; QueryOK,0rowsaffected(0.00sec) mysql>setautocommit=0; QueryOK,0rowsaffected(0.00sec) mysql>updatetest_innodb_locksetb='b1'wherea=1; QueryOK,1rowaffected(0.00sec) Rowsmatched:1Changed:1Warnings:0 更新，但是不提交 2 mysql>updatetest_innodb_locksetb='b1'wherea=1; 被阻塞，等待 3 mysql>commit;QueryOK,0rowsaffected(0.05sec)提交 4 mysql>updatetest_innodb_locksetb='b1'wherea=1; QueryOK,0rowsaffected(36.14sec) Rowsmatched:1Changed:0Warnings:0 解除阻塞，更新正常进行 无索引升级为表锁演示 5 mysql>updatetest_innodb_locksetb='2'whereb=2000; QueryOK,1rowaffected(0.02sec) Rowsmatched:1Changed:1Warnings:0 mysql>updatetest_innodb_locksetb='3'whereb=3000; 被阻塞，等待 6 7 mysql>commit;QueryOK,0rowsaffected(0.10sec) 8 mysql>updatetest_innodb_locksetb='3'whereb=3000; QueryOK,1rowaffected(1min3.41sec) Rowsmatched:1Changed:1Warnings:0 阻塞解除，完成更新 间隙锁带来的插入问题演示 9 mysql>select*fromtest_innodb_lock; +------+------+|a|b|+------+------+ |1|b2| |3|3| |4|4000| |5|5000| |6|6000| |7|7000| |8|8000| |9|9000| |1|b1| +------+------+ 9rowsinset(0.00sec) mysql>updatetest_innodb_locksetb=a*100wherea<4anda>1; QueryOK,1rowaffected(0.02sec) Rowsmatched:1Changed:1Warnings:0 10 mysql>insertintotest_innodb_lockvalues(2,'200'); 被阻塞，等待 11 mysql>commit; QueryOK,0rowsaffected(0.02sec) 12 mysql>insertintotest_innodb_lockvalues(2,'200'); QueryOK,1rowaffected(38.68sec) 阻塞解除，完成插入 使用共同索引不同数据的阻塞示例 13 mysql>updatetest_innodb_locksetb='bbbbb'wherea=1andb='b2'; QueryOK,1rowaffected(0.00sec) Rowsmatched:1Changed:1Warnings:0 14 mysql>updatetest_innodb_locksetb='bbbbb'wherea=1andb='b1';被阻塞 15 mysql>commit; QueryOK,0rowsaffected(0.02sec) 16 mysql>updatetest_innodb_locksetb='bbbbb'wherea=1andb='b1';QueryOK,1rowaffected(42.89sec) Rowsmatched:1Changed:1Warnings:0 session提交事务，阻塞去除，更新完成 死锁示例 17 mysql>updatet1setid=110whereid=11; QueryOK,0rowsaffected(0.00sec) Rowsmatched:0Changed:0Warnings:0 18 mysql>updatet2setid=210whereid=21; QueryOK,1rowaffected(0.00sec) Rowsmatched:1Changed:1Warnings:0 19 mysql>updatet2setid=2100whereid=21; 等待sessionb释放资源，被阻塞 20 mysql>updatet1setid=1100whereid=11; QueryOK,0rowsaffected(0.39sec) Rowsmatched:0Changed:0Warnings:0 等待sessiona释放资源，被阻塞
行级锁定
行级锁定不是MySQL自己实现的锁定方式，而是由其他存储引擎自己所实现的，如广为大家所知的Innodb存储引擎，以及MySQL的分布式存储引擎NDBCluster等都是实现了行级锁定。
Innodb锁定模式及实现机制
考虑到行级锁定君由各个存储引擎自行实现，而且具体实现也各有差别，而Innodb是目前事务型存储引擎中使用最为广泛的存储引擎，所以这里我们就主要分析一下Innodb的锁定特性。
总的来说，Innodb的锁定机制和Oracle数据库有不少相似之处。Innodb的行级锁定同样分为两种类型，共享锁和排他锁，而在锁定机制的实现过程中为了让行级锁定和表级锁定共存，Innodb也同样使用了意向锁（表级锁定）的概念，也就有了意向共享锁和意向排他锁这两种。
当一个事务需要给自己需要的某个资源加锁的时候，如果遇到一个共享锁正锁定着自己需要的资源的时候，自己可以再加一个共享锁，不过不能加排他锁。但是，如果遇到自己需要锁定的资源已经被一个排他锁占有之后，则只能等待该锁定释放资源之后自己才能获取锁定资源并添加自己的锁定。而意向锁的作用就是当一个事务在需要获取资源锁定的时候，如果遇到自己需要的资源已经被排他锁占用的时候，该事务可以需要锁定行的表上面添加一个合适的意向锁。如果自己需要一个共享锁，那么就在表上面添加一个意向共享锁。而如果自己需要的是某行（或者某些行）上面添加一个排他锁的话，则先在表上面添加一个意向排他锁。意向共享锁可以同时并存多个，但是意向排他锁同时只能有一个存在。所以，可以说Innodb的锁定模式实际上可以分为四种：共享锁（S），排他锁（X），意向共享锁（IS）和意向排他锁（IX），我们可以通过以下表格来总结上面这四种所的共存逻辑关系：
排他锁（X） 意向共享锁（IS） 意向排他锁（IX） 共享锁（S） 兼容 冲突 兼容 冲突 排他锁（X） 冲突 冲突 冲突 冲突 意向共享锁（IS） 兼容 冲突 兼容 兼容 意向排他锁（IX） 冲突 冲突 兼容 兼容
共享锁（S）
虽然Innodb的锁定机制和Oracle有不少相近的地方，但是两者的实现确是截然不同的。总的来说就是Oracle锁定数据是通过需要锁定的某行记录所在的物理block上的事务槽上表级锁定信息，而Innodb的锁定则是通过在指向数据记录的第一个索引键之前和最后一个索引键之后的空域空间上标记锁定信息而实现的。Innodb的这种锁定实现方式被称为“NEXT-KEYlocking”（间隙锁），因为Query执行过程中通过过范围查找的华，他会锁定整个范围内所有的索引键值，即使这个键值并不存在。
间隙锁有一个比较致命的弱点，就是当锁定一个范围键值之后，即使某些不存在的键值也会被无辜的锁定，而造成在锁定的时候无法插入锁定键值范围内的任何数据。在某些场景下这可能会对性能造成很大的危害。而Innodb给出的解释是为了组织幻读的出现，所以他们选择的间隙锁来实现锁定。
除了间隙锁给Innodb带来性能的负面影响之外，通过索引实现锁定的方式还存在其他几个较大的性能隐患：
当Query无法利用索引的时候，Innodb会放弃使用行级别锁定而改用表级别的锁定，造成并发性能的降低；
当Quuery使用的索引并不包含所有过滤条件的时候，数据检索使用到的索引键所只想的数据可能有部分并不属于该Query的结果集的行列，但是也会被锁定，因为间隙锁锁定的是一个范围，而不是具体的索引键；
当Query在使用索引定位数据的时候，如果使用的索引键一样但访问的数据行不同的时候（索引只是过滤条件的一部分），一样会被锁定
Innodb各事务隔离级别下锁定及死锁
Innodb实现的在ISO／ANSISQL92规范中所定义的ReadUnCommited，ReadCommited，RepeatableRead和Serializable这四种事务隔离级别。同时，为了保证数据在事务中的一致性，实现了多版本数据访问。
之前在第一节中我们已经介绍过，行级锁定肯定会带来死锁问题，Innodb也不可能例外。至于死锁的产生过程我们就不在这里详细描述了，在后面的锁定示例中会通过一个实际的例子为大家爱展示死锁的产生过程。这里我们主要介绍一下，在Innodb中当系检测到死锁产生之后是如何来处理的。
在Innodb的事务管理和锁定机制中，有专门检测死锁的机制，会在系统中产生死锁之后的很短时间内就检测到该死锁的存在。当Innodb检测到系统中产生了死锁之后，Innodb会通过相应的判断来选这产生死锁的两个事务中较小的事务来回滚，而让另外一个较大的事务成功完成。那Innodb是以什么来为标准判定事务的大小的呢？MySQL官方手册中也提到了这个问题，实际上在Innodb发现死锁之后，会计算出两个事务各自插入、更新或者删除的数据量来判定两个事务的大小。也就是说哪个事务所改变的记录条数越多，在死锁中就越不会被回滚掉。但是有一点需要注意的就是，当产生死锁的场景中涉及到不止Innodb存储引擎的时候，Innodb是没办法检测到该死锁的，这时候就只能通过锁定超时限制来解决该死锁了。另外，死锁的产生过程的示例将在本节最后的Innodb锁定示例中演示。
Innodb锁定机制示例
mysql>createtabletest_innodb_lock(aint(11),bvarchar(16))engine=innodb; QueryOK,0rowsaffected(0.02sec)
mysql>createindextest_innodb_a_indontest_innodb_lock(a); QueryOK,0rowsaffected(0.05sec) Records:0Duplicates:0Warnings:0
mysql>createindextest_innodb_lock_b_indontest_innodb_lock(b); QueryOK,11rowsaffected(0.01sec) Records:11Duplicates:0Warnings:0