# 准备数据
create table t_deadlock(
id int not null auto_increment,
name varchar(20),
type int,
key (type),
primary key (id)
);
insert into t_deadlock (name, type) VALUES ('zs', 1);
insert into t_deadlock (name, type) VALUES ('ls', 2);
insert into t_deadlock (name, type) VALUES ('ww', 3);
# 需求: 对zs的type做加1操作, 为防止资源抢夺(更新丢失), 设置锁
--事务1-------------
begin;
select type from t_deadlock where name='zs' lock in share
mode; # 共享锁
--事务2-------------
begin;
select type from t_deadlock where name='zs' lock in share
mode; # 共享锁
--事务1-------------
update t_deadlock set type=2 where name='zs'; # 等待事务2释放
共享锁
--事务2-------------
update t_deadlock set type=2 where name='zs'; # 等待事务1释放
共享锁
# 相互等待, 产生死锁
# 更新丢失的解决办法:
1.使⽤用update子查询更更新 (乐观锁)
update t_deadlock set type=type+1 where name='zs';
2.查询时直接使用排它锁 (悲观锁)
select type from t_deadlock where name='zs' for update;