张治峰的博客

MySQL 锁机制

2021-10-02

概述

锁是计算机协调多个进程或线程并发访问某一资源的机制。
在数据库中,除了传统的计算资源(如CPU、RAM、I/O等)的争用以外,数据也是一种供需要用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题,锁冲突也是影响数据库并发访问性能的一个重要因素。

锁分类

  • 从性能上分为乐观锁(用版本对比来实现)和悲观锁
  • 从对数据库操作的类型分,分为读锁和写锁(都属于悲观锁)
    • 读锁(共享锁,S锁(Shared)):针对同一份数据,多个读操作可以同时进行而不会互相影响
    • 写锁(排它锁,X锁(eXclusive)):当前写操作没有完成前,它会阻断其他写锁和读锁
  • 从对数据操作的粒度分,分为表锁和行锁

表锁

每次操作锁住整张表。开销小,加锁快、不会出现死锁。锁定粒度大,发生锁冲突的概率最高,并发度最低。
一般用在整表数据迁移的场景。

案例
数据准备

# 建表
CREATE TABLE `mylock`(
`id` INT (11) NOT NULL AUTO_INCREMENT,
`NAME` VARCHAR (20) DEFAULT NULL,
PRIMARY KEY (`id`)
)ENGINE=MyISAM DEFAULT CHARSET=utf8;
#插入数据
INSERT INTO`test`.`mylock`(`id`,`NAME`)VALUES('1','a');
INSERT INTO`test`.`mylock`(`id`,`NAME`)VALUES('2','b');
INSERT INTO`test`.`mylock`(`id`,`NAME`)VALUES('3','c');
INSERT INTO`test`.`mylock`(`id`,`NAME`)VALUES('4','d');

读锁

  1. 客户端A 加读锁

    LOCK table mylock read;

  2. 客户端A 查询表 mylock

    SELECT * from mylock;

  3. 客户端B 查询表 mylock 数据

    SELECT * from mylock;

    从3,4结果客户端A和客户端B都可以读该表,所以读锁不阻塞读

  4. 客户端A 插入或者更新锁定的表都会报错

    UPDATE mylock set `name` = "aa" where id =1;

  5. 客户端B 插入或更新会等待

    UPDATE mylock set `name` = "aa" where id =1;

写锁
客户端A对该表的增删改查都没有问题,客户端B对该表的所有操作被阻塞。(按照读锁的操作步骤自行实验,截图好累。。。。)

结论
1、对MyISAM表的读操作(加读锁) ,不会阻塞其他进程对同一表的读请求,但会阻塞对同一表的写请求。只有当读锁释放后,才会执行其它进程的写操作。
2、对MylSAM表的写操作(加写锁) ,会阻塞其他进程对同一表的读和写操作,只有当写锁释放后,才会执行其它进程的读写操作。

行锁

每次操作锁住一行数据。开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度最 高。
一个session开启事务更新不提交,另一个session更新同一条记录会阻塞,更新不同记录不会阻塞

InnoDB与MYISAM的最大不同有两点:

  • InnoDB支持事务(TRANSACTION)
  • InnoDB支持行级锁

无索引行锁会升级为表锁

锁主要是加在索引上,如果对非索引字段更新,行锁可能会变表锁
客户端A 执行:update account set balance = 800 where name = ‘zhangsan’;
客户端B 对该表任一行操作都会阻塞住,InnoDB的行锁是针对索引加的锁,不是针对记录加的锁。并且该索引不能失效,否则都会从行锁升级为表锁。

锁定某一行还可以用lock in share mode(共享锁) 和for update(排它锁),例如:select * from test_innodb_lock where a = 2 for update; 这样其他session只能读这行数据,修改则会被阻塞,直到锁定 行的session提交

MyISAM/InnoDB对比分析
MyISAM在执行查询语句SELECT前,会自动给涉及的所有表加读锁,在执行update、insert、delete操作会自 动给涉及的表加写锁。
InnoDB在执行查询语句SELECT时(非串行隔离级别),不会加锁。但是update、insert、delete操作会加行锁。
简而言之,就是读锁会阻塞写,但是不会阻塞读。而写锁则会把读和写都阻塞。

行锁分析
通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况

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 (重要)系统启动后到现在总共等待的次数

当等待次数很高,而且每次等待时长也不小的时候,我们就需要分析系统中为什么会有如此多的等待, 然后根据分析结果着手制定优化计划。

死锁

set tx_isolation=’repeatable-read’;

Session_1执行:select * from account where id=1 for update;

Session_2执行:select * from account where id=2 for update;

Session_1执行:select * from account where id=2 for update;

Session_2执行:select * from account where id=1 for update;

查看近期死锁日志信息:show engine innodb status\G;

大多数情况mysql可以自动检测死锁并回滚产生死锁的那个事务,但是有些情况mysql没法自动检测死锁

间隙锁(Gap Locks)

间隙锁是封锁索引记录中的间隔(后面具体用例子讲解说明是间隔),或者第一条索引记录之前的范围,又或者最后一条索引记录之后的范围。间隙锁 在某些情况下可以解决幻读问题。

mysql间隙锁默认开启 查看间隙锁状态 传送门 ->mysql常用命令及配置

