MySQL实践
第一次遇到mysql的deadlock
1. 实验准备
1.1 常用mysql事务命令
set autocommit=0;
start transaction;
commit;
rollback;
-- mysql 5.6 查看隔离级别
select @@session.tx_isolation;
select @@global.tx_isolation;
-- mysql 8.0 查看隔离级别
select @@session.transaction_isolation;
select @@global.transaction_isolation;
-- 设置数据库隔离级别
set session transaction isolation level repeatable read;
-- 四种隔离级别
read uncommitted
read committed
repeatable read
serializable
1.2 创建表
drop table if exists account;
create table account(
`accountno` int unsigned not null auto_increment,
`value` int not null default 0,
primary key(`accountno`)
)engine=innodb auto_increment=111 charset=utf8;
insert into account (value) values (1500);
2. 脏读实验
开两个窗口：
窗口A：
set autocommit=0;
set session transaction isolation level read uncommitted;
start transaction;
select * from account where accountno=111;
窗口B：
set autocommit=0;
set session transaction isolation level read uncommitted;
start transaction;
update account set value=value+80 where accountno=111;
窗口A：
select * from account where accountno=111;
出现脏读
3. 不可重复读
窗口A：
set autocommit=0;
set session transaction isolation level read committed;
start transaction;
select * from account where accountno=111;
不会出现脏读
窗口B：
set autocommit=0;
set session transaction isolation level read uncommitted;
start transaction;
update account set value=value+80 where accountno=111;
窗口A：
select * from account where accountno=111;
窗口B：
commmit;
窗口A：
select * from account where accountno=111;
出现不可重复读
4. 幻读
窗口A：
set autocommit=0;
set session transaction isolation level repeatable read;
start transaction;
select * from account where accountno=999;
窗口B：
set autocommit=0;
set session transaction isolation level repeatable read;
start transaction;
insert into account (accountno, value) values (999, 700);
commit;
窗口A：
select * from account where accountno=999;
可重复读
窗口A：
insert into account (accountno, value) values (999, 700);
出现幻读
5. next-key防止发生幻读
5.1 innodb解决幻读的方法
- 多版本并发控制（MVCC，快照读）
- next-key锁（当前读）
5.2 next-key
InnoDB是一个支持行锁的存储引擎，有三种排它锁（X锁）：
- Record Lock：行锁，单个行记录上的锁。
- Gap Lock：间隙锁，锁定一个范围，但不包括记录本身。GAP锁的目的，是为了防止幻读、防止间隙内有新数据插入、防止已存在的数据更新为间隙内的数据。
- Next-Key Lock：record lock + gap lock，锁定一个范围，并且锁定记录本身。对于行的查询，都是采用该方法，主要目的是解决幻读的问题。
InnoDB默认加锁方式是next-key 锁。
-- 比如有一个表，id列上有90,100,102
-- 执行如下命令
select * from order where id=100 for update
-- 如果查找到了id为100的记录，则不加锁
-- 否则，mysql会锁住(90,102]这个区间
-- 执行如下命令
select * from order where id>100 for update
-- mysql会锁住(90,+∞)这个区间
-- 执行delete命令同上
-- 如果没有delete成功，同样也会锁住区间
-- 执行update命令
-- 如果执行范围删除，不论是否成功，都会锁区间
-- 执行特定记录删除，select for update
5.3 next-key是如何解决幻读的？
select * from xxx where xxx = ? for update;
- 执行上面这条语句，相当于当前读，成功读该条数据之后，还要加一个next-key排他锁。
- 加完next-key之后，此时如果另外一个事务需要对加了next-key锁的记录进行操作，需要等待，直到锁被释放。从而避免了幻读。
- 同样的，如果在执行第1步的时候，已经有其他事务对这个间隙的记录加了锁，当前事务需要等待直到锁被释放。
5.4 next-key防止幻读实验
窗口A：
set autocommit=0;
set session transaction isolation level repeatable read;
start transaction;
select * from account where accountno>888 for update;
此时(111,+∞)范围的记录 被上锁
窗口B：
set autocommit=0;
set session transaction isolation level repeatable read;
start transaction;
insert into account (accountno, value) values (111, 700);
说明记录111没有被加锁
窗口B：
insert into account (accountno, value) values (112, 700);
记录112被加锁
其实可以验证：(111,+∞)范围的记录 都被上锁
事务B无法对数据进行操作，从而避免了幻读
5.5 总结
防止幻读的关键：进行当前读，然后利用next-key lock来加间隙锁
6. next-key lock 可能会死锁
e) values (112, 700);
[外链图片转存中...(img-qo4RFQLc-1623048416588)]
> 记录112被加锁
>
> 其实可以验证：(111,+∞)范围的记录 都被上锁
[外链图片转存中...(img-THool6wE-1623048416588)]
> 事务B无法对数据进行操作，从而避免了幻读
### 5.5 总结
> 防止幻读的关键：进行当前读，然后利用next-key lock来加间隙锁
## 6. next-key lock 可能会死锁
死锁产生的情况：两个事务，同时进行当前读，并对记录进行加锁，导致记录上加了两层锁。这时，两个事务都要修改记录时，就会出现死锁。