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更新的不是同一条数据,那么它们之间不会有任何影响。


一句话总结:多个事务操作同一行数据时,后来的事务处于阻塞等待状态。这样可以避免了脏读等数据一致性的问题。后来的事务可以操作其他行数据,解决了表锁高并发性能低的问题。

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、行锁的分析

可以使用如下语句对行锁进行分析:

show status like 'innodb_row_lock%';

对于各个状态说明如下:

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


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



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



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

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

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

精彩评论

全部回复12人评论7,777人参与