【高并发高性能高可用之海量数据MySQL实战-19】-MySQL锁原理分析实战

不要迷恋发哥

于 2022-04-08 22:00:00 发布

阅读量720

点赞数

分类专栏： MySQL 文章标签： mysql 数据库 database

本文链接：https://blog.csdn.net/chongfa2008/article/details/123999822

版权

MySQL 专栏收录该内容

30 篇文章 6 订阅

订阅专栏

1：一条update语句执行过程

CREATE TABLE t (
id INT PRIMARY KEY,
c VARCHAR(100)
) Engine=InnoDB CHARSET=utf8;

现在表里的数据就是这样的：

然后更新表里的一条数据：

update t set c='曹操' where id = 1;

执行流程：

2：MySQL锁介绍

按照锁的粒度来说，MySQL主要包含三种类型（级别）的锁定机制：

全局锁：锁的是整个database。由MySQL的SQL layer层实现的表级锁：锁的是某个table。由MySQL的SQL layer层实现的

行级锁：锁的是某行数据，也可能锁定行之间的间隙。由某些存储引擎实现，比如InnoDB。按照锁的功能来说分为：共享锁和排他锁。

1：共享锁Shared Locks（S锁）:

1、兼容性：加了S锁的记录，允许其他事务再加S锁，不允许其他事务再加X锁

2、加锁方式：select…lock in share mode

2：排他锁Exclusive Locks（X锁）:

1、兼容性：加了X锁的记录，不允许其他事务再加S锁或者X锁

2、加锁方式：select…for update

3：全局锁

全局锁就对整个数据库实例加锁，加锁后整个实例就处于只读状态，后续的MDL的写语句，DDL语句，已经更新操作的事务提交语句都将被阻塞。其典型的使用场景是做全库的逻辑备份，对所有的表进行锁定，从而获取一致性视图，保证数据的完整性。

加全局锁的命令为：

mysql> flush tables with read lock;

释放全局锁的命令为：

mysql>unlock tables；

或者断开加锁session的连接，自动释放全局锁。

说到全局锁用于备份这个事情，还是很危险的。因为如果在主库上加全局锁，则整个数据库将不能写入，备份期间影响业务运行，如果在从库上加全局锁，则会导致不能执行主库同步过来的操作，造成主从延迟。

对于innodb这种支持事务的引擎，使用mysqldump备份时可以使用--single-transaction参数，利用mvcc提供一致性视图，而不使用全局锁，不会影响业务的正常运行。而对于有MyISAM这种不支持事务的表，就只能通过全局锁获得一致性视图，对应的mysqldump参数为--lock-all-tables。

4：MySQL表级锁

1：表级锁介绍

MySQL的表级锁有四种：

1、表读、写锁。
2、元数据锁（meta data lock，MDL)。
3、自增锁(AUTO-INC Locks)

2：表读S、写锁X

1：表锁相关命令

MySQL 实现的表级锁定的争用状态变量：

mysql> show status like 'table%';

- table_locks_immediate：产生表级锁定的次数；
- table_locks_waited：出现表级锁定争用而发生等待的次数；

表锁有两种表现形式：

表共享读锁（Table Read Lock）
表独占写锁（Table Write Lock）

手动增加表锁：

lock table 表名称 read(write),表名称2 read(write)，其他;

查看表锁情况：

show open tables;

删除表锁：

删除表锁：

2：表锁演示

1：环境准备

CREATE TABLE mylock (
id int(11) NOT NULL AUTO_INCREMENT, 
NAME varchar(20) DEFAULT NULL, 
PRIMARY KEY (id)
);						
						
INSERT	INTO	mylock	(id,NAME)	VALUES	(1,	'a');
INSERT	INTO	mylock	(id,NAME)	VALUES	(2,	'b');
INSERT	INTO	mylock	(id,NAME)	VALUES	(3,	'c');
INSERT	INTO	mylock	(id,NAME)	VALUES	(4,	'd');

2：读锁演示

3：写锁演示

3：元数据锁

1：元数据锁介绍

MDL不需要显式使用，在访问一个表的时候会被自动加上。MDL的作用是，保证读写的正确性。你可以想象一下，如果一个查询正在遍历一个表中的数据，而执行期间另一个线程对这个表结构做变更，删了一列，那么查询线程拿到的结果跟表结构对不上，肯定是不行的。

