mysql中的锁机制之行锁

本篇博文的mysql版本:5.7.2

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:

m1.png

会话2:

m2.png


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

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

示例截图:

m3.png


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

m4.png

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


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

m5.png

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


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

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

示例截图:

m6.png

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


从上面的示例中,可以进行以下总结:

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



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

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


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

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

示例截图:

m7.png

会话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;

示例截图如下:

m8.png

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


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

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


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

示例截图:

m9.png


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

示例截图:

m1.png


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

示例截图:

m1.png


从上面的示例中,可以进行以下总结:

会话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语句

示例截图如下:

m2.png

从上图中可以看到,会话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;

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点中说的锁等待更严重的一种锁,死锁一般会对业务逻辑产生很严重的影响

        

死锁的示例图如下:

123.png

上图中,左图那两辆车造成死锁了吗?没有!上图中,右图四辆车造成死锁了吗?是!

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



别把最好的时光,浪费在无畏的等待与犹豫不决中



声明:禁止任何非法用途使用,凡因违规使用而引起的任何法律纠纷,本站概不负责。

扫码支持
扫码打赏,你说多少就多少

打开支付宝扫一扫,即可进行扫码打赏哦

精彩评论

全部回复12人评论7,777人参与
  1. 1楼
    便捷摄影工作室

    好牛,已测试

    2016-02-28 12:04:01 回复
  2. 2楼
    naive

    浏览器换了无数个都看不到密码

    2017-02-23 04:06:13 回复
    • King 作者

      @naive 现在已被屏蔽。我再放上去,我的网站就被屏蔽咯

      2017-02-24 01:46:37 回复

电子邮件地址不会被公开。 必填项已用 *标注

loading