事务隔离级别

事务隔离级别,脏读、不可重复读、幻读,乐观锁、悲观锁(共享锁、排它锁)


数据库事务具有四个特征,分别是原子性(Atomicity)一致性(Consistency)隔离性(Isolation)持久性(Durability),简称为事务的ACID特性。

事务的隔离性是指在并发环境中,并发的事务是相互隔离的。SQL标准中定义了四种数据库事务隔离级别,级别从低到高分别为:读未提交(Read Uncommitted)、读已提交(Read Commited)、可重复读(Repeatable Read)、串行化(Serializable)。在事务的并发操作中会出现脏读、不可重复读、幻读。在事务的并发操作中第二类更新丢失可以通过乐观锁悲观锁解决。

读未提交(Read Uncommitted)

  • 该隔离级别,所有事务都可以看到其他未提交事务的执行结果。通俗地讲就是,在一个事务中可以读取到另一个事务中新增或修改但未提交的数据。
  • 该隔离级别可能导致的问题是脏读。因为另一个事务可能会滚,所以在第一个事务中读取到的数据很可能是无效的数据,造成脏读现象。
set tx_isolation = 'READ-UNCOMMITTED';
事务A 事务B 备注
> begin;
> select name,balance from account where name = ‘Tom’;
Tom 2000
事务A启,查询Tom余额2000
> begin;
> select name,balance from account where name = ‘Tom’;
Tom 2000
> update account set balance = 2100 where name = ‘Tom’;
select name,blance from account where name = ‘Tom’;
Tom 2100
事务B开启,修改余额为2100,但是没有提交事务
> rollback;
> select name,blance from account where name = ‘Tom’;
Tom 2100
事务B会滚
> select name,blance from account where name = ‘Tom’;
Tom 2100
事务A中查询Tom余额为2100,读取到了事务未提交的修改,即脏读,且和之前读到的结果不同,即不可重复读
> rollback;
> select name,blance from account where name = ‘Tom’;
Tom 2000
事务B回滚
> select name,blance from account where name = ‘Tom’;
Tom 2000
> commit;
事务A又读取到了Tom之前的余额。

读已提交(Read Committed)

  • 这是大多数数据库系统的默认隔离级别(但不是mysql默认的)
  • 一个事务只能看见已经提交事务所做的修改。换句话说,一个事务从开始直到提交之前,所做的任何修改对其他事务都是不可见的。
  • 该隔离级别可能导致的问题是不可重复读。因为两次执行同样的查询,可能会得到不一样的结果。
set tx_isolation = 'READ-COMMITTED';
事务A 事务B 备注
> begin;
> select name,blance from account where name = ‘Tom’;
Tom 2000
事务A开启,查询Tom余额2000
> begin;
> select name,blance from account where name=‘Tom’;
Tom 2000
update account set blance = 2100 where name = ‘Tom’;
> select name,blance from account where name = ‘Tom’;
Tom 2100
事务B开启,修改余额为2100,但是没有提交事务
> select name,blance from account where name = ‘Tom’;
Tom 2000
事务A中查询Tom余额仍为2000,没有读取到事务B未提交的修改
> commit;
> select name,blance from account where name = ‘Tom’;
Tom 2100
事务B提交
> select name,blance from account where name = ‘Tom’;
Tom 2100
> commit;
事务A读取到了事务B提交的修改,和之前读到的结果不同,即不可重复读

可重复读(Repeatable Read)

  • 这是MySQL的默认事务隔离级别
  • 它确保同一事务的多个实例在并发读取数据时,会看到同样的数据行。通俗来讲,可重复读在一个事务里读取数据,怎么读都不会变,除非提交了该事务,再次进行读取。
  • 该隔离级别存在的问题时幻读
set tx_isolation = 'REPEATABLE_READ';
事务A 事务B 备注
> begin;
> select name,blance from acount where name = ‘Tom’;
Tom 2000
事务A开启,查询Tom余额2000
> begin;
> select name,blance from account where name = ‘Tom’;
Tom 2000
> update account set blance = 2100 where name = ‘Tom’;
> select name,blance from account where name = ‘Tom’;
Tom 2100
事务B开启,修改余额为2100,但是没有提交事务
> select name,blance from account where name = ‘Tom’;
Tom 2000
事务A中查询Tom余额仍为2000,没有读取到了事务B未提交的修改
> commit;
> select name,blance from account where name = ‘Tom’;
Tom 2100
事务B提交
> select name,blance from account where nname = ‘Tom’;
Tom 2100
> commit;
> select name,blance from account where name = ‘Tom’;
Tom 2100
事务A中查询Tom余额仍为1000,没有读取到事务B已提交的修改

下面看下如何出现幻读

