当前位置: 澳门新濠3559 > 数据库 > 正文

其避免了脏读和不可重复读问题,即原子性、一

时间:2019-10-07 08:58来源:数据库
最近买了《高性能MySQL》这本书回来看,从中收益颇多!我来一吐为快! 回顾 在MySQL的众多存储引擎中,只有InnoDB支持事务,所有这里说的事务隔离级别指的是InnoDB下的事务隔离级别。

最近买了《高性能MySQL》这本书回来看,从中收益颇多!我来一吐为快!

回顾

在MySQL的众多存储引擎中,只有InnoDB支持事务,所有这里说的事务隔离级别指的是InnoDB下的事务隔离级别。

读未提交:一个事务可以读取到另一个事务未提交的修改。这会带来脏读、幻读、不可重复读问题。(基本没用)

读已提交:一个事务只能读取另一个事务已经提交的修改。其避免了脏读,但仍然存在不可重复读和幻读问题。

可重复读:同一个事务中多次读取相同的数据返回的结果是一样的。其避免了脏读和不可重复读问题,但幻读依然存在。

澳门新濠3559,串行化:事务串行执行。避免了以上所有问题。

以上是SQL-92标准中定义的四种隔离级别。在MySQL中,默认的隔离级别是REPEATABLE-READ(可重复读),并且解决了幻读问题。简单的来说,mysql的默认隔离级别解决了脏读、幻读、不可重复读问题。

不可重复读重点在于update和delete,而幻读的重点在于insert。

在这里,我们只讨论可重复读。

回顾

在MySQL的众多存储引擎中,只有InnoDB支持事务,所有这里说的事务隔离级别指的是InnoDB下的事务隔离级别。

读未提交:一个事务可以读取到另一个事务未提交的修改。这会带来脏读、幻读、不可重复读问题。(基本没用)

读已提交:一个事务只能读取另一个事务已经提交的修改。其避免了脏读,但仍然存在不可重复读和幻读问题。

可重复读:同一个事务中多次读取相同的数据返回的结果是一样的。其避免了脏读和不可重复读问题,但幻读依然存在。

串行化:事务串行执行。避免了以上所有问题。

以上是SQL-92标准中定义的四种隔离级别。在MySQL中,默认的隔离级别是REPEATABLE-READ(可重复读),并且解决了幻读问题。简单的来说,mysql的默认隔离级别解决了脏读、幻读、不可重复读问题。

不可重复读重点在于update和delete,而幻读的重点在于insert。

在这里,我们只讨论可重复读。

事务的特征ACID,即原子性、一致性、隔离性、持久性。

我们都知道事务,那么在什么情况下我们需要使用事务呢?

知识储备

知识储备

原子性保证一个事务为一个最小的单元,内部不可分割;

银行应用是解释事务的一个经典例子。假设一个银行的数据库有两张表:支票(checking)和储蓄(savings)表。现在johnson要从支票账户中转移200块大洋到储蓄表中,那么至少需要三个步骤:

MVCC

澳门新濠3559 1

译注:

  MVCC的全称是“多版本并发控制”。这项技术使得InnoDB的事务隔离级别下执行一致性读操作有了保证,换言之,就是为了查询一些正在被另一个事务更新的行,并且可以看到它们被更新之前的值。这是一个可以用来增强并发性的强大的技术,因为这样的一来的话查询就不用等待另一个事务释放锁。这项技术在数据库领域并不是普遍使用的。一些其它的数据库产品,以及mysql其它的存储引擎并不支持它。

 

MVCC

澳门新濠3559 2

译注:

  MVCC的全称是“多版本并发控制”。这项技术使得InnoDB的事务隔离级别下执行一致性读操作有了保证,换言之,就是为了查询一些正在被另一个事务更新的行,并且可以看到它们被更新之前的值。这是一个可以用来增强并发性的强大的技术,因为这样的一来的话查询就不用等待另一个事务释放锁。这项技术在数据库领域并不是普遍使用的。一些其它的数据库产品,以及mysql其它的存储引擎并不支持它。

 

一致性保证事务中的每个操作线程不可单独提交,成功则一起提交,不成功则事务回滚;

  1. 检查支票账户余额是否高于200块大洋
  2. 支票账户减少200块大洋
  3. 储蓄账户中增加200块大洋

