欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

14. MySQL锁介绍

程序员文章站 2022-09-14 14:06:06
1. 锁介绍按照锁的粒度来说,MySQL主要包含三种类型(级别)的锁定机制:全局锁:锁的是整个database。由MySQL的SQL layer层实现的表级锁:锁的是某个table。由MySQL的SQL layer层实现的行级锁:锁的是某行数据,也可能锁定行之间的间隙。由某些存储引擎实现,比如InnoDB。按照锁的功能来说分为:共享读锁和排他写锁。按照锁的实现方式分为:悲观锁和乐观锁(使用某一版本列或者唯一列进行逻辑控制)表级锁和行级锁的区别:表级锁:开销小,加锁快;不会出现死锁;...

1. 锁介绍

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

14. MySQL锁介绍

2. MySQL表级锁

由MySQL SQL layer层实现

  • MySQL的表级锁有两种:表锁和原数据锁
  • MySQL实现的表级锁定的争用状态变量:
show status like 'table%';

14. MySQL锁介绍

# 产生表级锁定的次数
table_locks_immediate

# 出现表级锁定争用而发生等待的次数
table_locks_waited

1. 表锁

1. 表锁介绍
  • 表锁有两种表现形式
    • 表共享读锁(Table Read Lock)
    • 表独占写锁(Table Write Lock)
  • 手动增加表锁
lock table 表名称 read(write), 表名称2 read(write), 其他;
  • 查看表锁情况
show open tables;
  • 删除表锁
unlock tables;
2. 环境准备
--新建表 
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');
3. 表读锁
1、session1: lock table mylock read; -- 给mylock表加读锁 
2、session1: select * from mylock; -- 可以查询 
3、session1:select * from tdep; --不能访问非锁定表 
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 tdep; --可以访问

14. MySQL锁介绍

​ 当前session给某一个表加上表读锁时,当前 session 和其他的 session 都可以对该表进行读,当前session 不能访问其他没有锁定的表,其他 session 不可以对该表进行修改。

4. 表写锁
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; --可以访问

14. MySQL锁介绍

​ 当前 session 给某一个表加表读锁时,当前 session 可以对该表进行修改,但不能访问非锁定表, 其他 session 不能对该表进行查询。

2. 元数据锁

1. 元数据锁介绍

MDL不需要显式使用,在访问一个表的时候会被自动加上。MDL的作用是,保证读写的正确性

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

  • 读锁之间不互斥,因此你可以有多个线程同时对一张表增删改查。
  • 读写锁之间、写锁之间是互斥的,用来保证变更表结构操作的安全性。因此,如果有两个线程要同时给一个表 加字段,其中一个要等另一个执行完才能开始执行。
2. MDL演示
1、session1: begin;--开启事务 
2、session1: select * from mylock;--加MDL读锁 
3、session2: select * from mylock; -- 查询不阻塞
4、session2: alter table mylock add e int; -- 修改阻塞 
5、session1:commit; --提交事务 或者 rollback 释放读锁 
6、session2:Query OK, 0 rows affected (8.83 sec)
						Records: 0  Duplicates: 0  Warnings: 0 --修改完成 

14. MySQL锁介绍

​ session1先启动,这时候会对表mylock 加一个MDL读锁,session2 执行查询时,不会阻塞,对表结构做修改时,会阻塞。

3. MySQL行级锁

1. 介绍

MySQL的行级锁,是由存储引擎来实现的,利用存储引擎锁住索引项来实现的。因此InnoDB这种行锁实现特点意味着:只有通过索引条件检索的数据,InnoDB才使用行级锁,否则,InnoDB将使用表锁!

innodb所使用的行级锁定争用状态查看:

show status like 'innodb_row_lock%';

14. MySQL锁介绍

- Innodb_row_lock_current_waits:当前正在等待锁定的数量;

- Innodb_row_lock_time:从系统启动到现在锁定总时间长度(重要);

- Innodb_row_lock_time_avg:每次等待所花平均时间(重要);

- Innodb_row_lock_time_max:从系统启动到现在等待最常的一次所花的时间;

- Innodb_row_lock_waits:系统启动后到现在总共等待的次数(重要);

InnoDB的行级锁,按照锁定范围来说,分为三种:

  • 记录锁(Record Locks):锁定索引中一条记录。

  • 间隙锁(Gap Locks):要么锁住索引记录中间的值,要么锁住第一个索引记录前面的值或者最后一个索引记录后面的值。

  • Next-Key Locks:是索引记录上的记录锁和在索引记录之前的间隙锁的组合。

InnoDB的行级锁,按照功能来说,分为两种: RR

  • 共享锁(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

InnoDB也实现了表级锁,也就是意向锁,意向锁是mysql内部使用的,不需要用户干预。

  • 意向共享锁(IS):事务打算给数据行加行共享锁,事务在给一个数据行加共享锁前必须先取得该表的IS锁。

  • 意向排他锁(IX):事务打算给数据行加行排他锁,事务在给一个数据行加排他锁前必须先取得该表的IX锁。

意向锁和行锁可以共存,意向锁的主要作用是为了全表更新数据时的性能提升。否则在全表更新数据时, 需要先检索该表是否某些记录上面有行锁。

共享锁(S) 排他锁(X) 意向共享锁(IS) 意向排他锁(IX)
共享锁(S) 兼容 冲突 兼容 冲突
排他锁(X) 冲突 冲突 冲突 冲突
意向共享锁(IS) 兼容 冲突 兼容 兼容
意向排他锁(IX) 冲突 冲突 兼容 兼容

2. 行读锁

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

14. MySQL锁介绍

3. 行读锁升级为表锁

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

14. MySQL锁介绍

4. 行写锁

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

14. MySQL锁介绍

5. 间隙锁

# 创建表
create table news (id int, number int, primary key(id));

# 插入数据
insert into news values(1,2);
insert into news values(3,4);
insert into news values(6,5);
insert into news values(8,5);
insert into news values(10,5);
insert into news values(13,11);

# 添加唯一索引
alter table news add index idx_num(number);
session1:
start transaction ; 
select * from news where number=4 for update ;

session 2:
start transaction;
insert into news value(2,4);#(阻塞) 
insert into news value(2,2);#(阻塞) 
insert into news value(4,4);#(阻塞) 
insert into news value(4,5);#(阻塞) 
insert into news value(7,5);#(执行成功) 
insert into news value(9,5);#(执行成功) 
insert into news value(11,5);#(执行成功) 
​````
注:id和number都在间隙内则阻塞。
session 1:
start transaction ; 
select * from news where number=13 for update ; 
(select * from mylock where id>1 and id < 4 for update) 

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,无穷大)

6. 死锁

两个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

本文地址:https://blog.csdn.net/qq_36630853/article/details/107140916