MySQL支持的锁

从锁粒度上划分

表级锁
行级锁(InnoDB存储引擎)
页级锁(BDB存储引擎)

从锁操作上划分

从实现方式上划分

使用场景

修改表结构

修改数据库表结构会自动加表级锁（元数据锁）

行级锁升级表级锁

更新数据未使用索引
行级锁会上升为表级锁

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

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

MySQL锁分类

分为乐观锁和悲观锁

乐观锁

乐观锁是程序通过版本号或时间戳实现

悲观锁

表级锁

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

表级锁又分为表锁（MySQL layer层加锁）
元数据锁（MySQL layer层加锁）
意向锁(InnodB存储引擎层加锁) 内部使用的锁

表锁

需要手动加锁

• read lock

加读锁后还可以加读锁
不能加写锁

• write lock

加写锁后不能加读锁也不能加写锁

元数据锁

自动加锁
元数据其实就是表结构

意向锁

行级锁

每次操作锁住一行数据
锁定粒度最小
发生锁冲突的概率最低
并发读最高
是由InnoDB存储引擎实现的

共享读锁(S)

手动加锁
select .... lock in share mode
允许一个事务去读一行 
阻止其他事务获取相同数据集的排他锁

排他写锁(X)

自动加锁
允许获得排他写锁的事务更新数据
阻止其他事务取得相同数据集的共享读锁（不是普通读）
和排他写锁

• DML(insert、update、delete)
• select ... from udpate

整体分类

表级锁使用

表读锁

事务1给mylock表添加读锁
事务1查询mylock表可以读取到数据
事务1不可以查询其他表数据

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

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

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

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

表写锁

事务1给mylock添加表写锁
事务1可以查询mylock数据
事务1不可以查询其他表数据
事务1更新mylock表id为2的数据
可以执行

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

事务1释放mylock表的写锁

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

元数据锁的使用

元数据读锁

事务1开启事务
事务1查询mylock表
此时会加一个MDL读锁

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

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

行级锁分类及使用

查询行级锁状态

show status like 'innodb_row_locks'

行级锁的使用

事务1 开始事务
事务1 查询mylock表id为1的数据
id列为索引列
加行读锁

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

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

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

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

行读锁升级为表锁

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

事务1开始事务
事务1查询mylock表 查询条件是name='c'
并手动给name='c'添加行读锁
但name列并未使用索引
所以行读锁就会升级为表级锁

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

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

行写锁

主键索引产生记录锁

事务1开始事务
事务1查询mylock表id为1的数据 
并添加行写锁

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

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

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

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

行锁原理

主键加锁

id为主键索引
update t set name='x' where id =10;
加锁行为仅在id=10的主键索引记录上加X锁(记录锁)

唯一键加锁

id为唯一键索引
name为主键索引

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

非唯一键加锁

name是主键
id是索引（二级索引）

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

有了间隙锁 
这些区间内不允许其他事务做插入操作

无索引加锁

name是逐渐
id没有索引
select * from t where id =10; 
会导致全表扫描

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

死锁

死锁现象

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

表级锁死锁

用户A先访问表A 对表A加了锁
然后再访问表B
用户B先访问表B 对表B加了锁
然后再访问表A
因表B被加了锁
所以用户A需等着用户B释放了表B的锁才可以对表B加锁
因表A被加了锁
所以用户B需等着用户A释放了表A的锁才可以对表A加锁
所以2者互相等待 从而死锁

解决方案

• 调整程序的逻辑

把表A和表B当成同一个资源

用户A访问完表A和表B之后
用户B再来访问表A和表B

• 尽量避免同时锁定2个资源

行级锁死锁

产生原因1

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

解决方案

SQL语句中不要使用太复杂的关联表的查询 
优化索引

产生原因2

表中的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机制处理 普通读 不会使用锁

共享锁转排他锁

事务A查询一条记录 加共享读锁
事务A更新这条记录
事务B也更新这条记录 需要排他锁
事务B需等待事务A释放了共享锁
才可以获得排他锁进行更新
所以事务B进入了排队等待
事务A也需要排他锁进行更新操作
但是无法授予该 锁请求
因为事务B已经有了一个排他锁请求
并且等待事务A释放其共享锁

解决方案

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