事务与mysql隔离级别(代码片段)

关键词：

事务

定义： 比如ABCD四个业务，作为一个事务，他们要么一起都执行完毕，要么都不执行。(只要有一个不成功，那么所有的都不可以成功)

四个特性

ACID

原子性(Atomicity)

整个事务中的所有操作，要么全都完成，要么全部不完成。

事务在执行过程中发生错误，会被回滚(Rollback)到事务开始前的状态。

一致性(Consistency)

事务必须保持系统处于一致的状态，不管在任何时间并发事务有多少。

比如转账，ABCDE五个人每个人初始状态 有 100元，总额是500元。 互相转账的操作下，

要保持住整体系统的总额依然是500元。

隔离性(Isolation)

隔离状态进行事务操作，使他们在给定的时间内 像只有自己在操作一样。

如果有两个事务，在同一时间内，执行相同的操作，事务的隔离性要求 保证每一个事务在系统中认为只有

该事务在使用系统。

这种属性有时候叫串行化, 为了防止事务操作间的混淆，必须串行化或者序列化请求，使得同一时间仅有一个请求请求于同一数据

持久性(Durability)

由于一项操作通常会包含许多子操作，而这些子操作可能会因为硬件的损坏或其他因素产生问题，要正确实现ACID并不容易。ACID建议数据库将所有需要更新以及修改的资料一次操作完毕，但实际上并不可行。

目前主要有两种方式实现ACID：第一种是Write ahead logging，也就是日志式的方式(现代数据库均基于这种方式)。第二种是Shadow paging。

相对于WAL（write ahead logging）技术，shadow paging技术实现起来比较简单，消除了写日志记录的开销, 恢复的速度也快(不需要redo和undo)。shadow paging的缺点就是事务提交时要输出多个块，这使得提交的开销很大，而且以块为单位，很难应用到允许多个事务并发执行的情况——这是它致命的缺点。

WAL 的中心思想是对数据文件 的修改（它们是表和索引的载体）必须是只能发生在这些修改已经 记录了日志之后 -- 也就是说，在日志记录冲刷到永久存储器之后． 如果我们遵循这个过程，那么我们就不需要在每次事务提交的时候 都把数据页冲刷到磁盘，因为我们知道在出现崩溃的情况下， 我们可以用日志来恢复数据库：任何尚未附加到数据页的记录 都将先从日志记录中重做（这叫向前滚动恢复，也叫做 REDO） 然后那些未提交的事务做的修改将被从数据页中删除 （这叫向后滚动恢复 - UNDO）。

什么是WAL

"In computer science, write-ahead logging (WAL) is a family of techniques for providing atomicity and durability (two of the ACID properties) in database systems."——维基百科

在计算机领域，WAL（Write-ahead logging，预写式日志）是数据库系统提供原子性和持久化的一系列技术。

在使用WAL的系统中，所有的修改都先被写入到日志中，然后再被应用到系统状态中。通常包含redo和undo两部分信息。

为什么需要使用WAL，然后包含redo和undo信息呢？举个例子，如果一个系统直接将变更应用到系统状态中，那么在机器掉电重启之后系统需要知道操作是成功了，还是只有部分成功或者是失败了（为了恢复状态）。如果使用了WAL，那么在重启之后系统可以通过比较日志和系统状态来决定是继续完成操作还是撤销操作。

redo log

redo log称为重做日志，每当有操作时，在数据变更之前将操作写入redo log，这样当发生掉电之类的情况时系统可以在重启后继续操作。

undo log

undo log称为撤销日志，当一些变更执行到一半无法完成时，可以根据撤销日志恢复到变更之间的状态。

MySQL中用redo log来在系统Crash重启之类的情况时修复数据（事务的持久性），而undo log来保证事务的原子性。

隔离问题

脏读

一个事务读到另一个事务未提交的数据

不可重复读

一个事务读到另一个事务已经提交的数据。

是指：一个事务范围内，做了俩次相同的查询，却返回了不同的结果。

起因： 这是由于一个事务在查询时，系统中另一个事务修改的提交而引起的。

例子： 比如事务T1 读取某一数据，这个时候事务T2读取并修改了这一数据，T1为了读取

? 值然后进行校验，而再次读取该数据，就会得到不同的结果。

幻读

幻读是指一个事务不是独立执行时发生的情况(像是受到了别的事务的干扰)

例子：

背景： 事务A读取与搜索条件匹配的若干行，事务B以插入或删除的方式来修改事务A搜索到的结果集，

? 然后提交。

情形： 事务A对一个表中的数据做了修改，然后与此同时，这个事务B也对这个结果集刚好做了insert操作，

