mysql中的锁机制之行锁

1、概述

偏向InnoDB存储引擎，开销大，加锁慢，会出现死锁；锁定粒度最小，发生锁冲突的概率最底，并发度也最高。

InnoDB与MyISAM的最大不同点：一是支持事务，二是采用了行级锁。

2、准备建表语句和数据

-- 创建一张InnoDB存储引擎的数据表``CREATE` `TABLE` ``employee` (`` ```id` ``int``(11) ``NOT` `NULL` `AUTO_INCREMENT,`` `````name``` ``varchar``(20) ``DEFAULT` `NULL``,`` ```dep_id` ``int``(11) ``DEFAULT` `NULL``,`` ```age` ``int``(11) ``DEFAULT` `NULL``,`` ```salary` ``decimal``(10,2) ``DEFAULT` `NULL``,`` ```cus_id` ``int``(11) ``DEFAULT` `NULL``,`` ``PRIMARY` `KEY` `(`id`)``) ENGINE=InnoDB AUTO_INCREMENT=109 ``DEFAULT` `CHARSET=utf8;` `-- 插入测试数据``INSERT` `INTO` ``employee` ``VALUES` `(``'1'``, ``'鲁班'``, ``'1'``, ``'10'``, ``'1000.00'``, ``'1'``);``INSERT` `INTO` ``employee` ``VALUES` `(``'2'``, ``'后裔'``, ``'1'``, ``'20'``, ``'2000.00'``, ``'1'``);``INSERT` `INTO` ``employee` ``VALUES` `(``'3'``, ``'孙尚香'``, ``'1'``, ``'20'``, ``'2500.00'``, ``'1'``);``INSERT` `INTO` ``employee` ``VALUES` `(``'4'``, ``'凯'``, ``'4'``, ``'20'``, ``'3000.00'``, ``'1'``);``INSERT` `INTO` ``employee` ``VALUES` `(``'5'``, ``'典韦'``, ``'4'``, ``'40'``, ``'3500.00'``, ``'2'``);``INSERT` `INTO` ``employee` ``VALUES` `(``'6'``, ``'貂蝉'``, ``'6'``, ``'20'``, ``'5000.00'``, ``'1'``);``INSERT` `INTO` ``employee` ``VALUES` `(``'7'``, ``'孙膑'``, ``'6'``, ``'50'``, ``'5000.00'``, ``'1'``);``INSERT` `INTO` ``employee` ``VALUES` `(``'8'``, ``'蔡文姬'``, ``'30'``, ``'35'``, ``'4000.00'``, ``'1'``);

3、行锁的演示

注意：mysql5.5及以上版本，默认事务会进行自动提交。

使用2个客户端分别连接上mysql，我这里用的是Navicat Premium和mysql命令行版，下面的演示中我会将Navicat Premium称为：会话1。mysql命令行版称为：会话2。

2个mysql客户端示例截图如下：

会话1：

会话2：

3.1、在会话1中关闭mysql的事务的自动提交

set` `autocommit = 0 #关闭自动提交事务

示例截图：

在会话1中进行数据的更新操作 然后紧接着进行查询操作：

从上图中可以看到，将id=1的数据的name列的值改成了"李白"，并且当前自己查询自己，看到的数据也确实是修改后的数据。

此时我们在会话2中查询该表的数据：

从上图中可以看到 会话2中的mysql读取到的数据并不是会话1中修改的"李白"的这条数据，由此可见会话2中是看不到更新的内容的，只有会话1中进行了commit（提交）操作之后，会话2中才能看到最新修改的数据。

将会话1中的数据进行commit后，然后在会话2中执行查询操作，查看会话2中查询到的数据和会话1中查询到的数据是否一样

commit``; ``-- 在会话1中执行commit操作

示例截图：

从上图中可以看到会话1中进行了commit之后，在会话2中查询到的数据就是最新的数据，和会话1中看到的数据是一模一样的。

从上面的示例中，可以进行以下总结：

