03-MySql 事物

事务

• 同一个表可能同时有很多人在用，为保持数据的一致性，所以提出了事务的概念。

一、事务和存储引擎

• 查看数据库全部存储引擎？

show engines;

Engine	Support	Comment	Transaction	XA	Savepoint
FEDERATED	NO	Federated	MySQL	storage	engine
MRG_MYISAM	YES	Collection of identical MyISAM tables	NO	NO	NO
MyISAM	YES	MyISAM storage engine	NO	NO	NO
BLACKHOLE	YES	/dev/null storage engine (anything you write to it disappears)	NO	NO	NO
CSV	YES	CSV storage engine	NO	NO	NO
MEMORY	YES	Hash	based, stored in memory, useful for temporary tables	NO	NO
ARCHIVE	YES	Archive storage engine	NO	NO	NO
InnoDB	DEFAULT	Supports	transactions, row-level locking, and foreign keys	YES	YES
PERFORMANCE_SCHEMA	YES	Performance Schema	NO	NO	NO

InnoDB是5.5以后mysql默认的存储引擎，也是支持事物的引擎，存储引擎比较多，主要是熟悉Innodb和MyISAM

• 查看mysql当前默认的存储引擎？

show variables like '%storage_engine%'

• 查看某张表的存储引擎？

//可以查询出创建表的原始语句，根据语句看出使用的存储引擎
show create table 表名;

二、事务的特点

特点	描述
原子性（atomicity）	一个事务是一个不可分割的工作单位，事务中包括的诸操作要么都做，要么都不做。
一致性（consistency）	事务必须是使数据库从一个一致性状态变到另一个一致性状态。一致性与原子性是密切相关的。
隔离性（isolation）	一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。
持久性（durability）	持久性也称永久性（permanence），指一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响。

三、事务隔离级别

3.1 隔离级别

隔离级别	脏读	不可重复读	幻读	备注
读未提交(read-uncommitted)	是	是	是	最低的隔离级别。一个事务可以读取另一个事务并未提交的更新结果。
读提交(read-committed)	否	是	是	可以看到另一个事物未提交的数据，只能避免脏读
可重复读(repeatable-read)	否	否	是	mysql默认
串行化(serializable)	否	否	否	读写都会锁表，最安全

• 隔离级别越高，数据一致性越强，但是性能损耗越大
• 查看mysql事务隔离级别：

查看InnoDB存储引擎 系统级的隔离级别 和 会话级的隔离级别：
select @@global.tx_isolation,@@tx_isolation; 
    show variables like '%tx_isolation%';

• 修改事物隔离级别：

SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}
设置innodb的事务级别方法是：set 作用域 transaction isolation level 事务隔离级别，例如:
set global transaction isolation level read committed; //全局的
set session transaction isolation level read committed; //当前会话

• 注意:修改隔离级别后如果重启mysql，又会回到默认的repeatable-read隔离级别，如果想重启后也生效，需要修改配置文件

重启也要生效要在mysql配置文件中[mysqld]下加上:
transaction-isolation=Read-Committed

• 上面修改了隔离级别之后，新创建的连接隔离级别都是修改后的隔离级别。

3.2 几个问题

• 脏读：脏读发生在一个事务A读取了被另一个事务B修改但B未提交的数据。假如B回退则事务A读取的是无效的数据。
  这跟不可重复读类似，但是第二个事务不需要执行提交。
• 不可重复读：不可重复读的重点是修改。同样的条件, 你读取过的数据, 再次读取出来发现值不一样了(锁行来避免)
• 幻读：幻读的重点在于新增或者删除 (数据条数变化)。同样的条件, 第1次和第2次读出来的记录数不一样(需要锁表来避免)

四、隔离级别效果

• 我们一个一个来看隔离级别所起到的效果。我们有一张薪水表如下：

id	name	salary
1	james	1000
2	david	1500
3	nining	1000
4	yaoming	1500