? 添加了一行新数据, 那么事务A还没提交之前，发现多了一行数据没有修改，就好像产生了幻觉一样.

解决方法： 一般解决幻读的方法是增加范围锁RangeS，锁定检索范围为只读，这样就避免了幻读。

在数据库定义的四种隔离级别中

最高隔离级别SERIALIZABLE_READ可以保证不出现幻读的问题。

Repeatable Read (RR)

隔离界别

MySQL InnoDB事务的隔离级别有四级，默认是“可重复读”（REPEATABLE READ）。

• 未提交读（READ UNCOMMITTED）。另一个事务修改了数据，但尚未提交，而本事务中的SELECT会读到这些未被提交的数据（脏读）。
• 提交读（READ COMMITTED）。本事务读取到的是最新的数据（其他事务提交后的）。问题是，在同一个事务里，前后两次相同的SELECT会读到不同的结果（不重复读）。
• 可重复读（REPEATABLE READ）。在同一个事务里，SELECT的结果是事务开始时时间点的状态，因此，同样的SELECT操作读到的结果会是一致的。但是，会有幻读现象（稍后解释）。
• 串行化（SERIALIZABLE）。读操作会隐式获取共享锁，可以保证不同事务间的互斥。

测试Mysql的隔离级别的幻读

创建表和检查引擎

