MySQL锁机制

发表于

MySQL支持的锁

从锁粒度上划分

1
2
3
scss复制代码表级锁
行级锁(InnoDB存储引擎)
页级锁(BDB存储引擎)

从锁操作上划分

从实现方式上划分

使用场景

修改表结构

1
复制代码修改数据库表结构会自动加表级锁（元数据锁）

行级锁升级表级锁

1
2
复制代码更新数据未使用索引
行级锁会上升为表级锁

更新数据使用索引会使用行级锁

select .... from update使用行级锁

MySQL锁分类

1
复制代码分为乐观锁和悲观锁

乐观锁

1
复制代码乐观锁是程序通过版本号或时间戳实现

悲观锁

表级锁

1
2
3
4
5
复制代码每次操作锁住整个表
锁定力度大
发生锁冲突的概率最高
并发度最低
应用在MyISAM、InnoDB、BDB等存储引擎中

1
2
3
scss复制代码表级锁又分为表锁（MySQL layer层加锁）
元数据锁（MySQL layer层加锁）
意向锁(InnodB存储引擎层加锁) 内部使用的锁
表锁
1
复制代码需要手动加锁

  • read lock
1
2
复制代码加读锁后还可以加读锁
不能加写锁
  • write lock
1
复制代码加写锁后不能加读锁也不能加写锁
元数据锁
1
2
复制代码自动加锁
元数据其实就是表结构

意向锁

行级锁

1
2
3
4
5
复制代码每次操作锁住一行数据
锁定粒度最小
发生锁冲突的概率最低
并发读最高
是由InnoDB存储引擎实现的

共享读锁(S)
1
2
3
4
csharp复制代码手动加锁
select .... lock in share mode
允许一个事务去读一行 
阻止其他事务获取相同数据集的排他锁
排他写锁(X)
1
2
3
4
复制代码自动加锁
允许获得排他写锁的事务更新数据
阻止其他事务取得相同数据集的共享读锁（不是普通读）
和排他写锁
  • DML(insert、update、delete)
  • select … from udpate

整体分类

表级锁使用

表读锁

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
bash复制代码事务1给mylock表添加读锁
事务1查询mylock表可以读取到数据
事务1不可以查询其他表数据

事务2普通查询mylock表 
没有加锁 
可以查到 
通过MVCC机制查询数据的历史版本

事务2更新mylock表id为2的数据 
需要先获取这条数据的行锁
才可以进行修改
但此时是获取不到的
因为事务1还未释放mylock表的读锁
所以事务2只能等待事务1释放mylock表的读锁
才能够获取到行锁

事务1释放了mylock表的读锁
那么则可以查询其他表的数据了

事务2也获取到id为2的这条数据的行锁
执行更新操作

表写锁

1
2
3
4
5
6
7
8
9
10
11
12
13
14
bash复制代码事务1给mylock添加表写锁
事务1可以查询mylock数据
事务1不可以查询其他表数据
事务1更新mylock表id为2的数据
可以执行

事务2查询mylock数据
查询阻塞 
因为事务1还未释放mylock表的写锁

事务1释放mylock表的写锁

事务2的查询得以继续执行
事务1页可以访问其他表数据了

元数据锁的使用

元数据读锁

1
2
3
4
5
6
7
8
9
复制代码事务1开启事务
事务1查询mylock表
此时会加一个MDL读锁

事务2修改mylock表结构
此时会被阻塞

事务1提交事务或回滚事务
事务2才得以修改完成

行级锁分类及使用

查询行级锁状态

1
sql复制代码show status like 'innodb_row_locks'

行级锁的使用

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
bash复制代码事务1 开始事务
事务1 查询mylock表id为1的数据
id列为索引列
加行读锁

事务2更新id为2的数据 
因未锁定该行 所以可以更新

事务2更新id为1的数据
该行的行读锁还未释放
此时修改被阻塞

事务1提交事务
事务2对id为1这条数据的更新才得以执行

"注"
使用索引加行锁
未锁定的行可以访问

行读锁升级为表锁

未使用索引的行级锁会升级为表锁

1
2
3
4
5
6
7
8
9
10
11
12
ini复制代码事务1开始事务
事务1查询mylock表 查询条件是name='c'
并手动给name='c'添加行读锁
但name列并未使用索引
所以行读锁就会升级为表级锁