执行更新操作的时候：自己（当前连接）可以查看到自己所更新的数据内容，非当前连接，看不到更新的数据内容，只有当前连接进行commit后，其它连接才能看到更新的数据内容。

3.2、将会话1和会话2中的事务的自动提交都关闭

注：上面我们已经将会话1中的事务的自动提交给关闭了，所以这里只需要关闭会话2中的事务的自动提交就可以了

在会话2中关闭mysql的事务的自动提交

set` `autocommit = 0; #关闭自动提交事务

示例截图：

会话1执行更新操作，会话2也执行更新操作，并且都是更新同一条记录

-- 会话1中执行如下SQL(会话1中先执行如下sql,注意 并没有进行commit操作)：``update` `employee ``set` `name` `= ``'李白1'` `where` `id = 1;` `-- 会话2中执行如下SQL(会话1中执行完了之后,会话1中执行完了之后,在执行如下sql)：``update` `employee ``set` `name` `= ``'李白2'` `where` `id = 1;

示例截图如下：

从上图中可以看到 会话1执行了更新操作后，紧接着会话2中也执行了更新同一条数据的sql，发现会话2中执行的sql语句被堵塞在哪里进行等待了。

为什么会话2中的sql执行会被阻塞在哪里进行等待？

答：因为会话1中正在执行，并且会话1中已经把这行数据进行锁死了（也就是说行锁默认就会给你加上了），所以会话2中执行更新同一条数据的时候会被阻塞在哪里进行等待。

只有会话1中的数据进行commit之后，之前在会话2中被阻塞在哪里的操作才会自动执行下去。

示例截图：

然后不要忘了在会话2中同样需要进行commit操作

示例截图：

此时无论在会话1还是会话2中执行查询操作，那么id=1的name列的值就应该变成了李白2。

示例截图：

从上面的示例中，可以进行以下总结：

会话1执行更新操作，会话2也执行更新操作，并且都是更新同一条记录：会话1没有提交事务时，会话2的更新会处于阻塞状态 ，当会话1进行commit的时候，会话2才会继续执行，会话2更新完毕之后，同样需要进行commit操作。

3.3、会话1和会话2同时更新数据，但更新的不是同一条记录，是否会互相影响？

答：不会受到彼此之间的影响，就不一 一截图了，可以自己试验看一下。

小总结：如果会话1和会话2同时更新同一条数据的话，那么其中一个会话会被阻塞等待在哪里，因为其中一个会话会将修改的那一行数据进行加锁（行锁），加锁的那一行进行commit之后（进行commit之后也就是等于释放锁了），其它会话才能依次进行操作。如果会话1和会话2更新的不是同一条数据，那么它们之间不会有任何影响。

一句话总结：多个事务操作同一行数据时，后来的事务处于阻塞等待状态。这样可以避免了脏读等数据一致性的问题。后来的事务可以操作其他行数据，解决了表锁高并发性能低的问题。*另外InnoDB的行锁是针对索引加的锁，不是针对记录加的锁。并且该索引不能失效，否则都会从行锁升级为表锁。*

4、索引失效，导致行锁变表锁

字段使用varchar类型时，没有添加引号，导致索引失效。就会造成行锁变表锁，另一个会话连接更新数据时，会造成阻塞。

4.1、行锁变表锁的演示

-- 添加一个复合索引` `ALTER` `TABLE` ``lock`.`employee` ``ADD` `INDEX` ``idx_name_age`(```name```, `age`) USING BTREE;

会话1中的操作如下：

set` `autocommit = 0; ``-- 第1步 先关闭事务的自动提交` `update` `employee ``set` `name``= ``'韩信'` `where` `age = 10 ``-- 第2步 执行该条SQL语句

会话2中的操作如下：

set` `autocommit = 0; ``-- 第1步 同样先关闭事务的自动提交` `update` `employee ``set` `name``= ``'明世隐'` `where` `age = 35; ``-- 第2步 执行该条SQL语句

示例截图如下：

从上图中可以看到，会话1和会话2明明修改的不是同一条数据，但是会话2依然被阻塞在哪里进行等待了。。怎么办呢？只能等待会话1进行commit操作后，会话2才能继续进行下去啦。