间隙锁的产生条件(RR事务隔离级别下)

  1. 使用普通索引锁定;
  2. 使用多列唯一索引;
  3. 使用唯一索引锁定多行记录。

案例一.唯一索引的间隙锁
数据环境: MySQL,InnoDB,默认的隔离级别 可重复读(RR)
数据准备

CREATE TABLE `gap_locks` (
`id` int(1) NOT NULL AUTO_INCREMENT,
`name` varchar(8) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `gap_locks` VALUES ('1', '张三');
INSERT INTO `gap_locks` VALUES ('4', '李四');
INSERT INTO `gap_locks` VALUES ('7', '王五');
INSERT INTO `gap_locks` VALUES ('11', '赵六');
INSERT INTO `gap_locks` VALUES ('15', '孙七');

开始之前 我们先来看看什么是间隙,id 字段的值 为 1 4 7 11 , 1-4这个区间内还空了 2,3 这两个值 这就是一个间隙。那么这张表一共有下面6个间隙(整个数据区间为 负无穷 到 正无穷)

  • (-∞,1]
  • (1,4]
  • (4.7)
  • (7,11]
  • (11,15]
  • (15,+∞]

我们首先打开两个mysql 会话 分别叫做客户端A 和 客户端B

  1. 客户端A 打开事务 进行批量更新 不提交事务。
    BEGIN;
    UPDATE gap_locks set name = '测试' where id > 5 and id < 14;
  2. 客户端B 分别插入各个区间中的 数据
    # 客户端B插入一条 id = 3,name = '小张3' 的数据 
    INSERT INTO `gap_locks` (`id`, `name`) VALUES (3, '小张3'); # 正常执行

    # 客户端B插入一条 id = 5,name = '小张5' 的数据
    INSERT INTO `gap_locks` (`id`, `name`) VALUES (5, '小张5'); # 阻塞

    # 客户端B插入一条 id = 8,name = '小张8' 的数据
    INSERT INTO `gap_locks` (`id`, `name`) VALUES (8, '小张8'); # 阻塞

    # 客户端B插入一条 id = 12, name = '小张12' 的数据
    INSERT INTO `gap_locks` (`id`, `name`) VALUES (12, '小张12'); # 阻塞

    # 客户端B插入一条 id = 16, name = '小张16' 的数据
    INSERT INTO `gap_locks` (`id`, `name`) VALUES (16, '小张16'); # 正常执行

从上面我们可以看到,(4, 7]、(11, 15]这两个区间,都不可插入数据,其它区间,都可以正常插入数据。所以我们可以得出结论:当我们给 (5, 14] 这个区间加锁的时候,会锁住 (4, 7],(11,15] 这两个区间。

如果锁住不存在的的数据(截图太累此处直接给案例及结果)

  1. 客户端A 打开事务 更新不存在的数据 不提交事务。
    BEGIN;
    UPDATE gap_locks set name = '测试' where id = 3;
  2. 客户端B 分别插入各个区间中的 数据
    INSERT INTO `gap_locks` (`id`, `name`) VALUES (2, '小张2'); # 阻塞
    INSERT INTO `gap_locks` (`id`, `name`) VALUES (3, '小张3'); # 阻塞
    # 客户端B插入一条 id = 8,name = '小张8' 的数据
    INSERT INTO `gap_locks` (`id`, `name`) VALUES (8, '小张8'); # 正常执行
    # 客户端B插入一条 id = 12, name = '小张12' 的数据
    INSERT INTO `gap_locks` (`id`, `name`) VALUES (12, '小张12'); # 正常执行

结论
结论
1.对于指定查询某一条记录的加锁语句,如果该记录不存在,会产生记录锁和间隙锁,如果记录存在,则只会产生记录锁,如:WHERE id = 5 FOR UPDATE;
2. 对于查找某一范围内的查询语句,会产生间隙锁,如:WHERE id BETWEEN 5 AND 7 FOR UPDATE;

普通索引的间隙锁

临键锁

临键锁(Next-key Locks)
Next-Key Locks是行锁与间隙锁的组合。像上面那个例子1里的这个(4,15]的整个区间可以叫做临键锁。

锁优化建议

  • 尽可能让所有数据检索都通过索引来完成,避免无索引行锁升级为表锁
  • 合理设计索引,尽量缩小锁的范围
  • 尽可能减少检索条件范围,避免间隙锁
  • 尽量控制事务大小,减少锁定资源量和时间长度,涉及事务加锁的sql尽量放在事务最后执行
  • 尽可能低级别事务隔离

INFORMATION_SCHEMA
查看INFORMATION_SCHEMA系统库锁相关数据表

#查看事务
select * from INFORMATION_SCHEMA.INNODB_TRX;
#查看锁
select * from INFORMATION_SCHEMA.INNODB_LOCKS;
#查看锁等待
select * from INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

#释放锁,trx_mysql_thread_id可以从INNODB_TRX表里查看到
kill trx_mysql_thread_id

# 查看锁等待详细信息
show engine innodb status\G;
Tags: mysql
使用支付宝打赏
使用微信打赏

若你觉得我的文章对你有帮助,欢迎点击上方按钮对我打赏

扫描二维码,分享此文章