MySQL锁机制及其优化

概述

在一般的数据库驱动的业务中，很大的一个难点就是：在最大程度地利用数据库的并发访问的同时，还要确保每个用户能以一致的方式读取和修改数据，为此，MySQL就有了锁（locking）的机制。频繁出现的锁的不仅本身消耗着资源，也影响着数据库的运行性能，因此，做好数据库的锁优化，对于数据库的性能具有很大意义。

锁的定义

锁是数据库系统区别文件系统的一个关键特性。锁机制是用于管理对共享资源的并发访问。
在MySQL中，lock和latch都可以被称为锁，但是两者具有不同的意义：

latch 一般称为闩锁，要求锁定的时间非常短，在innodb引擎中，latch又可分为mutex（互斥锁）和rwlock（读写锁），目的是用来保证并发线程操作临界资源的正确性，通常没有死锁检测的机制

lock的对象是事务，用来锁定的是数据库中的对象，如表，页，行。并且在commit或rollback后释放（不同的隔离级别的释放时间可能不同）

innodb中锁的类型

innodb实现了两种标准行级锁
共享锁（S Lock）:允许事物读一行数据
排他锁（X lock）：允许事物删除或更新一条数据

此外，innodb支持多颗粒锁定，这种锁定允许事务在行级别上的锁和表级上的锁同时存在，为此，innodb支持一种额外的锁方式：意向锁
意向锁将锁定的对象分为多个层次如: 数据库 > 表 > 页 > 记录，并且规定，要想对最细粒度的对象上锁，那么首先要对粗粒度的对象上锁，就如要想对一行记录加上X锁，就必须在该行所在的数据库，表，页先上意向锁IX，最后对行记录加上X锁。

innodb存储引擎支持意向锁设计比较简练，其意向锁为表级别的锁目的是为了在一个事物中揭示下一行被请求的锁类型，意向锁分为两种：
1）意向共享锁，事物想要获得一张表中某几行的共享锁
2）意向排他锁，事物想要获得一张表中某几行的排他锁

由于支持的是行级别的锁，所以意向锁不会阻塞全表扫以外的任何请求，其兼容性如下：

以上，是MySQL锁的基本知识

latch的优化

对于innodb中的latch，可以通过以下命令查看

mysql>show engine innodb mutex;
+----------------+-----------------------------+------------------+
| Type | Name | Status |
+----------------+-----------------------------+------------------+
| InnoDB | rwlock: dict0dict.cc:1184 | waits=90 |
| InnoDB | rwlock: log0log.cc:838 | waits=189885 |
| InnoDB | sum rwlock: buf0buf.cc:1460 | waits=2 |
+----------------+-----------------------------+------------------+
返回行数：[3]，耗时：698 ms.

列type显示的总是Innodb，列name显示的是latch的信息以及所在源码的位置（行数），列status显示的是等待的次数
上述所有的这些信息都是比较底层的，一般仅供开发人员参考

MySQL表锁的优化

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

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

优化建议：
1）建议将表切换为innodb 存储引擎，减少表级别锁出现概率
2）优化select语句，使其能在更短的时间内完成查询，减少表锁时间
3）对于只使用表级锁的存储引擎(如MyISAM、MEMORY和MERGE)，启用--low-priority-updates模式，使所有更新表的语句的优先级都低于SELECT语句

也可考虑赋予特定insert，update，delete语句更低的优先级

4）在查询语句中使用SQL_BUFFER_RESULT（查询缓存），来减少表被锁定的时间
5）将大的表转为separate tables，因为允许对一个表中的列运行查询，而更新仅限于不同表中的列。

innodb行锁的优化

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

要想合理利用Innodb的行级锁定，做到扬长避短，我们必须做好以下工作：
1）尽可能让所有的数据查询都通过索引来完成，从而避免Innodb因为无法通过索引键加锁而升级为表级锁定；
2）合理设计索引，让Innodb在索引键上面加锁的时候尽可能准确，尽可能的缩小锁定范围，避免造成不必要的锁定而影响其他查询的执行；
3）尽可能减少基于范围的数据检索过滤条件，避免因为间隙锁带来的负面影响而锁定了不该锁定的记录；
4）尽量控制事务的大小，减少锁定的资源量和锁定时间长度；

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

mysql>show status like 'innodb_row_lock%'
+-------------------------------+-----------------+
| Variable_name | Value |
+-------------------------------+-----------------+
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 0 |
| Innodb_row_lock_time_avg | 0 |
| Innodb_row_lock_time_max | 0 |
| Innodb_row_lock_waits | 0 |
+-------------------------------+-----------------+
返回行数：[5]，耗时：4 ms.

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（等待总时长）这三项。尤其是当等待次数很高，而且每次等待时长也不小的时候，我们就需要分析系统中为什么会有如此多的等待，然后根据分析结果着手指定优化计划。

死锁优化

死锁是指两个或两个以上的事务在执行过程中，因争夺锁资源而造成的一种相互等待的现象。若无外力作用，事务都将无法推进下去。

一般的，解决死锁的问题的最简单的方式是不要有等待，将任何等待都转化为回滚，并且事务重新开始

死锁优化的第一步就是设置合理的锁超时时间，不要存在长时间所等待的情况

mysql>show variables like '%innodb_lock_wait%'
+--------------------------+-----------------+
| Variable_name | Value |
+--------------------------+-----------------+
| innodb_lock_wait_timeout | 50 |
+--------------------------+-----------------+
返回行数：[1]，耗时：4 ms.

一旦发生死锁，需要及时处理，下列信息可以让运维人员及时发现死锁的问题

mysql>show engine innodb status\G
TRANSACTIONS
------------
Trx id counter 295495
Purge done for trx's n:o < 295495 undo n:o < 0 state: running but idle
History list length 31
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421494576134400, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421494576130752, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421494576131664, not started
0 lock struct(s), heap size 1136, 0 row lock(s)

结语

总之，优化锁的最好方法就是尽量避免锁等待的发生，在高并发和锁消耗直接找到一个性能的平衡点，对于用户来说，根据自己的业务特点和要求，正确的进行锁优化，是提高系统性能重要的一个步骤。

学习参考资料：MySQL5.7文档，《MySQL技术内幕》，相关博文