脏读、不可重复读、幻读

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;
  1. 执行上面这条语句,相当于当前读,成功读该条数据之后,还要加一个next-key排他锁。
  2. 加完next-key之后,此时如果另外一个事务需要对加了next-key锁的记录进行操作,需要等待,直到锁被释放。从而避免了幻读。
  3. 同样的,如果在执行第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 可能会死锁

死锁产生的情况:两个事务,同时进行当前读,并对记录进行加锁,导致记录上加了两层锁。这时,两个事务都要修改记录时,就会出现死锁。
上一篇:mysql 错 Could not open JDBC Connection for transaction; nested exception is java.sql.SQLExceptio


下一篇:Laravel 嵌套事务 transactions