事务A 事务B 备注
> begin;
> select name,blance from accounnt;
Tom 2000
事务A开启,查询到Tom账户
> begin;
> select name,blance from acount; Tom 2000
> insert into account(name,blance) values (‘Ted’, 1000);
> select name,blance from account;
Tom 2000
Ted 1000
commit;
事务B开启,新增Ted账户
> select name,blance from account;
Tom 1000
> insert into account(name,blance) values (‘Ted’, 1000);
ERROR 1062(2300): Duplicate entry ‘Ted’ for key ‘uk_name’ rollback;
事务A再次查询时,只看到Tom账户,新增Ted账户时报唯一键冲突。明明没有查询到Ted账户,但是有新增冲突,这就是幻读。如果事务B新增或删除数据,事务A进行更新时,不是预期的影响行数,也是幻读

串行化(Serializable)

  • 这是最高的隔离级别
  • 它通过强制事务排序,使之不可能相互冲突,从而解决幻读问题。通俗地讲就是,假如两个事务都操作到同一数据行,那么这个数据行就会被锁定,只允许读取/操作到数据行的事务优先操作,只有当事务提交了,数据行才会解锁,后一个事务才能成功操作这个数据行,否则只能一直等待
  • 该隔离级别可能导致大量的超时现象和锁竞争
set tx_isolation = 'SERIALIZABLE';
事务A 事务B 事务C 备注
> begin;
> select * from account where ctime = ‘2019-06-12 10:28:13’;
1 Tom 2000 2019-06-12 10:28:13 2019-06-12 16:48:58
事务A开启,并且查询了Tom账户,并拿到了该行的共享锁
> begin;
> select * from account where ctime=‘2018-06-12 10:28:13’;
1 Tom  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开启,能够正常查询Tom账户(可以拿到该行的共享锁),但是不能修改(不可以拿到该行的排它锁)
> 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账户了,即拿到了该行的共享锁

注:
1.这是使用ctime、utime字段进行查询和修改,是为了展示使用非索引字段也有事务隔离效果
2.在该隔离级别中,select语句会自动获取获取共享锁,update/insert/delete会自动获取排它锁
3.一个事务中的一行记录的共享锁被拿走,其他事务只能获取改行的共享锁,不能获取排它锁;一个事务中的一行记录的怕它锁被拿走,其他事务不能获取该行的共享锁和排它锁

从上面四种数据库事务隔离级别介绍可以对应出解决的问题,如图:

隔离级别 脏读 不可重复读 幻读
读未提交
读已提交 ×
可重复读 × ×
串行化 × × ×

第一类更新丢失

SQL标准中对此没有定义,不会出现该错误。

事务A 事务B
开启事务
查询Tom余额为2000
开启事务
查询Tom余额为2000
更新Tom余额为2100
提交事务
更新Tom余额为1800
会滚事务
查询Tom余额为2000
(事务B的更新丢失)

第二类更新丢失

事务A 事务B
开启事务
查询Tom余额为2000
开启事务
查询Tom余额为2000
更新Tom余额为2100
提交事务
更新Tom余额为800
事务提交
查询Tom余额为800(事务B的更新丢失)

第二类更新丢失解决办法

乐观锁
在更新语句中增加过滤条件,进行版本的判读,可以是这条记录的版本号、更新时间等。然后通过影响行数来判断是否更新成功。

> begin;
> select name,blance,utime from account where name = 'Tom';
Tom 2000 2019-06-12 18:20:06
> update account set blance=2100 where name = 'Tom' and utime = '2019-06-12 18:20:06';
> commit;

悲观锁
悲观锁分为共享锁和排它锁。
共享锁又称为读锁,简称S锁,顾名思义,共享锁就是多个事务对于同一数据可以共享一把锁,共享锁是用来读取数据的。另外,一个事务获取了同一数据的共享锁,其他事务就不能获取该数据的排它锁。
排它锁又称为写锁,简称X锁,顾名思义,排它锁就是不能与其他锁并存,如一个事务获取了一个数据行的排它锁,其他事务就不能再获取改行的其他锁,包括共享锁和排它锁。另外不存再什么事务隔离级别,update/insert/delete会自动获取排它锁
共享锁获取方式:select * from account where name = ‘Tom’ lock in share mode;
排它锁获取方式:select * from account where name = ‘Tom’ for update;

MySQL分为表级锁和行级锁,共享锁和排它锁是行级锁。表级锁在此不做讨论。

最佳实践

通常,对于绝大多数的应用程序来锁,可以优先考虑将数据库系统的隔离级别设置为读已提交(Read Committed),这能够在避免脏读的同时保证较好的并发性能。尽管这种事务隔离级别会导致不可重复读、幻读和第二类丢失更新等并发问题,但较为科学的做法是在可能出现这类问题的个别场合中,由应用程序主动采用悲观锁或乐观锁来进行事务控制。

CREATE TABLE `accout` (
    `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 ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户余额表';
©️2020 CSDN 皮肤主题: 技术黑板 设计师: CSDN官方博客 返回首页
实付0元
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值