说明

网上看到大量的文章讲到MVCC都是说给没一行增加两个隐藏的字段分别表示行的创建时间以及过期时间,它们存储的并不是时间,而是事务版本号。

事实上,这种说法并不准确,严格的来讲,InnoDB会给数据库中的每一行增加三个字段,它们分别是DB_TRX_ID、DB_ROLL_PTR、DB_ROW_ID。

但是,为了理解的方便,我们可以这样去理解,索引接下来的讲解中也还是用这两个字段的方式去理解。

 

说明

网上看到大量的文章讲到MVCC都是说给没一行增加两个隐藏的字段分别表示行的创建时间以及过期时间,它们存储的并不是时间,而是事务版本号。

事实上,这种说法并不准确,严格的来讲,InnoDB会给数据库中的每一行增加三个字段,它们分别是DB_TRX_ID、DB_ROLL_PTR、DB_ROW_ID。

但是,为了理解的方便,我们可以这样去理解,索引接下来的讲解中也还是用这两个字段的方式去理解。

 

隔离性保证不同事务间看到的数据视图相互独立,相互隔离(隔离级别可设置);

试想一下,如果上面步骤执行到第二步,突然因为什么原因而终止了,顾客支票账户中莫名其妙的减少了200块大洋。如果顾客恰好是一位情绪激动的大妈,那你就等着大妈带着平底锅和四级头去银行找你吧!

增删查改

在InnoDB中,给每行增加两个隐藏字段来实现MVCC,一个用来记录数据行的创建时间,另一个用来记录行的过期时间(删除时间)。在实际操作中,存储的并不是时间,而是事务的版本号,每开启一个新事务,事务的版本号就会递增。

于是乎,默认的隔离级别(REPEATABLE READ)下,增删查改变成了这样:

  • SELECT
    • 读取创建版本小于或等于当前事务版本号,并且删除版本为空或大于当前事务版本号的记录。这样可以保证在读取之前记录是存在的。
  • INSERT
    • 将当前事务的版本号保存至行的创建版本号
  • UPDATE
    • 新插入一行,并以当前事务的版本号作为新行的创建版本号,同时将原记录行的删除版本号设置为当前事务版本号
  • DELETE
    • 将当前事务的版本号保存至行的删除版本号

 

增删查改

在InnoDB中,给每行增加两个隐藏字段来实现MVCC,一个用来记录数据行的创建时间,另一个用来记录行的过期时间(删除时间)。在实际操作中,存储的并不是时间,而是事务的版本号,每开启一个新事务,事务的版本号就会递增。

于是乎,默认的隔离级别(REPEATABLE READ)下,增删查改变成了这样:

  • SELECT
    • 读取创建版本小于或等于当前事务版本号,并且删除版本为空或大于当前事务版本号的记录。这样可以保证在读取之前记录是存在的。
  • INSERT
    • 将当前事务的版本号保存至行的创建版本号
  • UPDATE
    • 新插入一行,并以当前事务的版本号作为新行的创建版本号,同时将原记录行的删除版本号设置为当前事务版本号
  • DELETE
    • 将当前事务的版本号保存至行的删除版本号

 

持久性保证事务提交后数据会持久的保存下来;

所以为了避免这种情况,就必须用到事务,上述三个步骤中有任何一个执行失败,就必须回滚所有的步骤,以免有大妈找上门。事务SQL如下所示:

快照读和当前读

快照读:读取的是快照版本,也就是历史版本

当前读:读取的是最新版本

普通的SELECT就是快照读,而UPDATE、DELETE、INSERT、SELECT ...  LOCK IN SHARE MODE、SELECT ... FOR UPDATE是当前读。

 

快照读和当前读

快照读:读取的是快照版本,也就是历史版本

当前读:读取的是最新版本

普通的SELECT就是快照读,而UPDATE、DELETE、INSERT、SELECT ...  LOCK IN SHARE MODE、SELECT ... FOR UPDATE是当前读。

 

 

  1. START TRANSACTION;
  2. SELECT balance FROM checking WHERE customer_id=123456;
  3. UPDATE checking SET balance = balance - 200 WHERE customer_id=123456;
  4. UPDATE savings SET balance = balance 200 WHERE customer_id=123456;
  5. COMMIT;

