mysql锁

锁的颗粒

• 行级锁

• 表锁

• 间隙锁(不需要了解)

锁的类型

1. 读锁(共享锁 / S锁) : 一个事务加了s锁后, 其他事务只能读不能写, 其他事务可以再加读锁

select ... from table lock in share mode ;

2. 写锁(排他锁 / X锁 ) : 一个事务加了x锁后, 其他事务只能读不能写 , 不能再加锁

update ...
insert ...
delete ...
select ... for update ;

3. 无锁 : select 语句直接查完全不受锁影响, 既不触发加锁也不被已有的锁限制

select ... from table ; 

读锁测试:

# id 为主键索引 , num 不是索引 , 表名叫i
# 事务A
begin;
select * from i where id = 1 lock in share mode;        # 第一条记录加读锁

# 事务B
begin; 
update i set a = 11 where id =1 ;           # 阻塞
update i set a = 22 where id =2 ;           # 正常提交
select * from i where id = 1 lock in share mode;        # 正常提交, 且在第一条记录加了第二个读锁

读锁总结 :

1. select ... lock in share mode 加的是行级读锁

2. 多个读锁是可以重复加的

3. 读锁是可读不可写的

4. 事务结束(commit / rollback)后锁自动解除

写锁测试

# 事务A 
begin ; 
select * from i where id = 1 for update ;
# 事务B
begin;
select * from i where id = 1;       # 正常读取
update i set a = 11 where id= 1;    # 阻塞
update i set a = 22 where id= 2;    # 正常执行
select * from i where id = 1 for update ;       # 阻塞 , 无法再加写锁
select * from i where id = 1 lock in share mode;    # 阻塞 , 无法再加读锁

# 事务A
commit ;
# 事务B
commit ;

写锁总结:

1. for update 加的是行级写锁

2. 加上写锁后不能再加读锁 / 写锁

3. 写锁是可读不可写的

4. 事务结束(commit / rollback)后锁自动解除

锁的实现

• 悲观锁 : mysql实现的锁都是悲观锁, 尤其是X锁

• 乐观锁 : 需要程序实现锁, 一般使用版本字段或者时间戳实现

乐观锁工作原理 : 在数据表增加version字段, 当事务修改行记录并提交时version增加1, 其他事务在开启时先读当前的version, 对行记录修改后再比对当前的version和一开始的version是否一致 ; 如果一致表示这段时间内没有其他事务修改记录, 事务提交 ; 否则回滚, 并重新开始事务

image.png

双锁冲突

	读锁	写锁
读锁	不冲突	冲突
写锁	冲突	冲突

锁冲突结论: 只有读锁之间不冲突

sql 语句手动加锁

1. 加读锁 select ... lock in share mode;

2. 加写锁 select ... for update ;

注意 : 如果select 的条件中未使用到索引, for update 将会变成表锁 ; 不仅是select...for update ,update 也是一样

select ... for update 测试: 不使用索引会导致全表写锁

第一种情况 : 索引上使用不等于

# id 为主键索引 , num 不是索引 , 表名叫i
set autocommit = 0 ; 
# 事务A
begin;
select * from i where  id <> 1 for update;      # 全表锁

# 事务B
begin ; 
update i set num=1 where id = 1 ;           # 阻塞

# 事务A
commit ;
# 事务B正常提交
commit ;

第二种情况 : 查询条件只使用非索引字段

# 事务A
begin ;
select * from i where num = 1 for update;       # 全表锁

# 事务B
begin;
update i set num = 22 where num = 2 ;           # 阻塞

# 事务A
commit ;
# 事务B正常提交
commit ;

update 测试: 不使用索引会导致全表写锁

# 先创建两个记录, num 分别为 1, 2
# 事务A
begin ;
update i set num = 10 where num = 1 ;       # 全表锁
# 事务B正常提交
begin ;
update i set num = 11 where num = 1 ;       # 阻塞
update i set num = 20 where num = 2 ;       # 阻塞, 证明全表锁了

# 事务A
commit ;
# 事务B
commit ;

隐式加锁总结 :

排他锁在没有使用索引时会隐式加排他锁, 且加的是表锁

隐式加锁的情况

1. insert , update , delete 都会隐式地加行锁

2. select 不加任何锁, select 语句也不会触发锁机制

3. 排他锁在没有使用索引时会隐式加排他锁, 且加的是表锁

行级锁会锁定任何符合条件的行

对读到的数据加行锁, 对之后插入的满足加锁条件的数据也加行锁 , 目的是防止幻读

测试:

# 事务A
begin;
select * from i where num = 1 for update ;      # id 1 加行写锁
# SELECT * from i where num  =1 LOCK in SHARE MODE; # id 1 加行读锁

# 事务B 
begin; 
insert into i (num) value (1) ;      # 阻塞
insert into i (num) value (2) ;      # 不阻塞

# 事务A
commit ;
# 事务B
commit ;

行级锁范围总结:

新插入的数据如果满足加锁条件是不允许插入的, 无论是读锁还是写锁下

死锁产生的4个条件

1. 互斥 : 一个资源在一个时刻只给一个进程使用

2. 不可剥夺 : 资源被一个进程占用后只能由该进程自己释放

3. 请求和保持 : 进程申请新的资源时不会释放自己持有的资源

4. 循环等待 : 在死锁形成后必然出现一个循环等待资源的队列

死锁产生的原因总结 :

在事务中对数据的加锁顺序不一致

死锁避免的方法

1. 允许所有资源都能共享使用 (破坏互斥夺)

2. 当一个事务等待资源超过一定时间要主动释放持有的资源 (破坏不可剥夺)

3. 静态法 : 在事务开启之初先获取到全部需要的资源 (破坏请求和保持)

4. 动态法 : 保证事务在获取下一资源时已经释放持有的全部资源 (破坏请求和保持)

5. 对资源进行标记, 强迫事务必须按照一定规则获取资源(破坏加锁顺序不一致/破坏循环等待)

死锁的排查

1. 关闭autocommit;

2. 查看死锁日志

show eneign innodb status;
# 找到lock wait , deadlock内容

f322dc80608df70e6b11caba70e4b0d.png afcd4e2ab971ad475a7ba5c89cde0b5.png

3. 找到相关的sql语句

4. 模拟复现场景, 分析异常语句

死锁检测和恢复

触发检测的条件:

1. 定时检测

2. 进程阻塞检测

3. 系统资源利用下降检测

死锁恢复方案 :

1. 撤消所有的死锁进程

2. 连续撤消死锁进程直至不再存在死锁

3. 连续剥夺资源直到不再存在死锁

4. 把每个死锁进程备份到前面定义的某个检查点，并重新启动所有进程

死锁、活锁、饥饿

饥饿: 进程长时间等待资源, 导致对进程的功能造成影响 ; 饥饿可能由于分配算法不公平导致永久等待

活锁 : 两个及以上进程连续重复相同的交互以响应其他进程的更改而没有做任何有用的工作, 没有阻塞, 也没有进展; 当特定系统中允许的进程总数应该由进程表中的条目总数定义时，就会发生活锁