4.1 脏读

• 脏读发生在一个事务A读取了被另一个事务B修改但B未commit提交的数据。首先使用命令设置隔离级别为read-uncommitted，
  然后在2个session(A、B)中开启事物：

set global transaction isolation level read uncommitted; 

执行顺序	事物A	事物B
1	BEGIN;
2		BEGIN;
3	select * from t_salary where name=“james”;得到1000
4		UPDATE t_salary set salary=salary+1000 where name=“james”;
5	select * from t_salary where name=“james”;得到2000
6		ROLLBACK;事物B回滚
7		select * from t_salary where name=“james”;得到1000
8	select * from t_salary where name=“james”;得到1000

• 可以看到在事物A中读取到了事物B未提交的数据，事物A的过程中读到了脏数据2000，如果过程中使
  用这个数据来做业务处理，则可能导致业务逻辑异常。

4.2 不可重复读

• 不可重复读的重点是修改: 同样的条件, 你读取过的数据, 再次读取出来发现值不一样了。首先使用命令设置隔离级别为read-committed，
  然后在2个session(A、B)中开启事物：

set global transaction isolation level read committed; 

执行顺序	事物A	事物B
1	BEGIN;
2		BEGIN;
3	select * from t_salary where name=“james”;得到1000
4		UPDATE t_salary set salary=salary+1000 where name=“james”;
5	select * from t_salary where name=“james”;得到1000
6		COMMIT;事物B提交
7	select * from t_salary where name=“james”;得到2000

• 我们看到，整个过程其实事物A没有提交，但是事物B修改了数据，在B未提交之前，事物A读取到的都是1000，B提交后，A读到的就是2000了。(注意这里和
  脏读的区别，这里只能是B提交之后A才能看到2000，而脏读修改后不提交A也能看到) 因此这里发现事物A中对同一个数据前后读取2次发现数据不一致，这称为
  不可重复读(因为重复读取的数据很可能是不一致的)。

• 在"read committed"隔离级别下是不能预防不可重复读的，"read committed"是很多数据库的默认隔离级别，但是不是mysql的默认。mysql默认的是
  repeatable-read，顾名思义，可重复读，也就是不会出现上述情况，我们修改隔离级别之后在测试一次。

set global transaction isolation level repeatable-read; 

执行顺序	事物A	事物B
1	BEGIN;
2		BEGIN;
3	select * from t_salary where name=“james”;得到1000
4		UPDATE t_salary set salary=salary+1000 where name=“james”;
5	select * from t_salary where name=“james”;得到1000
6		COMMIT;事物B提交
7	select * from t_salary where name=“james”;得到1000

• 我们看到在repeatable-read隔离级别下，事物A中读取的数据是一致的，哪怕事物B已经提交(实际上这个时候真正的数据已经是2000了，新开一个session查看就是2000，因为事物B已经提交，事物提交后就持久化了)，但是在事物A中看到的仿佛还是自己开启事物的时候的值。这是mysql的默认隔离级别

4.3 幻读

• 幻读的重点在于新增或者删除 (数据条数变化)。幻读需要锁表来避免，因此需要最高的隔离级别来保证；
  我们先在repeatable-read隔离级别下看看幻读的问题现象

执行顺序	事物A	事物B
1	BEGIN;
2		BEGIN;
3	select * from t_salary where id=10; 得到空
4		INSERT INTO dbtest.t_salary ( id, name, salary) VALUES ( 10, ‘duncan’, ‘0’);插入成功
5		COMMIT;事物B提交
6	INSERT INTO dbtest.t_salary ( id, name, salary) VALUES ( 10, ‘duncan’, ‘100’);提示主键冲突

• 我们看到在事物A中，首先进行了一次查询，发现主键为10的数据是没有的，此时事物B插入了一条主键为10的数据并提交事物，
  事物A依据自己的判断认为主键为10的数据没有于是进行插入发现主键冲突。