一致性非锁定读和锁定读

一致性非锁定读和锁定读

sql规范定义的事务的隔离级别:

事务之所以可靠,当然离不开ACID特性:

锁定读

  在一个事务中,标准的SELECT语句是不会加锁,但是有两种情况例外。SELECT ... LOCK IN SHARE MODE 和 SELECT ... FOR UPDATE。

  SELECT ... LOCK IN SHARE MODE

  给记录假设共享锁,这样一来的话,其它事务只能读不能修改,直到当前事务提交

  SELECT ... FOR UPDATE

  给索引记录加锁,这种情况下跟UPDATE的加锁情况是一样的

锁定读

  在一个事务中,标准的SELECT语句是不会加锁,但是有两种情况例外。SELECT ... LOCK IN SHARE MODE 和 SELECT ... FOR UPDATE。

  SELECT ... LOCK IN SHARE MODE

  给记录假设共享锁,这样一来的话,其它事务只能读不能修改,直到当前事务提交

  SELECT ... FOR UPDATE

  给索引记录加锁,这种情况下跟UPDATE的加锁情况是一样的

1.READ UNCOMMITTED(读取未提交内容)

  • 原子性(atomicity):整个事务中的操作要么全部成功,要么全部失败。
  • 一致性(consistency):数据库总是从一个一致性状态转换到另一个一致性状态。比如上面所说的,事务开始前和执行后,顾客johnson在银行的总账户余额是一样的。
  • 隔离性(isolation):通常来说,一个事务所做的修改在提交之前,其他事务是不可见的。也就是说事务间是相互隔离的。
  • 持久性(durability):事务在提交之后,对数据库数据所做的修改是永久性的。

一致性非锁定读

  consistent read (一致性读),InnoDB用多版本来提供查询数据库在某个时间点的快照。如果隔离级别是REPEATABLE READ,那么在同一个事务中的所有一致性读都读的是事务中第一个这样的读读到的快照;如果是READ COMMITTED,那么一个事务中的每一个一致性读都会读到它自己刷新的快照版本。Consistent read(一致性读)是READ COMMITTED和REPEATABLE READ隔离级别下普通SELECT语句默认的模式。一致性读不会给它所访问的表加任何形式的锁,因此其它事务可以同时并发的修改它们。

 

一致性非锁定读

  consistent read (一致性读),InnoDB用多版本来提供查询数据库在某个时间点的快照。如果隔离级别是REPEATABLE READ,那么在同一个事务中的所有一致性读都读的是事务中第一个这样的读读到的快照;如果是READ COMMITTED,那么一个事务中的每一个一致性读都会读到它自己刷新的快照版本。Consistent read(一致性读)是READ COMMITTED和REPEATABLE READ隔离级别下普通SELECT语句默认的模式。一致性读不会给它所访问的表加任何形式的锁,因此其它事务可以同时并发的修改它们。

 

    所有事务可以看到未提交事务的执行结果,本隔离级别很少用到实际应用中,读取未提交的数据,又称为“脏读”。

细心的人可能会注意到。在讨论隔离性的时候,我用了“通常来说”,下面就让我们讨论下事务的隔离级别。

悲观锁和乐观锁

悲观锁,正如它的名字那样,数据库总是认为别人会去修改它所要操作的数据,因此在数据库处理过程中将数据加锁。其实现依靠数据库底层。

乐观锁,如它的名字那样,总是认为别人不会去修改,只有在提交更新的时候去检查数据的状态。通常是给数据增加一个字段来标识数据的版本。

 

悲观锁和乐观锁

悲观锁,正如它的名字那样,数据库总是认为别人会去修改它所要操作的数据,因此在数据库处理过程中将数据加锁。其实现依靠数据库底层。

乐观锁,如它的名字那样,总是认为别人不会去修改,只有在提交更新的时候去检查数据的状态。通常是给数据增加一个字段来标识数据的版本。

 

2.READ COMMITTED(读取提交内容)

隔离级别 脏读可能性 不可重复读可能性 幻读可能性 加锁读
READ UNCOMMITTED YES YES YES NO
READ COMMITTED NO YES YES NO
REPEATABLE READ NO NO YES NO
SERIALIZABLE NO NO NO YES