为什么上面会话1中的update更新操作会造成行锁变成表锁？

答：因为上面我们建立了一个复合索引（name,age），而会话1中的where条件只有age，造成了不符合索引中的左前缀原则，导致没有用上该复合索引，所以导致索引失效，行锁就变成了表锁。从而虽然会话2中更新的数据和会话1中更新的数据不是同一条数据，会话2依然受到了影响。

5、如何锁定一行数据？

锁定一行指的是：在查询数据的时候，就把某一行数据进行加锁（行锁），不允许其它人进行修改了。锁定某一行的关键词语：for update;

会话1中操作：

set` `autocommit = 0;` `-- 查询id=1的数据，并锁定该行记录``select` `*``from` `employee ``where` `id = 1 ``for` `update``;

会话2中操作：

set` `autocommit = 0;` `-- 这里会被阻塞。上面会话1中在查询的时候已经加锁了。``update` `employee ``set` `name` `= ``'zl'` `where` `id = 1;

5.1：for update的更多使用及注意细节

记住一个原则：一锁二判三更新

** **

①、for update如何使用

使用姿势：

select` `* ``from` `table` `where` `xxx ``for` `update

②、for update的锁行及锁表操作示例

InnoDB存储引擎 默认是行级别的锁，当有明确指定的主键时候，是行级锁。否则是表级别。

例子：假设表foods ，存在有id跟name、status三个字段，id是主键，status有索引。

例1：(明确指定主键，并且有此记录，行级锁)

SELECT` `* ``FROM` `foods ``WHERE` `id=1 ``FOR` `UPDATE``;``SELECT` `* ``FROM` `foods ``WHERE` `id=1 ``and` `name``=``'php最好的语言'` `FOR` `UPDATE``;

例2：(明确指定主键/索引，若查无此记录，无锁)

SELECT * FROM foods WHERE id=-1 FOR UPDATE;

例3：(无主键/索引，表级锁)

SELECT` `* ``FROM` `foods ``WHERE` `name``=``'php最好的语言'` `FOR` `UPDATE``;

例4: (主键/索引不明确，表级锁)

SELECT` `* ``FROM` `foods ``WHERE` `id <> ``'3'` `FOR` `UPDATE``;``SELECT` `* ``FROM` `foods ``WHERE` `id ``LIKE` `'3'` `FOR` `UPDATE``;

** **

③、for update的注意点

⑴、for update 仅适用于InnoDB，并且必须开启事务，在begin与commit之间才生效。

⑵、要测试for update的锁表情况，可以利用MySQL的Command Mode，开启二个视窗来做测试。

** **

④、for update的疑问点

当开启一个事务进行for update的时候，另一个事务也有for update的时候会一直等着，直到第一个事务结束吗？

答：会的。除非第一个事务commit或者rollback或者断开连接，第二个事务会立马拿到锁进行后面操作。

如果没查到记录会锁表吗？

答：会的。表级锁时，不管是否查询到记录，都会锁定表。

例2不是说无锁吗？为什么最后说都会锁定表？

答：说的是表级锁时会锁，例2主键查询，是行锁，查询不到记录不锁表，例3和例4这种表级锁，查询不到记录也会锁表。

⑤、关于行锁的小总结：由此可见，行锁都是基于索引来进行锁定的，只要字段有索引（无论是主键索引或者普通索引）并且有该条记录，都是能成功锁定该行记录的。

6、间隙锁

什么是间隙锁？

当我们使用范围条件，而不是相等条件检索数据，并请求共享或排它锁时，InnoDB会给符合条件的已有数据记录的索引项加锁，对于键值在条件范围内但并不存在的记录 叫做"间隙(GAP)"，InnoDB也会对这个"间隙"加锁，这种锁机制，就是所谓的间隙锁。间隙锁的主要作用就是防止幻读。

会话1中的操作

set` `autocommit = 0;` `update` `employee ``set` `name` `= ``'wzyl'` `where` `id > 3 ``and` `id < 7; ``-- 将id为4、5、6这三行数据进行锁定。

