事务隔离级别,脏读、不可重复读、幻读,乐观锁、悲观锁(共享锁、排它锁)
数据库事务具有四个特征,分别是原子性(Atomicity)、一致性(Consistency)、隔离性(Isoation)、持久性(Durability),简称为事务的ACID特性。
事务的隔离性是指在并发环境中,并发的事务是相互隔离的。SQL标准中定义了四种数据库事务隔离级别,级别从低到高分别为:读未提交(Read Uncommitted)、读已提交(Read Committed)、可重复读(Repeatable Read)、串行化(Serializable)。在事务的并发操作中会出现脏读、不可重复读、幻读。在事务的并发操作中第二类更新丢失可以通过乐观锁和悲观锁解决。
--------------------------------------------------------------------------------
读未提交(Read Uncommitted)
- 该隔离级别,所有事务都可以看到其他未提交事务的执行结果。通俗地讲就是,在一个事务中可以读取到另一个事务中新增或修改但未提交的数据。
- 该隔离级别可能导致的问题是脏读。因为另一个事务可能回滚,所以在第一个事务中读取到的数据很可能是无效的脏数据,造成脏读现象。
> set tx_isolation='READ-UNCOMMITTED';
事务A | 事务B | 备注 |
---|---|---|
> begin; > select name,blance from account where name='jack'; jack 1000 |
事务A开启,查询jack余额1000 |
|
> begin; > select name,blance from account where name='jack'; jack 1000 > update account set blance=1200 where name='jack'; > select name,blance from account where name='jack'; jack 1200 |
事务B开启,修改余额为1200, 但是没有提交事务 |
|
> select name,blance from account where name='jack'; jack 1200 |
事务A中查询jack余额为1200,读 取到了事务B未提交的修改,即脏 读,且和之前读到的结果不同, 即不可重复读 |
|
> rollback; > select name,blance from account where name='jack'; jack 1000 |
事务B回滚 | |
> select name,blance from account where name='jack'; jack 1000 > commit; |
事务A又读取到了jack之前的余额。 |
读已提交(Read Committed)
- 这是大多数数据库系统的默认隔离级别(但不是mysql默认的)
- 一个事务只能看见已经提交事务所做的修改。换句话说,一个事务从开始直到提交之前,所做的任何修改对其他事务都是不可见的。
- 该隔离级别可能导致的问题是不可重复读。因为两次执行同样的查询,可能会得到不一样的结果。
> set tx_isolation='READ-COMMITTED';
事务A | 事务B | 备注 |
---|---|---|
> begin; > select name,blance from account where name='jack'; jack 1000 |
事务A开启,查询jack余额1000 | |
> begin; > select name,blance from account where name='jack'; jack 1000 > update account set blance=1200 where name='jack'; > select name,blance from account where name='jack'; jack 1200 |
事务B开启,修改余额为1200, 但是没有提交事务 |
|
> select name,blance from account where name='jack'; jack 1000 |
事务A中查询jack余额仍为1000, 没有读取到事务B未提交的修改 |
|
> commit; > select name,blance from account where name='jack'; jack 1200 |
事务B提交 | |
> select name,blance from account where name='jack'; jack 1200 > commit; |
事务A读取到了事务B提交的修改, 和之前读到的结果不同,即不可重复读 |
可重复读(Repeatable Read)
- 这是MySQL的默认事务隔离级别
- 它确保同一事务的多个实例在并发读取数据时,会看到同样的数据行。通俗来讲,可重复读在一个事务里读取数据,怎么读都不会变,除非提交了该事务,再次进行读取。
- 该隔离级别存在的问题是幻读
set tx_isolation='REPEATABLE-READ';
事务A | 事务B | 备注 |
---|---|---|
> begin; > select name,blance from account where name='jack'; jack 1000 |
事务A开启,查询jack余额1000 | |
> begin; > select name,blance from account where name='jack'; jack 1000 > update account set blance=1200 where name='jack'; > select name,blance from account where name='jack'; jack 1200 |
事务B开启,修改余额为1200, 但是没有提交事务 |
|
> select name,blance from account where name='jack'; jack 1000 |
事务A中查询jack余额仍为1000, 没有读取到了事务B未提交的修改 |
|
> commit; > select name,blance from account where name='jack'; jack 1200 |
事务B提交 | |
> select name,blance from account where name='jack'; jack 1000 > commit; > select name,blance from account where name='jack'; jack 1200 |
事务A中查询jack余额仍为1000, 没有读取到事务B已提交的修改 |
下面看下如何出现幻读的
事务A | 事务B | 备注 |
---|---|---|
> begin; > select name,blance from account; jack 1000 |
事务A开启,查询到jack账户 | |
> begin; > select name,blance from account; jack 1000 > insert into account (name,blance) values ('tom',800); > select name,blance from account; jack 1000 tom 800 commit; |
事务B开启,新增tom账户 | |
> select name,blance from account; jack 1000 > insert into account (name,blance) values ('tom',800); ERROR 1062 (23000): Duplicate entry 'tom' for key 'uk_name' rollback; |
事务A再次查询时,只看到jack 账户,新增tom账户时报唯一键冲 突。明明没有查询到tom账 户,但是有新增冲突,这就是 幻读。如果事务B新增或删 除数据,事务A进行更新时, 不是预期的影响行数,也是幻读 |
串行化(Serializable)
- 这是最高的隔离级别
- 它通过强制事务排序,使之不可能相互冲突,从而解决幻读问题。通俗地讲就是,假如两个事务都操作到同一数据行,那么这个数据行就会被锁定,只允许先读取/操作到数据行的事务优先操作,只有当事务提交了,数据行才会解锁,后一个事务才能成功操作这个数据行,否则只能一直等待
- 该隔离级别可能导致大量的超时现象和锁竞争。
> set tx_isolation='SERIALIZABLE';
事务A | 事务B | 事务C | 备注 |
---|---|---|---|
> begin; > select * from account where ctime='2018-06-12 10:28:13'; 1 jack 1000 2018-06-12 10:28:13 2018-06-12 16:48:58 |
事务A开启,并且查询了jack账户,并拿到了该行的共享锁 | ||
> begin; > select * from account where ctime='2018-06-12 10:28:13'; 1 jack 1000 2018-06-12 10:28:13 2018-06-12 16:48:58 > update account set blance=1200 where utime='2018-06-12 16:48:58'; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction |
事务B开启,能够正常查询jack账户(可以拿到该行的共享锁),但是不能修改(不可以拿到该行的排它锁) | ||
> commit; | 事务A提交,释放了jack账户的共享锁 | ||
> update account set blance=1200 where utime='2018-06-12 16:48:58'; > select * from account where ctime='2018-06-12 10:28:13'; 1 jack 1200 2018-06-12 10:28:13 2018-06-12 16:48:58 |
事务B可以修改账户jack了,即拿到了该行的排它锁 | ||
> begin; > select * from account where ctime='2018-06-12 10:28:13'; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction |
事务C开启,不能查询jack账户,因为事务B拿了改行的排它锁,导致事务C拿不到该行的共享锁 | ||
> commit; | 事务B提交,释放了jack账户的排它锁 | ||
> select * from account where ctime='2018-06-12 10:28:13'; 1 jack 1200 2018-06-12 10:28:13 2018-06-12 17:06:02 commit; |
事务C可以查询jack账户了,即拿到了该行的共享锁 |
注:
- 这里使用ctime、utime字段进行查询和修改,是为了展示使用非索引字段也有事务隔离效果
- 在该隔离级别中,select语句会自动获取共享锁,update/insert/delete会自动获取排它锁
- 一个事务中的一行记录的共享锁被拿走,其他事务只能获取改行的共享锁,不能获取排它锁;一个事务中的一行记录的排它锁被拿走,其他事务不能获取该行的共享锁和排它锁
--------------------------------------------------------------------------------
从上面四种数据库事务隔离级别介绍可以对应出解决的问题,如图:
--------------------------------------------------------------------------------
第一类更新丢失
SQL标准中对此没有定义,不会出现该错误。
事务A | 事务B |
---|---|
开启事务 | |
查询jack余额为1000 | |
开启事务 | |
查询jack余额为1000 | |
更新jack余额为1200 | |
提交事务 | |
更新jack余额为800 | |
回滚事务 | |
查询jack余额为1000 (事务B的更新丢失) |
第二类更新丢失
事务A | 事务B |
---|---|
开启事务 | |
查询jack余额为1000 | |
开启事务 | |
查询jack余额为1000 | |
更新jack余额为1200 | |
提交事务 | |
更新jack余额为800 | |
事务提交 | |
查询jack余额为800 (事务B的更新丢失) |
第二类更新丢失解决办法
乐观锁
在更新语句中增加过滤条件,进行版本的判断,可以是这条记录的版本号、更新时间等。然后通过影响行数来判断是否更新成功。
> begin;
> select name,blance,utime from account where name='jack';
jack 1000 2018-06-12 18:20:06
> update account set blance=1200 where name='jack' and utime='2018-06-12 18:20:06';
> commit;
悲观锁
悲观锁分为共享锁和排它锁。
共享锁又称为读锁,简称S锁,顾名思义,共享锁就是多个事务对于同一数据可以共享一把锁,共享锁是用来读取数据的。另外,一个事务获取了同一数据的共享锁,其他事务就不能获取该数据的排它锁。
排它锁又称为写锁,简称X锁,顾名思义,排它锁就是不能与其他所并存,如一个事务获取了一个数据行的排它锁,其他事务就不能再获取该行的其它锁,包括共享锁和排它锁。另外不存什么事务隔离级别,update/insert/delete会自动获取排它锁
共享锁获取方式:select * from account where name='jack' lock in share mode;
排它锁获取方式:select * from account where name='jack' for update;
MySQL分为表级锁和行级锁,共享锁和排它锁是行级锁。表级锁在此不做讨论。
最佳实践
通常,对于绝大多数的应用程序来说,可以优先考虑将数据库系统的隔离级别设置为读已提交(Read Committed),这能够在避免脏读的同时保证较好的并发性能。尽管这种事务隔离级别会导致不可重复读、幻读和第二类丢失更新等并发问题,但较为科学的做法是在可能出现这类问题的个别场合中,由应用程序主动采用悲观锁或乐观锁来进行事务控制。
另本文中示例的表结构如下:
CREATE TABLE `account` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(16) NOT NULL DEFAULT '' COMMENT '用户名',
`blance` int(11) NOT NULL DEFAULT '0' COMMENT '余额',
`ctime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '写入时间',
`utime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
PRIMARY KEY (`id`),
UNIQUE KEY `uk_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户余额表';
上一篇: 分布式事务:Saga模式
下一篇: php 查询 不能输出结果