有这样三种锁我们需要了解

  • Record Locks(记录锁):在索引记录上加锁。
  • Gap Locks(间隙锁):在索引记录之间加锁,或者在第一个索引记录之前加锁,或者在最后一个索引记录之后加锁。
  • Next-Key Locks:在索引记录上加锁,并且在索引记录之前的间隙加锁。它相当于是Record Locks与Gap Locks的一个结合。

假设一个索引包含以下几个值:10,11,13,20。那么这个索引的next-key锁将会覆盖以下区间:

(negative infinity, 10]
(10, 11]
(11, 13]
(13, 20]
(20, positive infinity)

 

了解了以上概念之后,接下来具体就简单分析下REPEATABLE READ隔离级别是如何实现的

有这样三种锁我们需要了解

  • Record Locks(记录锁):在索引记录上加锁。
  • Gap Locks(间隙锁):在索引记录之间加锁,或者在第一个索引记录之前加锁,或者在最后一个索引记录之后加锁。
  • Next-Key Locks:在索引记录上加锁,并且在索引记录之前的间隙加锁。它相当于是Record Locks与Gap Locks的一个结合。

假设一个索引包含以下几个值:10,11,13,20。那么这个索引的next-key锁将会覆盖以下区间:

(negative infinity, 10]
(10, 11]
(11, 13]
(13, 20]
(20, positive infinity)

 

了解了以上概念之后,接下来具体就简单分析下REPEATABLE READ隔离级别是如何实现的

    大多数数据库的默认隔离级别是此级别,但不是mysql默认的。一个事务在开始的时候只能看见已提交事务所做的改变。一个事务从开始到提交前所做的任何改变都是不可见的,除非提交。这种隔离级别也称为不可重复读。

 

理论分析

之所以说是理论分析,是因为要是实际操作证明的话我也不知道怎么去证明,毕竟作者水平实在有限。

但是,这并不意味着我在此胡说八道,有官方文档为证。

澳门新濠3559 3

这段话的大致意思是,在默认的隔离级别中,普通的SELECT用的是一致性读不加锁。而对于锁定读、UPDATE和DELETE,则需要加锁,至于加什么锁视情况而定。如果你对一个唯一索引使用了唯一的检索条件,那么只需锁定索引记录即可;如果你没有使用唯一索引作为检索条件,或者用到了索引范围扫描,那么将会使用间隙锁或者next-key锁以此来阻塞其它会话向这个范围内的间隙插入数据。

作者曾经有一个误区,认为按照前面说MVCC下的增删查改的行为就不会出现任何问题,也不会出现不可重复读和幻读。但其实是大错特错。

举个很简单的例子,假设事务A更新表中id=1的记录,而事务B也更新这条记录,并且B先提交,如果按照前面MVVC说的,事务A读取id=1的快照版本,那么它看不到B所提交的修改,此时如果直接更新的话就会覆盖B之前的修改,这就不对了,可能B和A修改的不是一个字段,但是这样一来,B的修改就丢失了,这是不允许的。

所以,在修改的时候一定不是快照读,而是当前读。

而且,前面也讲过只有普通的SELECT才是快照读,其它诸如UPDATE、删除都是当前读。修改的时候加锁这是必然的,同时为了防止幻读的出现还需要加间隙锁。

  • 一致性读保证了可用重复读
  • 间隙锁防止了幻读

回想一下

1、利用MVCC实现一致性非锁定读,这就有保证在同一个事务中多次读取相同的数据返回的结果是一样的,解决了不可重复读的问题

2、利用Gap Locks和Next-Key可以阻止其它事务在锁定区间内插入数据,因此解决了幻读问题

综上所述,默认隔离级别的实现依赖于MVCC和锁,再具体一点是一致性读和锁。

 

理论分析

之所以说是理论分析,是因为要是实际操作证明的话我也不知道怎么去证明,毕竟作者水平实在有限。

但是,这并不意味着我在此胡说八道,有官方文档为证。

澳门新濠3559 4