会话2中的操作

set` `autocommit = 0;` `update` `employee ``set` `name` `= ``'test'` `where` `id = 1; ``-- 可以执行 不会被阻塞` `update` `employee ``set` `name` `= ``'test'` `where` `id = 5; ``-- 不能执行，会被阻塞在那里进行等待,因为5这行数据在会话1中的范围锁定之内。

7、行锁的分析

7.1 查看有没有行锁在等待

可以使用如下语句对行锁进行分析（即 监控有没有锁 在等待）：

show status ``like` `'innodb_row_lock%'``;

对于各个状态说明如下：

Innodb_row_lock_current_waits：当前正在等待 锁 的数量 即 当前有多少 锁 正在等待（重点关注）

Innodb_row_lock_waits：数据库系统启动到现在 一共发生过多少次 锁 等待的次数，如果数据库重启 则该值会重置为0（重点关注）

Innodb_row_lock_time：从系统启动到现在锁定总时间长度

Innodb_row_lock_time_avg：每次等待所花平均时间

Innodb_row_lock_time_max：从系统启动到现在等待最长的一次所花的时间长度

上面这5个状态变量，比较重要的是：Innodb_row_lock_waits、Innodb_row_lock_waits

尤其是当等待次数很高，而且每次等待时长也很大的时候，我们就要分析系统中为什么有这么多的等待，然后根据分析结果来制定优化。

7.2 查看哪个事务在等待（被阻塞了）

-- 使用以下语句查看有哪些事务在等到锁（被阻塞了）``SELECT` `* ``FROM` `information_schema.INNODB_TRX ``WHERE` `trx_state=``'LOCK WAIT'``;` `-- 上面语句查询出来的结果中，关注以下几列即可：``trx_id: 事务id号` `trx_state: 当前事务的状态` `trx_mysql_thread_id: mysql连接层的 连接线程id号(即 执行 show processlist 语句后 显示出来的结果中的 id 列选项)` `trx_query: 被阻塞的执行语句(一般是要丢给开发的)

备注：其实还有一个语句 可以显示的比上面（7.2）这个操作 显示的更全面和细致，即下面的 7.3 操作 一般使用下面的 7.3 进行排查即可 上面的 7.2 了解下就行

7.3 查看锁源(锁源：就是 阻塞别人的罪魁祸首)，谁锁的我！

-- 使用以下语句查看 被锁和锁定它 的之间的关系``SELECT` `* ``FROM` `sys.innodb_lock_waits;  ``-- ====>被锁的和锁定它的之间关系` `-- 上面语句查询出来的结果中，关注以下几列即可：``locked_table: 哪张表出现的锁等待，产生锁等待的表名` `waiting_trx_id: 等待的事务id号(与上面 7.2 中的 trx_id 列对应)` `waiting_pid: 等待的线程id号(与上面 7.2 中的 trx_mysql_thread_id 列对应)` `blocking_trx_id: 锁源的事务id号 ` `blocking_pid: 锁源的线程id号 即 执行 show processlist 语句后 显示出来的结果中的 id 列选项 可以理解为process_id。其实就是一个连接线程id号` `-- 上面语句查询出来的结果中，以下几列了解 知道什么意思即可：``locked_index: 在那个索引上锁住了, innodb的锁是基于索引来锁的` `locked_type: 锁的类型 包含 行级锁(Record Lock)、间隙锁(Gap Lock)、``Next``-``Key` `Lock锁(行锁和间隙锁组合起来就叫``Next``-``Key` `Lock)` `waiting_query: 被阻塞的执行语句(与上面 7.2 中的 trx_query 列对应)` `waiting_lock_mode: 等待锁的类型，是一个什么级别的锁(X,S) 一般情况下都是X，X: 排它锁,阻塞所有的` `sql_kill_blocking_connection: mysql的建议处理方式 直接kill掉锁源的连接 ps: mysql的建议一般不采纳。。必须从根上解决问题,万一下次又遇到了呢？即使临时kill掉 也要注意是否会影响到业务逻辑