因此，在 MySQL 5.5 版本中引入了 MDL，当对一个表做增删改查操作的时候，加 MDL 读锁；当要对表做结构变更操作的时候，加 MDL 写锁。

读锁之间不互斥，因此你可以有多个线程同时对一张表增删改查。

读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行。

2：元数据锁演示

session1（Navicat）、session2（mysql）

1、session1: begin;--开启事务
select * from mylock;--加MDL读锁
2、session2: alter table mylock add f int; -- 修改阻塞
3、session1：commit; --提交事务 或者 rollback 释放读锁
4、session2：Query OK, 0 rows affected (38.67 sec)	--修改完成
Records: 0	Duplicates: 0	Warnings: 0

4：自增锁(AUTO-INC Locks)

AUTO-INC锁是一种特殊的表级锁，发生涉及AUTO_INCREMENT列的事务性插入操作时产生。

5：MySQL行级锁

1：行级锁介绍

MySQL的行级锁，是由存储引擎来实现的，这里我们主要讲解InnoDB的行级锁。

InnoDB行锁是通过给索引上的索引项加锁来实现的，因此InnoDB这种行锁实现特点意味着：只有通过索 引条件检索的数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！

InnoDB的行级锁，按照锁定范围来说，分为四种：

记录锁（Record Locks）:锁定索引中一条记录。
间隙锁（Gap  Locks）:要么锁住索引记录中间的值，要么锁住第一个索引记录前面的值或者最后一个索引记录后面的值。
临键锁（Next-Key  Locks）:是索引记录上的记录锁和在索引记录之前的间隙锁的组合（间隙锁+记录锁）。
插入意向锁(Insert Intention Locks)：做insert操作时添加的对记录id的锁。

InnoDB的行级锁，按照功能来说，分为两种：

共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。
排他锁（X）：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。

对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁（X)；对于普通SELECT语句，InnoDB不会加任何锁，事务可以通过以下语句显示给记录集加共享锁或排他锁。

手动添加共享锁（S）：

SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE

手动添加排他锁（x）：

SELECT * FROM table_name WHERE ... FOR UPDATE

案例

CREATE TABLE `t1_simple`	(
`id` int(11) NOT NULL,
`pubtime` int(11) NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE,
INDEX `idx_pu`(`pubtime`) USING BTREE
) ENGINE = InnoDB;
INSERT INTO `t1_simple` VALUES (1, 10);
INSERT INTO `t1_simple` VALUES (4, 3);
INSERT INTO `t1_simple` VALUES (6, 100);
INSERT INTO `t1_simple` VALUES (8, 5);
INSERT INTO `t1_simple` VALUES (10, 1);
INSERT INTO `t1_simple` VALUES (100, 20);

2：意向锁 Intention Locks

1：意向锁介绍

InnoDB也实现了表级锁，也就是意向锁，意向锁是mysql内部使用的，不需要用户干预。意向锁和行锁可以共存，意向锁的主要作用是为了【全表更新数据】时的性能提升。否则在全表更新数据时，需要先检索该范是否某些记录上面有行锁。

表明“某个事务正在某些行持有了锁、或该事务准备去持有锁”
意向锁的存在是为了协调行锁和表锁的关系，支持多粒度（表锁与行锁）的锁并存，。
例子：事务A修改user表的记录r，会给记录r上一把行级的排他锁（X），同时会给user表上一把意向排他锁（IX），这时事务B要给user表上一个表级的排他锁就会被阻塞。意向锁通过这种方式实现了行锁和表锁共存且满足事务隔离性的要求。
1）意向共享锁（IS锁）：事务在请求S锁前，要先获得IS锁2）意向排他锁（IX锁）：事务在请求X锁前，要先获得IX锁

2：意向锁的作用

当我们需要加一个排他锁时，需要根据意向锁去判断表中有没有数据行被锁定（行锁）；

如果意向锁是行锁，则需要遍历每一行数据去确认；
如果意向锁是表锁，则只需要判断一次即可知道有没数据行被锁定，提升性能。

意向锁和共享锁、排他锁的兼容关系

下图表示意向锁和共享锁、排他锁的兼容关系

意向锁相互兼容，因为IX、IS只是表明申请更低层次级别元素（比如 page、记录）的X、S操作。因为上了表级S锁后，不允许其他事务再加X锁，所以表级S锁和X、IX锁不兼容