这段话的大致意思是,在默认的隔离级别中,普通的SELECT用的是一致性读不加锁。而对于锁定读、UPDATE和DELETE,则需要加锁,至于加什么锁视情况而定。如果你对一个唯一索引使用了唯一的检索条件,那么只需锁定索引记录即可;如果你没有使用唯一索引作为检索条件,或者用到了索引范围扫描,那么将会使用间隙锁或者next-key锁以此来阻塞其它会话向这个范围内的间隙插入数据。

作者曾经有一个误区,认为按照前面说MVCC下的增删查改的行为就不会出现任何问题,也不会出现不可重复读和幻读。但其实是大错特错。

举个很简单的例子,假设事务A更新表中id=1的记录,而事务B也更新这条记录,并且B先提交,如果按照前面MVVC说的,事务A读取id=1的快照版本,那么它看不到B所提交的修改,此时如果直接更新的话就会覆盖B之前的修改,这就不对了,可能B和A修改的不是一个字段,但是这样一来,B的修改就丢失了,这是不允许的。

所以,在修改的时候一定不是快照读,而是当前读。

而且,前面也讲过只有普通的SELECT才是快照读,其它诸如UPDATE、删除都是当前读。修改的时候加锁这是必然的,同时为了防止幻读的出现还需要加间隙锁。

  • 一致性读保证了可用重复读
  • 间隙锁防止了幻读

回想一下

1、利用MVCC实现一致性非锁定读,这就有保证在同一个事务中多次读取相同的数据返回的结果是一样的,解决了不可重复读的问题

2、利用Gap Locks和Next-Key可以阻止其它事务在锁定区间内插入数据,因此解决了幻读问题

综上所述,默认隔离级别的实现依赖于MVCC和锁,再具体一点是一致性读和锁。

 

3.REPEATABLE READ(可重复读)

  • 未提交读(READ UNCOMMITTED):事务中的修改,即使没有提交,其他事务也可以读到,这就有可能造成了脏读。
  • 提交读(READ COMMITTED):大多数数据库系统默认实用的隔离级别就是这种,但mysql不是。READ COMMITTED就是在事务提交前,所做的修改对其他事务是不可见的。但READ COMMITTED可能会造成不可重复读。就是在一个事务中,同样的查询语句,可能会得到不一样的结果。其实就是在两次查询中间,另一个事务修改了查询结果的值。
  • 可重复读(REPETABLE READ):REPETABLE READ解决了脏读和不可重复读的问题,但理论上,REPETABLE READ无法解决幻读的问题。幻读就是指,一个事务在读取某一范围的值时,另一个事务恰好在该范围内插入了新纪录,那么当你再次读取该范围的值时,就会产生幻行。这与不可重复读有点像,只不过不可重复读时UPDATE,而幻读时INSERT
  • 可串行化(SERIALIZABLE):SERIALIZABLE读取每一行数据都要加锁,强制事务串行执行,所以可能导致大量的超时和锁争用问题。

演示

澳门新濠3559 5

澳门新濠3559 6

澳门新濠3559 7

澳门新濠3559 8

澳门新濠3559 9

澳门新濠3559 10

上面四幅截图对比,可以看到由于id是主键,用id作为检索条件时只锁定那一个索引记录。接下来,看索引范围的例子

澳门新濠3559 11

澳门新濠3559 12

这两幅截图,可以看出,由于没有使用唯一索引作为检索条件,导致不光锁定了索引记录,还锁定了索引之间的间隙,应该是是使用了next-key锁。

 

参考 

演示

澳门新濠3559 13

澳门新濠3559 14

澳门新濠3559 15

澳门新濠3559 16

澳门新濠3559 17

澳门新濠3559 18

上面四幅截图对比,可以看到由于id是主键,用id作为检索条件时只锁定那一个索引记录。接下来,看索引范围的例子

澳门新濠3559 19

澳门新濠3559 20

这两幅截图,可以看出,由于没有使用唯一索引作为检索条件,导致不光锁定了索引记录,还锁定了索引之间的间隙,应该是是使用了next-key锁。

 

参考 

    此隔离级别是为了解决可重复读隔离级别导致的问题即一个事务多个实例并发读取数据时会看到不同的结果。此隔离级别不会看到其他事务提交后的结果,即事务即使提交了我也看不到。此级别也称为“幻读”。