• 修改隔离级别为serializable，发现就不会这样。在serializable隔离级别下，第4步会一直阻塞，在事物A中insert可以成功，再commit事物A后，事物B就会提示主键冲突

4.4 不可重复读和幻读

• 幻读错误的理解：说幻读是事务A执行两次select操作得到不同的数据集，即 select 1 得到 10 条记录，select 2 得到 11 条记录。这其实并不是幻读，这是不可重复读的一种，只会在 R-U， R-C 级别下出现，而在 mysql 默认的 RR 隔离级别是不会出现的。
  (RR保证是可以重复读的，我自己验证发现在mysql默认隔离级别下这样的查总数的问题是不会出现的，也说明了这不是幻读，而是不可重复读)

• 幻读。并不是说两次读取获取的结果集不同，幻读侧重的方面是某一次的select 操作得到的结果所表征的数据状态无法支撑后续的业务操作。
  更为具体一些：select 某记录是否存在，不存在，准备插入此记录，但执行 insert 时发现此记录已存在，无法插入，此时就发生了幻读。这也是
  前面所提到的幻读侧重在记录的增删。不可重复读侧重表达读-读，幻读则是读-写。

4.4.1 serializable避免幻读

将隔离级别设置为serializable自然是可以避免幻读，但是那样对效率的影响是非常大的，我们可以使用mysql中自带的锁来避免幻读。

4.4.2 repeatable-read下避免幻读

• repeatable-read隔离级别下，可以通过一些手段避免幻读，在效率和数据一致性方面做一定的折中。RR级别下只要对SELECT操作也手动加行（X）锁即可类似
  SERIALIZABLE级别（它会对 SELECT 隐式加锁），命令如下:

# 这里需要用X锁， 用LOCK IN SHARE MODE 拿到 S锁 后我们没办法做写操作
select  *  from t_salary where id=10 FOR UPDATE;

如果 id = 10 的记录存在则会被加行（X）锁，如果不存在，则会加 next-lock key / gap 锁（范围行锁），即记录存在与否，mysql 都会对记录应该对应的索
引加锁，其他事务是无法再获得做操作的。这里我们就展示下 id = 10 的记录不存在的场景，FOR UPDATE 也会对此 “记录” 加锁，InnoDB 的行锁
（gap锁是范围行锁，一样的）锁定的是记录所对应的索引，且聚簇索引同记录是直接关系在一起的。

执行顺序	事物A	事物B
1	BEGIN;
2		BEGIN;
3	select * from t_salary where id=10 FOR UPDATE; 得到空
4		INSERT INTO dbtest.t_salary ( id, name, salary) VALUES ( 10, ‘duncan’, ‘0’);插入过程阻塞
5	INSERT INTO dbtest.t_salary ( id, name, salary) VALUES ( 16, ‘duncan’, ‘10000’);插入成功
6	COMMIT;事物A提交
7		事物B提示主键冲突

• 我们可以看到使用FOR UPDATE可以在RR级别下避免幻读，SERIALIZABLE级别更像悲观锁，它认为幻读时刻都会发生，故会自动的隐式的对事务所需资源加排它锁，
  其他事务访问此资源会被阻塞等待，事务是安全的，但需要认真考虑性能。
• InnoDB的行锁锁定的是索引，而不是记录本身，因此索引相同的记录都会被加锁，会造成索引竞争，尽可能的使用主键或唯一索引对记录加锁。
• 索引映射的记录如果存在，加行锁，如果不存在，则会加 next-key lock / gap 锁 / 间隙锁，故InnoDB可以实现事务对某记录的预先占用，如果记录存在，
  它就是本事务的，如果记录不存在，那它也将是本事物的，只要本事物还在，其他事务就别想占有它。

五、参考

• 数据库事务隔离级别 - 分析脏读 & 不可重复读 & 幻读
• mysql 幻读的详解、实例及解决办法