MySQL（3）锁分类及各锁演示

最新推荐文章于 2021-12-25 14:18:26 发布

Miaoshuowen

最新推荐文章于 2021-12-25 14:18:26 发布

阅读量463

点赞数 3

分类专栏： # MySQL 文章标签： mysql

本文链接：https://blog.csdn.net/Miaoshuowen/article/details/104758032

版权

MySQL 专栏收录该内容

9 篇文章 1 订阅

订阅专栏

MySQL 锁分类

在这里插入图片描述

按照锁的粒度来说，MySQL主要包含三种类型（级别）的锁定机制：
全局锁：锁的是整个database。由MySQL的SQL layer层实现的
表级锁：锁的是某个table。由MySQL的SQL layer层实现的
行级锁：锁的是某行数据，也可能锁定行之间的间隙。由某些存储引擎实现，比如InnoDB。
按照锁的功能来说分为：
共享读锁和排他写锁。
按照锁的实现方式分为：
悲观锁和乐观锁（使用某一版本列或者唯一列进行逻辑控制）
表级锁和行级锁的区别：
表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低；
行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高；

MySQL表级锁

表级锁介绍

由MySQL SQL layer层实现

MySQL的表级锁有两种：
一种是表锁。
一种是元数据锁（meta data lock，MDL)。
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;

删除表锁

unlock tables;

表锁演示

建表语句

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');

读锁演示

1、表读锁
session1和session2为两个独立的连接

1、session1: lock table mylock read; -- 给mylock表加读锁 
2、session1: select * from mylock; -- 可以查询 
3、session1：select * from tuser; --不能访问非锁定表 
4、session2：select * from mylock; -- 可以查询 没有锁 
5、session2：update mylock set name='x' where id=2; -- 修改阻塞,自动加行写锁 
6、session1：unlock tables; -- 释放表锁 
7、session2：Rows matched: 1 Changed: 1 Warnings: 0 -- 修改执行完成 
8、session1：select * from tuser; --可以访问

2、表写锁

1、session1: lock table mylock write; -- 给mylock表加写锁 
2、session1: select * from mylock; -- 可以查询 
3、session1：select * from tdep; --不能访问非锁定表 
4、session1：update mylock set name='y' where id=2; --可以执行 
5、session2：select * from mylock; -- 查询阻塞 
6、session1：unlock tables; -- 释放表锁 
7、session2：4 rows in set (22.57 sec) -- 查询执行完成 
8、session1：select * from tdep; --可以访问

元数据锁介绍

MDL不需要显式使用，在访问一个表的时候会被自动加上。MDL的作用是，保证读写的正确性。你可以想象一下，如果一个查询正在遍历一个表中的数据，而执行期间另一个线程对这个表结构做变更，删了一列，那么查询线程拿到的结果跟表结构对不上，肯定是不行的。
因此，在 MySQL 5.5 版本中引入了 MDL，当对一个表做增删改查操作的时候，加 MDL 读锁；当要对表做结构变更操作的时候，加 MDL 写锁。
读锁之间不互斥，因此你可以有多个线程同时对一张表增删改查。
读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行。

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 123456

行锁演示

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

行读锁

查看行锁状态

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：系统启动后到现在总共等待的次数；

1、session1: begin;--开启事务未提交 
	select * from mylock where ID=1 lock in share mode; 
	--手动加id=1 的行读锁,使用索引 
2、session2：update mylock set name='y' where id=2; 
	-- 未锁定该行可以修改 
3、session2：update mylock set name='y' where id=1; 
	-- 锁定该行修改阻塞 ERROR 1205 (HY000): Lock wait timeout exceeded; 
	try restarting transaction -- 锁定超时 
4、session1: commit; 
	--提交事务 或者 rollback 释放读锁 
5、session2：update mylock set name='y' where id=1; 
	--修改成功 Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 
	注：使用索引加行锁 ，未锁定的行可以访问

行读锁升级为表锁

注：未使用索引行锁升级为表锁

1、session1: begin;
	--开启事务未提交 --手动加name='c'的行读锁,未使用索引 
	select * from mylock where name='c' lock in share mode; 
2、session2：update mylock set name='y' where id=2; 
	-- 修改阻塞 未用索引行锁升级 为表锁 
3、session1: commit; 
	--提交事务 或者 rollback 释放读锁 
4、session2：update mylock set name='y' where id=2; 
	--修改成功 Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0

行写锁

1、session1: begin;
	--开启事务未提交 --手动加id=1的行写锁, 
	select * from mylock where id=1 for update; 
2、session2：select * from mylock where id=2 ; 
	-- 可以访问 
3、session2: select * from mylock where id=1 ; 
	-- 可以读 不加锁 
4、session2: select * from mylock where id=1 lock in share mode ; 
	-- 加读锁被阻塞 