到这里,如果还不是太懂,你需要细细消化下前面的内容,这时可以打开mysql,将隔离级别设置为READ COMMITTED。然后试试它是不是解决了脏读,会不会出现不可重复读?再将隔离级别设置为REPETABLE READ。看看REPETABLE READ是不是解决了不可重复读,会不会出现幻读?

4.SERIALIZABLE(可串行化)

SET session transaction isolation level read committed;

    可串行化是最高的隔离级别,它通过强制事务排序,使之不可重读,解决了幻读的问题。此隔离级别会在每个读的数据行上加共享锁,使用这种隔离级别会产生大量的超时现象,一般实际开发中不会用到。

如果你真的实验了,会发现mysql的REPETABLE READ隔离级别并不会出现幻读的现象。那你有没有想过mysql的事务是怎么实现的呢?

 

你肯定听说mysql的表锁和行锁,那你可能以为事务是基于行锁实现的。其实并没有那么简单,为了提高并发性能,mysql的大多是事务引擎都同时实现了多版本并发控制(MVCC)。它在很多情况下避免了加锁操作,所以开销更低。MVCC大都实现了非阻塞的读操作,写操作也只锁定必要的行。

mysql加锁机制 :

那么InnoDB中的MVCC是如何工作的呢?其实是通过在每行数据后面增加两个列,一个是创建版本号,一个是删除版本号。里面存储的是系统版本号,你开启一个事务系统版本号就会递增。事务开始时刻的系统版本号就作为事务版本号,用来和查询的每行记录的版本号做比较。下面看下REPETABLE READ隔离界别下,MVCC具体是如何操作的。

根据类型可分为共享锁(SHARED LOCK)和排他锁(EXCLUSIVE LOCK)或者叫读锁(READ LOCK)和写锁(WRITE LOCK)。

  • SELECT查询出的数据需要满足2个条件    1、创建版本号 <= 系统版本号  2、删除版本号为空或删除版本号>系统版本号
  • INSERT     为新插入的每一行保存当前事务版本号为行的创建版本号
  • UPDATE  为插入的一行新记录保存当前事务版本号为行的创建版本号,同时保存当前事务版本号为原来的行的删除版本号
  • DELETE  为删除的每一行保存当前事务版本号为行的删除版本号

根据粒度划分又分表锁和行锁。表锁由数据库服务器实现,行锁由存储引擎实现。

保存这两个额外的系统版本号,可以使大多数读操作都不用加锁,这样性能就会更好。但需要额外的存储空间和一些额外的检查工作,这也相当于用空间换时间。

 

在某些情况下我们还是需要用的锁。InnoDB采用两段锁协议。在事务执行过程中随时都可以加锁,事务提交或回滚时同时释放所有锁。这些锁一般都是隐式锁定,InnoDB会根据需要自动加锁。当然,你也可以通过SQL语句自己加锁:

mysql提供了3种事务型存储引擎,InnDB、NDB Cluster和Falcon。

SELECT ..... LOCK IN SHARE MODE;     乐观锁
SELECT ..... FOR UPDATE;             悲观锁

一个事务执行的任何过程中都可以获得锁,但是只有事务提交或回滚的时候才释放这些锁。这些都是隐式锁定,也可以显式锁定,InnoDB支持显式锁定,例如:

个人建议,除非你明确知道自己在干什么,否则轻易不要显式加锁,只会事倍功半!!!

SELECT .... LOCK IN SHARE MODE (加共享锁)

SELECT .....FOR UPDATE(加排他锁)

 

多版本并发控制(重要):

Mysql的事务存储引擎不是简单实用行加锁机制,而是叫多版本并发控制(MVCC)技术,和行加锁机制关联实用。以便应对更高的并发,当然是以消耗性能作为代价。

每种存储引擎对MVCC的实现方式不同,InnoDB引擎的简单实现方式如下:

InnoDB通过为每个数据航增加两个隐含值的方式来实现。这两个隐含值记录了行的创建时间,以及过期时间。每一行存储事件发生时的系统版本号。每一次开始一个新事务时版本号会自动加1,每个事务都会保存开始时的版本号,每个查询根据事务的版本号来查询结果。

编辑:数据库 本文来源:其避免了脏读和不可重复读问题,即原子性、一

关键词: 澳门新濠3559