详解MySQL中的事务(四种隔离级别、间隙锁等),看完还不懂你来打我

前置文章:
MySQL存储引擎
详解MySQL中的锁

为什么需要事务

现在的很多软件都是多用户,多程序,多线程的,对同一个表可能同时有很多人在用,为保持数据的一致性,所以提出了事务的概念。

A 给B 要划钱,A 的账户-1000元, B 的账户就要+1000元,这两个 update 语句必须作为一个整体来执行,不然A 扣钱了,B 没有加钱这种情况很难处理。

什么存储引擎支持事务

  1. 查看数据库下面是否支持事务
    show engines;
    在这里插入图片描述
  2. 查看mysql当前默认的存储引擎
    show variables like '%storage_engine%';
    在这里插入图片描述
  3. 查看某张表的存储引擎
    show create table 表名 ;
    在这里插入图片描述
  4. 对于表的存储结构的修改
    Alter table 表名 ENGINE = InnoDB;

行锁还是表锁

用到索引为行锁,没有为表锁。

事务特性

事务具有四个特征:原子性( Atomicity )、一致性( Consistency )、隔离性( Isolation )和持久性( Durability )。这四个特性简称为 ACID 特性。

原子性

事务是数据库的逻辑工作单位,事务中包含的各操作要么都做,要么都不做

一致性

事务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态。如果数据库系统运行中发生故障,有些事务尚未完成就被迫中断,这些未完成事务对数据库所做的修改有一部分已写入物理数据库,这时数据库就处于一种不正确的状态,或者说是 不一致的状态。

隔离性 ☆

一个事务的执行不能其它事务干扰。即一个事务内部的操作及使用的数据对其它并发事务是隔离的,并发执行的各个事务之间不能互相干扰。

四种隔离级别

SQL标准定义了4类隔离级别,包括了一些具体规则,用来限定事务内外的哪些改变是可见的,哪些是不可见的。低级别的隔离级一般支持更高的并发处理,并拥有更低的系统开销。

查看隔离级别:

  1. show variables like '%tx_isolation%';
  2. select @@tx_isolation;
    在这里插入图片描述
    示例:
DROP TABLE IF EXISTS `account`;
CREATE TABLE `account` (
  `id` int(11) NOT NULL,
  `name` varchar(50) DEFAULT NULL,
  `balance` int(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_balance` (`balance`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `account` VALUES ('1', 'lilei', '900');
INSERT INTO `account` VALUES ('2', 'hanmei', '100');
INSERT INTO `account` VALUES ('3', 'lucy', '250');
INSERT INTO `account` VALUES ('5', 'tom', '0');

在这里插入图片描述

Read Uncommitted(读取未提交内容)

在该隔离级别,所有事务都可以看到其他未提交事务的执行结果。本隔离级别很少用于实际应用,因为它的性能也不比其他级别好多少。

会导致脏读(Dirty Read):某个事务已更新一份数据但未提交,另一个事务在此时读取了同一份数据。
例如,事务A读取到了事务B修改后但未提交的数据,这就是脏读了。如果事务B回滚,事务A读取到的就是错误的数据。

sessionA:
set SESSION TRANSACTION ISOLATION LEVEL read UNCOMMITTED;
start TRANSACTION;

sessionB:
set SESSION TRANSACTION ISOLATION LEVEL read UNCOMMITTED;
start TRANSACTION;

sessionA:
update account set balance = balance -50 where id = 1;

sessionB:
select * from account;
在这里插入图片描述
读取到了未提交的数据,脏读

sessionA:
ROLLBACK;

sessionB:
select * from account;
在这里插入图片描述
之前sessionA读取到了错误的数据。

Read Committed(读取提交内容)

这是大多数数据库系统的默认隔离级别(但不是MySQL默认的)。它满足了隔离的简单定义:一个事务只能看见已经提交事务所做的改变。

解决了脏读,但会导致不可重复读(Non-repeatable read):在一个事务的两次查询之中数据不一致。
例如,事务A第一次查询的是原有数据,第二次查询的是事务B提交后的数据,两次查询不一致,但事务A不会读取未提交的数据。

示例:
sessionA:
set SESSION TRANSACTION ISOLATION LEVEL read committed;
start TRANSACTION;

sessionB:
set SESSION TRANSACTION ISOLATION LEVEL read committed;
start TRANSACTION;

sessionA:
update account set balance = balance -50 where id = 1;

sessionB:
select * from account;
在这里插入图片描述
没有读取到未提交的数据,解决了脏读

sessionA:
commit;

sessionB:
select * from account;
在这里插入图片描述
前后两次读取的数据不一样,出现了不可重复读

Repeatable Read(可重复读)

这是MySQL的默认事务隔离级别,它确保同一事务的多个实例在并发读取数据时,会看到同样的数据行。

解决了脏读、不可重复读,但会导致幻读 (Phantom Read):在一个事务的两次查询中数据笔数不一致。
例如,有一个事务查询了几行(Row)数据,而另一个事务却在此时插入了新的几行数据,先前的事务在接下来的查询中,就有几行数据是未查询出来的,如果此时插入和另外一个事务插入的一样的数据,就会报错。

不可重复读的和幻读很容易混淆,不可重复读侧重于修改,幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行,解决幻读需要锁表。

InnoDB存储引擎通过多版本并发控制(MVCC,Multiversion Concurrency Control)和间隙锁解决了该问题。

示例:
sessionA:
set SESSION TRANSACTION ISOLATION LEVEL repeatable read;
start TRANSACTION;

sessionB:
set SESSION TRANSACTION ISOLATION LEVEL repeatable read;
start TRANSACTION;

sessionA:
update account set balance = balance -50 where id = 1;

sessionB:
select * from account;
在这里插入图片描述
没有读取到未提交的数据,解决了脏读

sessionA:
commit;

sessionB:
select * from account;
在这里插入图片描述
前后两次读取的数据一样,解决了不可重复读

sessionB:
commit;
select * from account;
在这里插入图片描述
当前事务结束之后就可查询到新数据了

sessionA:
start TRANSACTION;

sessionB:
start TRANSACTION;

sessionA:
insert into account value(4,'aa',123);
commit;

sessionB:
select * from account;
在这里插入图片描述
insert into account value(4,'aa',123);
在这里插入图片描述
查询的时候并没有 id=4 的数据,却不能新增 key=4 的数据,就好像有 key=4 的数据的幻影在那里。这就是幻读

解决方法

删除新增的 id=4 的数据
在这里插入图片描述
sessionA:
start TRANSACTION;

sessionB:
start TRANSACTION;

sessionA:
insert into account value(4,'aa',123);
commit;

sessionB:
select * from account for update;
在这里插入图片描述
通过一个 for update 就看到了原本看不到的 id = 4 的数据,解决了幻读

Serializable(可串行化)

这是最高的隔离级别,它通过强制事务排序,使之不可能相互冲突简言之,它是在每个读的数据行上加上共享锁。在这个级别,可能导致大量的超时现象和锁竞争。

解决了脏读、不可重复读、幻读。

示例:
sessionA:
set SESSION TRANSACTION ISOLATION LEVEL serializable;
start TRANSACTION;

sessionB:
set SESSION TRANSACTION ISOLATION LEVEL serializable;
start TRANSACTION;

sessionA:
select * from account;
在这里插入图片描述

sessionB:
select * from account;

在这里插入图片描述
两个事务都可以对表进行查询

insert into account VALUES(6,'cc',500);
在这里插入图片描述
A进行过了操作之后,B进行写操作会进入了等待状态。
这里十分合理,假如此时sessionB可以写,岂不是sessionA读的数据就不是最新的了。在未超时之前:

sessionA:
commit;

sessionB:
select * from account;
在这里插入图片描述

间隙锁(gap锁)

其实在mysql中,可重复读已经解决了幻读问题,借助的就是间隙锁,隔离级别RR(Repeatable Read)。
间隙锁,是在索引的间隙之间加上锁。

示例1(判断条件为主键大小):

create table t_lock_1 (a int primary key);
insert into t_lock_1 values(10),(11),(13),(20),(40);

在这里插入图片描述
sessionA:

 begin;
 select * from t_lock_1 where a <= 13 for update;

在这里插入图片描述
sessionB:
begin;
insert into t_lock_1 values(21);
在这里插入图片描述
insert into t_lock_1 values(19) ;
在这里插入图片描述
表中有数据10 11 13 20 40,sessionA查询了小于等于13的数据,sessionB无法插入小于等于20 的数据,就好像在20到21之间的间隙里上了一道锁,这就是间隙锁。
为什么是在20这里上了间隙锁,而不是13这里呢?这是MySQL的实现方式,扫描到条件边界值的下一个表中数据再加锁。这个示例中,扫描到第一个大于 13 的数 20 就加锁了。
再问,为什么要这么设计呢?思考,假如判断的是 where a <= 15,那你怎么加锁呢 。所以上面这个 13 作为条件边界具有迷惑性,因为它恰好是表中数据。所以,给条件边界的下一个表中数据加锁是合理的。

示例2(判断条件为一般键大小):

create table t_lock_2 (a int primary key,b int, key (b));
insert into t_lock_2 values(1,1),(3,1),(5,3),(8,6),(10,8);

在这里插入图片描述
sessionA:

BEGIN;
select * from t_lock_2 where b=3 for update;

在这里插入图片描述
sessionB:
begin;
select * from t_lock_2 where a = 5 lock in share mode;
等待,因为数据 (5,3)上有一把排它锁

insert into t_lock_2 values(4, 2);
等待,因为 b=2 在(1, 6)内,a=4 在(3,8)内
insert into t_lock_2 values(6, 5);
等待,因为 b=5 在(1, 6)内,a=6 在(3,8)内

insert into t_lock_2 values(4, 0);
成功,因为 b=0 在(1,6)外,即使 a=4 在(3,8)内也没关系
insert into t_lock_2 values(6, 7);
成功,因为 b=7 在(1,6)外,即使 a=6 在(3,8)内也没关系

insert into t_lock_2 values(9, 6);
成功,因为 b=6 在(1,6)的边界上,但 a=9 在(3,8)外
insert into t_lock_2 values(7, 6);
等待,因为 b=6 在(1, 6)的边界上,但 a=6 在(3,8)内

update t_lock_2 set b = 5 where a = 8;
等待,因为 b=5 在(1, 6)内,a = 8 在 (3,8) 的边界上
update t_lock_2 set b = 9 where a = 8;
成功,因为 b=9 在(1, 6)外,a = 8 在 (3,8) 的边界上

update t_lock_2 set b = 1 where a = 8;
等待,因为 b = 1 在(1, 6)边界上,a = 8 在 (3,8) 的边界上

总结:
假设有数据(主键A,一般键B)锁住的范围在(A1,B1)与(A2,B2)之间,where 针对的是 B。
那么,另一事务成功的条件是:

  1. B 在范围 (B1,B2) 之外
  2. B 在边界 B1 或 B2 上,但是 A 在 (A1,A2) 之外

另一事务失败(进入等待状态)的条件是:

  1. B 在范围 (B1,B2) 之内
  2. B 在边界 B1 或 B2 上,但是 A 在 (A1,A2) 之内
  3. B 在边界 B1 或 B2 上,A在边界 A1 或 A2 上(如,将(A2,B2)修改为(A1,B2)。只有这里是改表中数据)

至于,只有(A1,B1)与(A2,B2)之间的数据不能读。

为什么能解决幻读?
因为间隙锁的粒度比行锁大(但比表锁小)。让原来一个事务能插入(引起幻读)的时候不让它插入,不就解决了幻读吗

持久性

指一个事务一旦提交,它对数据库中的数据的改变就应该是永久性的。接下来的其它操作或故障不应该对其执行结果有任何影响。

事务语法

开启事务

  1. START TRANSACTION(推荐)
  2. begin
  3. begin work

事务回滚

  1. rollback
  2. rollback work

事务提交

  1. commit
  2. commit work

自动提交

查看自动提交事务是否开启
show variables like '%autocommit%';
在这里插入图片描述
默认开启(这也意味着,当你开启一个事务并修改了数据而没有提交或回滚后,再开启一个事务,上一个事务会自动提交,而不是回滚)

关闭自动提交事务
set autocommit=0;
再查看:
在这里插入图片描述

保存点(了解)

作用:
使事务可以回滚到保存点

语法:
savepoint 保存点名字;
rollback to savepoint 保存点名字;

示例:

CREATE TABLE `testdemo` (
  `id` int(255) NOT NULL,
  `c1` varchar(300) CHARACTER SET utf8 DEFAULT NULL,
  `c2` int(50) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_c2` (`c2`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

start TRANSACTION;
insert into testdemo values(1,1,1);
savepoint s1;
insert into testdemo values(2,2,2);
savepoint s2;
insert into testdemo values(3,3,3);
savepoint s3;

select * from testdemo;
在这里插入图片描述
rollback to savepoint s2;
select * from testdemo;
在这里插入图片描述
commit;

©️2020 CSDN 皮肤主题: 岁月 设计师: pinMode 返回首页
实付0元
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

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

余额充值