备注：通过7.3的操作，就找到了被阻塞的人和阻塞别人的人

7.3的操作步骤 我们找到了 由 blocking_pid（会话） 产生的 对应的 blocking_trx_id（事务）导致了别人被阻塞了，即 这里 找到了锁源

7.4 根据锁源的pid 找到锁源SQL的线程id(thread_id)

注意：mysql内部有很多线程，包括 连接线程、专门负责执行sql语句的sql线程等等等等......，我们不能单纯的只通过 一个连接线程 直接找到 对应的sql语句，因为 一个连接线程 下边执行的语句太多啦，我们应该精准的找到 到底是哪一个sql线程 执行的这条sql语句才行。

所以说要找到那条准确的sql语句，必须准确的找到执行者（sql线程）是谁

-- 根据 锁源的连接线程id 找到 锁源的sql线程id（即 thread_id）``SELECT` `* ``FROM` `performance_schema.threads ``WHERE` `processlist_id=1520; ``-- 这里的1520连接线程id号 根据自身实际情况换成对应的` `-- 这里where条件中的 processlist_id 其实就是 7.3中的 blocking_pid

7.5 根据锁源的sql线程id 找到锁源的SQL语句

-- sql线程在执行的语句``SELECT` `* ``FROM` `performance_schema.`events_statements_current` ``WHERE` `thread_id=1789; ``-- 这里的1789sql线程id号 就是上面 7.4执行后 找到的thread_id 根据自身实际情况换成对应的` `-- 上面语句查询出来的结果中，关注以下几列即可：``thread_id: sql线程id号``sql_text: 锁源的sql语句，就是这条sql语句把其它sql语句给阻塞了 so 解决掉它`  `-- 执行语句的历史``SELECT` `* ``FROM` `performance_schema.`events_statements_history` ``WHERE` `thread_id=1789; ``-- 查看1789sql线程执行的历史语句

行锁分析尾声：7.1至7.5 就是排查锁的相关问题步骤 找到对应的sql语句后，该 改sql语句就改sql语句 该 改业务逻辑就改相关业务逻辑

8、死锁（Deadlock）的监控

死锁（Deadlock）：指两个或两个以上的进程在执行过程中，因争夺资源而造成的一种互相等待的现象。若无外力作用，它们都将无法继续进行下去，此时称系统处于死锁状态或系统产生了死锁，这些永远在互相等待的进程称为死锁进程。表级锁不会产生死锁，所以解决死锁主要还是针对于最常用的InnoDB存储引擎。

例如，如果线程A锁住了记录1并等待记录2，而线程B锁住了记录2并等待记录1，这样两个线程就发生了死锁现象

死锁是比上面第7点中说的锁等待更严重的一种锁，死锁一般会对业务逻辑产生很严重的影响

死锁的示例图如下：

上图中，左图那两辆车造成死锁了吗？没有！上图中，右图四辆车造成死锁了吗？是！

8.1 死锁的监控方法

-- 可以在mysql命令行模式下 使用以下语句查看 ``show engine innodb status\G ``-- 在列出的一大堆信息中，找Deadlock相关的信息 找到后 会看到死锁对应的事务id号等等......之类的` ` ` `-- 在Navicat Premium或Navicat for Mysql等之类的图形化界面软件中执行以下语句``show engine innodb status ``-- 其实就是去掉了\G 为了照顾一下不是很懂的小盆友。。` `但是show engine innodb status 只能显示最新的一条死锁 该方式无法完全捕获到系统曾经发生过的所有死锁信息

如果想要记录所有的死锁信息 可以打开innodb_print_all_deadlocks参数，打开后 可以将所有的死锁日志记录到errorlog中

show variables ``like` `'%deadlock%'``; ``-- 查看死锁日志记录是否打开` `set` `global` `innodb_print_all_deadlocks=1 ``-- 临时打开全局死锁日志记录，重启mysql的时候 该选项就失效了` `-- 死锁日志永久生效，将innodb_print_all_deadlocks参数添加到mysql配置文件中``innodb_print_all_deadlocks = 1