mysql> show create table t_bitfly\\G;
CREATE TABLE `t_bitfly` (
`id` bigint(20) NOT NULL default '0',
`value` varchar(32) default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk

mysql> select @@global.tx_isolation, @@tx_isolation;
+-----------------------+-----------------+
| @@global.tx_isolation | @@tx_isolation  |
+-----------------------+-----------------+
| REPEATABLE-READ       | REPEATABLE-READ |
+-----------------------+-----------------+

开启两个mysql窗口，模拟两个事务A，B

情景：模拟由于事务B在事务A执行中commit了一条新增记录的操作

t Session A Session B
|
| START TRANSACTION; |START TRANSACTION;
| |
| SELECT * FROM t_bitfly; |
| empty set |
| | INSERT INTO t_bitfly
| | VALUES (1, ‘a‘);
|
| SELECT * FROM t_bitfly; |
| empty set |
| | COMMIT;
| |
| SELECT * FROM t_bitfly;
| empty set
|
| INSERT INTO t_bitfly VALUES (1, ‘a‘);
| ERROR 1062 (23000):
| Duplicate entry ‘1‘ for key 1
(shit, 刚刚明明告诉我这没有这条记录的)

结果如下：

实验证明：RR级别确实解决了不可重复读的隔离问题，因为最后一次读取的时候还是没有读取到事务B

? 提交后的新纪录。

? 但是自己插入(或更新)时都不成功。

mysql对于RR级别能解决幻读的官方解释:

http://dev.mysql.com/doc/refman/5.0/en/innodb-record-level-locks.html

By default, InnoDB operates in REPEATABLE READ transaction isolation level and with the innodb_locks_unsafe_for_binlog system variable disabled. In this case, InnoDB uses next-key locks for searches and index scans, which prevents phantom rows (see Section 13.6.8.5, “Avoiding the Phantom Problem Using Next-Key Locking”).

意思是：可以通过 Next-Key Locking来解决幻读

关键点在于，是InnoDB默认对一个普通的查询也会加next-key locks，还是说需要应用自己来加锁呢？如果单看这一句，可能会以为InnoDB对普通的查询也加了锁，如果是，那和序列化（SERIALIZABLE）的区别又在哪里呢？

MySQL manual里还有一段：

13.2.8.5. Avoiding the Phantom Problem Using Next-Key Locking (http://dev.mysql.com/doc/refman/5.0/en/innodb-next-key-locking.html)

To prevent phantoms, InnoDB uses an algorithm called next-key locking that combines index-row locking with gap locking.

You can use next-key locking to implement a uniqueness check in your application: If you read your data in share mode and do not see a duplicate for a row you are going to insert, then you can safely insert your row and know that the next-key lock set on the successor of your row during the read prevents anyone meanwhile inserting a duplicate for your row. Thus, the next-key locking enables you to “lock” the nonexistence of something in your table.

我的理解是说，InnoDB提供了next-key locks，但需要应用程序自己去加锁。manual里提供一个例子：

SELECT * FROM child WHERE id > 100 FOR UPDATE;

这样，InnoDB会给id大于100的行（假如child表里有一行id为102），以及100-102，102+的gap都加上锁。

可以使用show innodb status来查看是否给表加上了锁。

事务A 事务B

start transaction; start transaction

select *from t_bitfly where id<=1 for update;

? insert into t_bitfly values(2,‘b‘);

? Query OK, 1 row affected (0.36 sec)

? //成功了! 这里证明如果事务A加锁的结果集范围不包含

? 事务B想要插入的key的范围的话（2不在id<2的范围），

? 则可以成 功执行

?

? insert into t_bitfly values(0,‘z‘);

? Lock wait timeout exceeded; try restarting transaction

? //意味着事务A不提交的时候，事务B因为范围锁永远无法

? 执行。

其他评论:

·1、第一个例子，事务b提交以后，事务a没有读取到（没有出现幻读），至于插入失败，是因为主键不唯一，这个就算是可见也一定不会成功的。
·2、第二个例子，查询并没有幻读，但是update之后出现了多余的数据，是因为update的时候，是会更新next-key的版本号的，如果update加入条件，只更新查询出来的id为1的数据，后续查询，还是查不到另外一条的（没有幻读，update更新了版本号，所以查询出来的数据是合法的）
? 后面的例子是你加锁的例子，没有问题。但是这样会大大的消耗了性能，其实你做的是SERIALIZABLE做的事情。
? 还有一点，你可能对next-key locks的理解有些偏差，所谓next-key locks并不是真的加锁，只是通过版本号，做了数据隔离，而版本号（当前版本，删除版本两个）是mysql的innodb自己维护的隐藏列。这种隔离是对查询的隔离，更新删除还有插入，都有自己的版本号维护，来保证查询的正确性。

mysql的savepoint实现可选择性的回滚

ABCD 一个事务

Connection conn = null;
try
  //1 获得连接
  conn = ...;
  //2 开启事务
  conn.setAutoCommit(false);
  A
  B
  C
  D
  //3 提交事务
  conn.commit();
 catche()
  //4 回滚事务
  conn.rollback();

AB(必选)，CD（可选）

场景举例：比如AB是转账操作，CD是银行发短信的操作

Connection conn = null;
Savepoint savepoint = null;  //保存点，记录操作的当前位置，之后可以回滚到指定的位置。（可以回滚一部分）
try
  //1 获得连接
  conn = ...;
  //2 开启事务
  conn.setAutoCommit(false);
  A
  B
  savepoint = conn.setSavepoint();
  C
  D
  //3 提交事务
  conn.commit();
 catche()
  if(savepoint != null)   //CD异常
     // 回滚到CD之前
     conn.rollback(savepoint);
     // 提交AB
     conn.commit();
   else   //AB异常
     // 回滚AB
     conn.rollback();
  

mysql事务管理(代码片段)

文章目录CURD什么是事务为什么会出现事务事务的版本支持事务提交方式事务常见操作方式事务隔离级别理解隔离性查看与设置隔离性隔离级别读未提交【ReadUncommitted】读提交【ReadCommitted】可重复读【RepeatableRead】串行化【Serializ... 查看详情

mysql事物隔离级别及搜索引擎(代码片段)

...事物隔离级别及搜索引擎一.MySQL事物隔离级别1.1查询全局事务隔离级别1.2查询会话事务隔离级别1.3设置全局事务隔离级别1.4设置会话事务隔离级别二.事务控制语句三.MySQL存储引擎3.1存储引擎概念介绍3.2MySQL常用的存储引擎:3.3lnnoD... 查看详情

mysql事务隔离级别(代码片段)

MySQL的事务必须满足A（原子性）C（一致性）I（隔离性）D（持久性）原则。其中，隔离性是为了尽量减少并发事务彼此之间的影响，最高的隔离级别可以保证并发事务之间互不影响。  在... 查看详情

mysql事务隔离级别(代码片段)

...别可以通过MySQL的视图来实现。读未提交读未提交是一个事务仅修改了数据但还未提交时，本次修改可以便可被其他事务查询到变更后的值。读未提交隔离级别下，其他事务进行查询时，直接返回记录上的最新值，... 查看详情

mysql原理篇之事务隔离级别和mvcc--13(代码片段)

Mysql原理篇之事务隔离级别和MVCC--13事前准备事务隔离级别事务并发执行遇到的问题SQL标准中的四种隔离级别MySQL中支持的四种隔离级别如何设置事务的隔离级别MVCC原理版本链ReadViewREADCOMMITTED——每次读取数据前都生成一个ReadView... 查看详情

面试官：说一下mysql事务隔离级别？(代码片段)

MySQL事务隔离级别是为了解决并发事务互相干扰的问题的，MySQL事务隔离级别总共有以下4种：READUNCOMMITTED：读未提交。READCOMMITTED：读已提交。REPEATABLEREAD：可重复读。SERIALIZABLE：序列化。1.四种事务隔离级... 查看详情

mysql事务(代码片段)

文章目录二、MySQL事务2.1、事务的概念2.2、事务的ACID特点①原子性（Atomicity）②一致性（Consistency）③隔离性（Isolation）ⅰ查询全局事务隔离级别：ⅱ查询会话事务隔离级别ⅲ设置全局事务隔离级别ⅳ... 查看详情

mysql事务(代码片段)

文章目录二、MySQL事务2.1、事务的概念2.2、事务的ACID特点①原子性（Atomicity）②一致性（Consistency）③隔离性（Isolation）ⅰ查询全局事务隔离级别：ⅱ查询会话事务隔离级别ⅲ设置全局事务隔离级别ⅳ... 查看详情

mysql事务readcommitted隔离级别的数据可见性(代码片段)

模拟MySQL事务ReadCommited已提交读隔离级别数据的可见性。预先创建一张表test，表中只有一个字段state。createtabletest(stateintnotnull)engine=InnoDBcharset=utf8mb4;在两个终端中分别开启两个事务：事务的隔离级别是ReadCommitted结论：在ReadCommitt... 查看详情

面试官问：mysql锁与事物隔离级别你知道吗？(代码片段)

...法、MySQL性能优化篇一些内容。我们再来聊聊MySQL的锁与事务隔离级别，分上下两篇，本篇重点讲MySQL的行锁与事务隔离级别。锁定义锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中，除了传统的计算资源（... 查看详情

数据库事务——事务隔离级别(代码片段)

文章目录数据库的事务隔离级别MySQL的事务隔离级别的实现1.READUNCOMMITTED&READCOMMITTED：2.REPEATABLEREAD：3.SERIALIZABLE：MySQL事务小细节事务可以嵌套吗？如何实现可重复读？如何解决幻读问题？MySQL事务的回滚... 查看详情

数据库事务——事务隔离级别(代码片段)

文章目录数据库的事务隔离级别MySQL的事务隔离级别的实现1.READUNCOMMITTED&READCOMMITTED：2.REPEATABLEREAD：3.SERIALIZABLE：MySQL事务小细节事务可以嵌套吗？如何实现可重复读？如何解决幻读问题？MySQL事务的回滚... 查看详情

mysql的事务和引擎，注意细品(代码片段)

mysql事务和引擎一、MySQL事务（一）、MySQL事务的概念（二）、事务的ACID特点1、ACID特点2、数据不一致产生的结果：(三)、事务的隔离1、MySQL事物隔离级别1.1查询全局事务隔离级别1.2查询会话事务隔离级别1.3设... 查看详情

mysql事务隔离级别(代码片段)

1.隔离级别1.1理论MySQL中事务的隔离级别一共分为四种，分别如下：序列化（SERIALIZABLE）可重复读（REPEATABLEREAD）提交读（READCOMMITTED）未提交读（READUNCOMMITTED）四种不同的隔离级别含义分别如... 查看详情

mysql事务隔离级别(代码片段)

1.隔离级别1.1理论MySQL中事务的隔离级别一共分为四种，分别如下：序列化（SERIALIZABLE）可重复读（REPEATABLEREAD）提交读（READCOMMITTED）未提交读（READUNCOMMITTED）四种不同的隔离级别含义分别如... 查看详情

隔离级别(代码片段)

隔离级别：一个事务必须与由其他事务进行的资源或数据更改相隔离的程度，事务指定一个隔离级别，该隔离级别定义一个事务必须与由其他事务进行的资源或数据更改相隔离的程度。隔离级别从允许的并发副作用（例如，脏读... 查看详情

mysql高级篇——事务的隔离级别与简单应用(代码片段)

...发所存在的问题1.1脏写1.2脏读1.3不可重复读1.4幻读2.SQL中事务的隔离级别3.案例实操3.1查看与修改MySQL的隔离级别3.2读未提交-举例3.3 读已提交-举例3.4 可重复读-举例3.5 幻读-举例1.数据并发所存在的问题针对事务的隔离性和并发... 查看详情

什么是脏读不可重复读幻读？一文带你搞定mysql事务隔离级别(代码片段)

...什么是脏读、不可重复读、幻读？一文带你搞定MySQL事务隔离级别事务ACID四个特性原子性(Atomicity)一致性(Consistency)隔离性(Isolation)持久性(Durability)事务隔离级别多个事务并发执行时引发的问题脏读不可重复读幻读MySQL四个事务... 查看详情