5、session1：commit; 
	-- 提交事务 或者 rollback 释放写锁 
5、session2：执行成功

间隙锁

建表语句

CREATE TABLE news (
	id INT, 
	number INT,
	PRIMARY KEY (id)
	); 
insert into news value（1,2）;
insert into news value（3,4）;
insert into news value（6,5）;
......
alter table news add index idx_num(number);

表数据

id（主键）	number（二级索引）
1	2
3	4
6	5
8	5
10	5
13	11

间隙锁防止两种情况

1、防止插入间隙内的数据
2、防止已有数据更新为间隙内的数据

间隙的范围
update news set number=3 where number=4;
number : 2 3 4
id:1 2 3 4 5

间隙情况：

id、number均在间隙内
id、number均在间隙外
id在间隙内、number在间隙外
id在间隙外，number在间隙内
id、number为边缘数据

具体间隙划分参考博客链接

非唯一索引等值间隙锁

非唯一索引间隙范围划分：

向左取得最靠近的值作为左区间
向右取得最靠近的值作为右区间
注：非主键索引产生间隙锁，主键范围产生间隙锁；
主键索引的间隙范围根据非唯一索引的间隙范围确定

session 1: start transaction ; 
	update news set number=3 where number=4; 
session 2: start transaction ; 
	insert into news value(2,3);#（均在间隙内，阻塞） 
	insert into news value(7,8);#（均在间隙外，成功） 
	insert into news value(2,8);#（id在间隙内，number在间隙外，成功） 
	insert into news value(4,8);#（id在间隙内，number在间隙外，成功） 
	insert into news value(7,3);#（id在间隙外，number在间隙内，阻塞） 
	insert into news value(7,2);# (id在间隙外，number为上边缘数据，阻塞) 
	insert into news value(2,2);#（id在间隙内，number为上边缘数据，阻塞） 
	insert into news value(7,5);#（id在间隙外，number为下边缘数据，成功） 
	insert into news value(4,5);#（id在间隙内，number为下边缘数据，阻塞）

结论：

只要number（where后面的）在间隙里（2 3 4），不包含最后一个数（下边缘）则不管id是多少都会阻塞
id在间隙内时，number为下边缘值时阻塞；id在间隙外时，number为下边缘值时不会阻塞

主键索引间隙锁

--主键索引范围 
session 1: start transaction ; 
	update news set number=3 where id>1 and id <6; 
session 2: start transaction ; 
	insert into news value(2,3);#（均在间隙内，阻塞） 
	insert into news value(7,8);#（均在间隙外，成功） 
	insert into news value(2,8);#（id在间隙内，number在间隙外，阻塞） 
	insert into news value(4,8);#（id在间隙内，number在间隙外，阻塞） 
	insert into news value(7,3);#（id在间隙外，number在间隙内，成功） 
	--id无边缘数据，因为主键不能重复

结论：

只要id（在where后面的）在间隙里(2 4 5)，则不管number是多少都会阻塞
所以当间隙锁在主键时，阻塞与否只与主键范围有关

非唯一索引无穷大

--无穷大 
session 1: start transaction ; 
	update news set number=3 where number=13 ; 
session 2: start transaction ; 
	insert into news value(11,5);#(执行成功) 
	insert into news value(12,11);#(执行成功) 
	insert into news value(14,11);#(阻塞) 
	insert into news value(15,12);#(阻塞)

结论：

检索条件number=13,向左取得最靠近的值11作为左区间，向右由于没有记录因此取得无穷大作为右区间，因此，session 1的间隙锁的范围（11，无穷大）
id和number同时满足

死锁

两个 session 互相等等待对方的资源释放之后，才能释放自己的资源，造成了死锁

1、session1: begin;--开启事务未提交 
	--手动加行写锁 id=1 ，使用索引 
	update mylock set name='m' where id=1; 
2、session2：begin;
	--开启事务未提交 
	--手动加行写锁 id=2 ，使用索引 
	update mylock set name='m' where id=2; 
3、session1: update mylock set name='nn' where id=2; 
	-- 加写锁被阻塞 
4、session2：update mylock set name='nn' where id=1; 
	-- 加写锁会死锁，不允许操作 ERROR 1213 (40001): Deadlock found when trying to get lock; 
	try restarting transaction

Miaoshuowen

关注

3
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL（3）锁分类及各锁演示

MySQL 锁分类按照锁的粒度来说，MySQL主要包含三种类型（级别）的锁定机制：全局锁：锁的是整个database。由MySQL的SQL layer层实现的表级锁：锁的是某个table。由MySQL的SQL layer层实现的行级锁：锁的是某行数据，也可能锁定行之间的间隙。由某些存储引擎实现，比如InnoDB。按照锁的功能来说分为：共享读锁和排他写锁。按照锁的实现方式分为：悲...
复制链接

扫一扫

专栏目录