事务2更新mylock表id为2的数据
就会被阻塞

事务1提交或回滚事务 表级锁就会被释放
事务2的更新操作就可以获取到行级锁 
然后执行更新操作

行写锁

主键索引产生记录锁

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
bash复制代码事务1开始事务
事务1查询mylock表id为1的数据 
并添加行写锁

事务2查询mylock表id为2的数据 
可以访问

事务2查询mylock表id为1的数据
可以访问 不加锁

事务2 查询mylock表id为1的数据 添加读锁
此时被阻塞

事务1提交 释放了id为1的行写锁
事务2 加读锁获取id为1的数据得以执行

行锁原理

主键加锁

1
2
3
bash复制代码id为主键索引
update t set name='x' where id =10;
加锁行为仅在id=10的主键索引记录上加X锁(记录锁)

唯一键加锁

1
2
3
4
5
bash复制代码id为唯一键索引
name为主键索引

先在唯一索引id上加id=10的x锁
再在id=10的主键索引记录上加x锁

非唯一键加锁

1
2
3
4
5
6
7
8
9
scss复制代码name是主键
id是索引（二级索引）

加锁行为：对满足id=10条件的记录和主键分别加x锁
然后在
(6,c)~(10,b)
(10,b)~(10,d)
（10,d)~(11,f)
间隙分别加Gap锁

1
2
复制代码有了间隙锁 
这些区间内不允许其他事务做插入操作

无索引加锁

1
2
3
4
5
6
7
8
bash复制代码name是逐渐
id没有索引
select * from t where id =10; 
会导致全表扫描

加锁行为：表里所有行和间隙均加x锁
由于InnoDB引擎行锁机制基于索引实现记录锁定
因为没有索引时会导致全表锁定

死锁

死锁现象

  • 表锁死锁
  • 行级锁死锁
  • 共享锁转换为排他锁

表级锁死锁

1
2
3
4
5
6
7
8
9
css复制代码用户A先访问表A 对表A加了锁
然后再访问表B
用户B先访问表B 对表B加了锁
然后再访问表A
因表B被加了锁
所以用户A需等着用户B释放了表B的锁才可以对表B加锁
因表A被加了锁
所以用户B需等着用户A释放了表A的锁才可以对表A加锁
所以2者互相等待 从而死锁

解决方案

  • 调整程序的逻辑
1
css复制代码把表A和表B当成同一个资源

1
2
css复制代码用户A访问完表A和表B之后
用户B再来访问表A和表B
  • 尽量避免同时锁定2个资源

行级锁死锁

产生原因1

1
2
3
4
5
rust复制代码在事务中执行了一条不满足for update的操作
则执行全表扫描
把行级锁上升为表级锁
多个这样的事务执行后
就很容易产生死锁和阻塞

解决方案

1
2
sql复制代码SQL语句中不要使用太复杂的关联表的查询 
优化索引

产生原因2

1
2
3
4
5
6
7
8
9
10
11
12
复制代码表中的2个数据id1和id2
事务1先访问id1 对id1加行锁
再访问id2
事务2先访问id2 对id2加行锁
再访问id1
事务1访问id2等待事务2释放id2的行锁
事务2访问id1等待事务1释放id1的行锁
所以事务1和事务2互相等待 阻塞
从而产生死锁

类似于2个表死锁
这个是表中的2个记录行级死锁

解决方案

  • 同一个事务中 尽可能做到一次性锁定所有资源
  • 按照id对资源排序 然后按顺序进行处理
  • 采用MVCC机制处理 普通读 不会使用锁

共享锁转排他锁

1
2
3
4
5
6
7
8
9
10
css复制代码事务A查询一条记录 加共享读锁
事务A更新这条记录
事务B也更新这条记录 需要排他锁
事务B需等待事务A释放了共享锁
才可以获得排他锁进行更新
所以事务B进入了排队等待
事务A也需要排他锁进行更新操作
但是无法授予该 锁请求
因为事务B已经有了一个排他锁请求
并且等待事务A释放其共享锁

解决方案

  • 避免引发对同一条记录的反复操作
  • 使用乐观锁进行控制

本文转载自: 掘金

开发者博客 – 和开发相关的 这里全都有

0%