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; # 正常提交, 且在第一条记录加了第二个读锁
读锁总结 :
select ... lock in share mode 加的是行级读锁
多个读锁是可以重复加的
读锁是可读不可写的
事务结束(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 ;
写锁总结:
for update 加的是行级写锁
加上写锁后不能再加读锁 / 写锁
写锁是可读不可写的
事务结束(commit / rollback)后锁自动解除
悲观锁 : mysql实现的锁都是悲观锁, 尤其是X锁
乐观锁 : 需要程序实现锁, 一般使用版本字段或者时间戳实现
乐观锁工作原理 : 在数据表增加version字段, 当事务修改行记录并提交时version增加1, 其他事务在开启时先读当前的version, 对行记录修改后再比对当前的version和一开始的version是否一致 ; 如果一致表示这段时间内没有其他事务修改记录, 事务提交 ; 否则回滚, 并重新开始事务
加写锁 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 ;
隐式加锁总结 :
排他锁在没有使用索引时会隐式加排他锁, 且加的是表锁
隐式加锁的情况
insert , update , delete 都会隐式地加行锁
select 不加任何锁, select 语句也不会触发锁机制
排他锁在没有使用索引时会隐式加排他锁, 且加的是表锁
行级锁会锁定任何符合条件的行
对读到的数据加行锁, 对之后插入的满足加锁条件的数据也加行锁 , 目的是防止幻读
# 事务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个条件
互斥 : 一个资源在一个时刻只给一个进程使用
不可剥夺 : 资源被一个进程占用后只能由该进程自己释放
请求和保持 : 进程申请新的资源时不会释放自己持有的资源
循环等待 : 在死锁形成后必然出现一个循环等待资源的队列
死锁产生的原因总结 :
在事务中对数据的加锁顺序不一致
死锁避免的方法
允许所有资源都能共享使用 (破坏互斥夺)
当一个事务等待资源超过一定时间要主动释放持有的资源 (破坏不可剥夺)
静态法 : 在事务开启之初先获取到全部需要的资源 (破坏请求和保持)
动态法 : 保证事务在获取下一资源时已经释放持有的全部资源 (破坏请求和保持)
对资源进行标记, 强迫事务必须按照一定规则获取资源(破坏加锁顺序不一致/破坏循环等待)
死锁的排查
关闭autocommit;
查看死锁日志
show eneign innodb status;
# 找到lock wait , deadlock内容