MySQL RR 与 锁

引言

之前在面试准备期间写过相关的笔记,但时间仓促,未进行详述,对大家参考意义不大。

image.png

MySQL数据库面试/开发中,事务一直是重中之重,这些知识在之前面试的时候学过,到现在也记得不太清楚了,本文带领大家一起来回顾MySQL的事务知识。

精讲

前置知识

事务通常并发执行,为了避免事务并发造成的一系列问题,数据库设计四大事务隔离级别:

  • READ-UNCOMMITTED
  • READ-COMMITTED
  • REPEATABLE-READ
  • SERIALIZABLE

MySQL默认的事务隔离级别是REPEATABLE-READOracle默认的
事务隔离级别是READ-COMMITTED

MySQL的众多引擎中,只有InnoDB引擎支持事务与外键,而另一常用的MyISAM引擎虽不支持事务、外键,但访问速度快。

下图来自:《深入浅出MySQL数据库开发优化与管理维护》

image.png

场景

我们聊聊最难理解的REPEATABLE-READ级别。

READ-COMMITED隔离级别下,会出现以下问题:

事务A 事务B
开启事务 开启事务
查询账户余额为3000元 /
/ 更新账户余额为3500元
/ 提交事务
使用相同的条件去查询账户余额,发现余额变为了3500元 /
提交事务 /

事务A就很蒙圈,两次查询结果不一样,我以哪个为准呢?要是以3500为准,那如果3500也被人改了呢?事务A不知道咋办了。

事务中多次读取结果不一致,这种现象为不可重复读。

实践

新建一张基于InnoDB引擎的表account

image.png

通过SELECT @@tx_isolation;查询当前事务隔离级别。

image.png

Navicat开启用于测试的查询Session

事务A 事务B 事务C
START TRANSACTION; / /
SELECT * FROM account WHERE id = 1; / /
image.png / /
/ START TRANSACTION; /
/ UPDATE account SET balance = 3500 WHERE id = 1; /
/ COMMIT; /
/ / START TRANSACTION;
SELECT * FROM account WHERE id = 1; / SELECT * FROM account WHERE id = 1;
image.png / image.png
COMMIT; / COMMIT;

通过本示例说明,普通的SELECTSELECT * FROM account WHERE id = 1,没有加锁。因为如果加了锁,InnoDB行级锁下会拒绝写操作。

出现问题了,虽然解决了不可重复读的问题,但是以上帝视角来看,以3000进行运算,最终的结果还是错误的。

这时候就需要锁了。

共享锁(读锁):读时加锁,允许其他事务共享锁,但不允许其他事务获取排它锁。

排它锁(写锁):写时加锁,不允许其他事务获取共享、排它锁。

共享锁、排它锁,也成为读、写锁,锁为什么这么设计不用详细描述相信大家也能理解。

DML:数据库操作语言(不要小看概念,有人面试就挂在DML上了),也就是我们常用的SELECT、INSERT、UPDATE、DELETE等操作语句。

InnoDB引擎下,默认INSERT、UPDATE、DELETE会对相关记录加行级排它锁,默认SELECT不加锁。

可通过lock in share mode加共享锁,for update加排它锁。

共享锁示例:

事务A 事务B
START TRANSACTION; START TRANSACTION;
SELECT * FROM account WHERE id = 1 LOCK IN SHARE MODE; /
image.png /
/ SELECT * FROM account WHERE id = 1;
/ 不加锁查询:image.png
/ SELECT * FROM account WHERE id = 1 LOCK IN SHARE MODE;
/ 其他事务拥有共享锁,本事务获取共享锁成功:image.png
/ SELECT * FROM account WHERE id = 1 FOR UPDATE;
/ 其他事务拥有共享锁,本事务获取排它锁失败:image.png
/ UPDATE account SET balance = 3500 WHERE id = 1;
/ UPDATE语句获取排它锁,同样失败:image.png
COMMIT; /
/ COMMIT;

排它锁示例:

事务A 事务B
START TRANSACTION; START TRANSACTION;
SELECT * FROM account WHERE id = 1 FOR UPDATE; /
image.png /
/ SELECT * FROM account WHERE id = 1;
/ 不加锁查询:image.png
/ SELECT * FROM account WHERE id = 1 LOCK IN SHARE MODE;
/ 其他事务拥有排它锁,本事务获取共享锁失败:image.png
/ SELECT * FROM account WHERE id = 1 FOR UPDATE;
/ 其他事务拥有排它锁,本事务获取排它锁失败:image.png
/ UPDATE account SET balance = 3500 WHERE id = 1;
/ UPDATE语句获取排它锁,同样失败:image.png
COMMIT; /
/ COMMIT;

总结

最后,提醒大家签约慎重,警惕互联网厂商毁约,避免被公司演一手。

image.png

阅读 381

推荐阅读
Tomorrow
用户专栏

明天,你好

316 人关注
135 篇文章
专栏主页
目录