上了表级X锁后，会修改数据，所以表级X锁和 IS、IX、S、X（即使是行排他锁，因为表级锁定的行肯定包括行级速订的行，所以表级X和IX、行级X）不兼容。

注意：上了行级X锁后，行级X锁不会因为有别的事务上了IX而堵塞，一个mysql是允许多个行级X锁同时存在的，只要他们不是针对相同的数据行。

3：记录锁(Record Locks)

记录锁, 仅仅锁住索引记录的一行，在单条索引记录上加锁。
record lock锁住的永远是索引，而非记录本身，即使该表上没有任何索引，那么innodb会在后台创建一个隐藏的聚集主键索引，那么锁住的就是这个隐藏的聚集主键索引。

所以说当一条sql没有走任何索引时，那么将会在每一条聚合索引后面加X锁，这个类似于表锁，但原理上和表锁应该是完全不同的。

-- 加记录共享锁
select * from t1_simple where id = 1 lock in share mode;
-- 加记录排它锁
select * from t1_simple where id = 1 for update;

4：间隙锁(Gap Locks)

区间锁, 仅仅锁住一个索引区间（开区间，不包括双端端点）。
在索引记录之间的间隙中加锁，或者是在某一条索引记录之前或者之后加锁，并不包括该索引记录本身。
间隙锁可用于防止幻读，保证索引间的不会被插入数据

session1:
begin;
select * from t1_simple where id > 4 for update;
	---------------------------------------------------------
session2:
insert into t1_simple values (7,100); --阻塞
insert into t1_simple values (3,100); --成功

5：临键锁(Next-Key Locks)

record lock + gap lock, 左开右闭区间，例如（5,8]。
默认情况下，innodb使用next-key locks来锁定记录。select … for update
但当查询的索引含有唯一属性的时候，Next-Key Lock 会进行优化，将其降级为Record Lock，即仅锁住索引本身，不是范围。
Next-Key Lock在不同的场景中会退化

当前数据库中的记录信息：

session1执行：

begin;
select * from t1_simple where pubtime = 20 for update; 
	-- 临键锁区间(10,20],(20,100]

session2执行：

insert into t1_simple values (16, 19); --阻塞
select * from t1_simple where pubtime = 20 for update; --阻塞
insert into t1_simple values (16, 50); --阻塞
insert into t1_simple values (16, 101); --成功

6：行锁加锁规则

主键索引
1. 等值查询
  1. 命中记录，加记录锁。
  2. 未命中记录，加间隙锁。
2. 范围查询

没有命中任何一条记录时，加间隙锁。
命中1条或者多条，包含where条件的临键区间，加临键锁

辅助索引
1. 等值查询

命中记录，命中记录的辅助索引项+主键索引项加记录锁，辅助索引项两侧加间隙锁。
未命中记录，加间隙锁
1. 范围查询

没有命中任何一条记录时，加间隙锁。
命中1条或者多条，包含where条件的临键区间加临键锁。命中记录的id索引项加记录锁。

7：插入意向锁(Insert Intention Locks)

插入意向锁是一种Gap锁，不是意向锁，在insert操作时产生。
在多事务同时写入不同数据至同一索引间隙的时候，并不需要等待其他事务完成，不会发生锁等待。
假设有一个记录索引包含键值4和7，不同的事务分别插入5和6，每个事务都会产生一个加在4-7之间的插入意向锁，获取在插入行上的排它锁，但是不会被互相锁住，因为数据行并不冲突。
插入意向锁不会阻止任何锁，对于插入的记录会持有一个记录锁。

8：锁相关参数

Innodb所使用的行级锁定争用状态查看：

mysql> 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（等待总时长）

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

查看事务、锁的sql：

select * from information_schema.innodb_locks;
select * from information_schema.innodb_lock_waits;
select * from information_schema.innodb_trx;

不要迷恋发哥

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
打赏
0
评论
【高并发高性能高可用之海量数据MySQL实战-19】-MySQL锁原理分析实战

1：一条update语句执行过程1CREATE TABLE t (2id INT PRIMARY KEY,3c VARCHAR(100)4) Engine=InnoDB CHARSET=utf8;现在表里的数据就是这样的：然后更新表里的一条数据：update t set c='曹操' where id = 1;执行流程：2：MySQL锁介绍按照锁的粒度来说，MySQL主要包含三种类型（级别）的锁定机制：全局锁：锁的是整个database。由MySQL..
复制链接

扫一扫