MySQL——MySQL神器之锁

锁是个啥

锁是计算机协调多个进程或线程并发访问某一资源的机制。
在数据库中，除传统的计算资源(如CPU、RAM、I/O等)的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。

MySQL中的锁

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

表级锁

总览

• 特点：表级别的锁定是 MySQL 各存储引擎中最大颗粒度的锁定机制。实现逻辑非常简单，带来的系统负面影响最小。所以获取锁和释放锁的速度很快。由于表级锁一次会将整个表锁定，所以可以很好的避免困扰我们的死锁问题。

• 弊端：出现锁定资源争用的概率也会最高，致使并发量大打折扣。

详解

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. 写锁定等待队列（Pending write-lock queue）中没有更高优先级的写锁定等待

如果满足了上面两个条件之后，该请求会被立即通过，并将相关的信息存入 Current read-lock 队列中，而如果上面两个条件中任何一个没有满足，都会被迫进入等待队列 Pending read-lock中等待资源的释放。

写锁定的过程

当客户端请求写锁定的时候，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 等待。如果刚开始第一次检测就 Current write-lock queue 中已经存在了锁定相同资源的写锁定存在，那么就只能进入等待队列等待相应资源锁定的释放了。

行级锁

总览

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

• 弊端：由于锁定资源的颗粒度很小，所以每次获取锁和释放锁需要做的事情也更多，带来的消耗自然也就更大了。此外，行级锁定也最容易发生死锁。

详解

行级锁定不是 MySQL 自己实现的锁定方式，而是由其他存储引擎自己所实现的，如广为大家所知的Innodb 存储引擎，以及 MySQL 的分布式存储引擎 NDB Cluster 等都是实现了行级锁定。Innodb 锁定模式及实现机制考虑到行级锁定均由各个存储引擎自行实现，而且具体实现也各有差别，而 Innodb 是目前事务型存储引擎中使用最为广泛的存储引擎，所以这里我们就主要分析一下 Innodb 的锁定特性。

共享锁，排他锁，意向锁

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

1. 事务A想给一条记录加锁，但是有一个共享锁正在锁定着这条记录，这是事务A可以给这条记录再加一个共享锁，但是不能加排他锁
2. 事务A想给一条记录加锁，但是有一个排他锁正在锁定着这条记录，那么事务A只能等该锁定释放资源之后自己才能获取锁定资源并添加自己的锁定

对于第二种情况，事务A可以在这条记录的表上加一个合适的意向锁。如果需要一个共享锁，就在表上面添加一个意向共享锁，而如果需要一个排他锁，则需要现在表上面添加一个意向排他锁。意向共享锁可以同时存在多个，但是意向排他锁只能同时存在一个

间隙锁

当我们要查询的数据在一个范围，并请求共享或排他锁时，Innodb会通过在指向数据记录的第一个索引键之前和最后一个索引键之后的空域空间上标记锁定信息，就是会给符合范围条件的已有数据记录的索引项加锁，但是呢这样又有一个坏处，就是当锁定一个范围键值之后，即使某些不存在的键值也会被无辜的锁定，而造成在锁定的时候无法插入锁定键值范围内的任何数据。

页级锁

• 特点：锁定颗粒度介于行级锁定与表级锁之间，所以获取锁定所需要的资源开销，以及所能提供的并发处理能力也同样是介于上面二者之间。另外，页级锁定和行级锁定一样，会发生死锁。
  例如有一个学生表，里面有50条数据，索引为id,使用以下sql语句查询

select * from student where id>49 for uodate;

Innodb不但会对id为49的数据加锁，也会对id>50的数据加锁，对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”。InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁。那既然有这么个致命缺点，为什么还要有间隙锁呢？

InnoDB使用间隙锁的目的有两个：

• 为了防止幻读，满足恢复和复制的需要

• MySQL的恢复机制要求
  在一个事务未提交前，其他并发事务不能插入满足其锁定条件的任何记录，也就是不允许出现幻读

怎么利用锁进行性能优化？

MyISAM 表锁优化建议

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

缩短锁定时间

如何让锁定时间尽可能的短呢？唯一的办法就是让我们的 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;
• concurrent_insert=1，当 MyISAM 存储引擎表数据文件中间不存在空闲空间的时候，可以从文件尾部进行 Concurrent Insert;
• concurrent_insert=0，无论 MyISAM 存储引擎的表数据文件的中间部分是否存在因为删除数据而留下的空闲空间，都不允许 Concurrent Insert。

合理利用读写优先级

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

Innodb 行锁优化建议

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

合理利用行级锁定

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

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

防止死锁

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