mysql transaction

mysql事务

事务就是要保证一组数据库操作，要么全部成功，要么全部失败。

在 MySQL 中，事务支持是在引擎层实现的。你现在知道，MySQL 是一个支持多引擎的系统，但并不是所有的引擎都支持事务。比如 MySQL 原生的 MyISAM 引擎就不支持事务，这也是 MyISAM 被 InnoDB 取代的重要原因之一。

MySQL事务ACID

  • 原子性(Atomicity) 一个事务应该是一个不可分割的工作单位，事务中包括的操作要么都成功，要么都不成功。 事务的回滚机制，保证原子性，要么全部提交成功，要么回滚
  • 一致性(Consistency) 事务必须是使数据库从一个一致性状态变到另一个一致性状态。一致性与原子性是密切相关的。 undo log +MVCC 保证一致性 (为了并发可以适当破坏一致性)
  • 隔离性(Isolation) 一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据在事物未提交前对并发的其他事务是隔离的，并发执行的各个事务之间不能互相影响。 隔离机制，不同的隔离级别保证了不同的隔离性
  • 持久性(Durability) 一个事务一旦成功提交，它对数据库中数据的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响。 binlog + redolog 两阶段提交保证了持久性

事务之间的几个特性并不是一组同等的概念： 如果在任何时刻都只有一个事务，那么其天然是具有隔离性的，这时只要保证原子性就能具有一致性。 如果存在并发的情况下，就需要保证原子性和隔离性才能保证一致性。

锁

数据库锁 = 乐观锁 + 悲观锁(共享锁、排它锁(行锁、表锁))

排它锁(Exclusive)，又称为X锁，写锁。 共享锁(Shared)，又称为S锁，读锁。

读写锁之间有以下的关系：

  • 一个事务对数据对象O加了 S 锁，可以对 O进行读取操作，但是不能进行更新操作。加锁期间其它事务能对O 加 S 锁，但是不能加 X 锁。
  • 一个事务对数据对象 O 加了 X 锁，就可以对 O 进行读取和更新。加锁期间其它事务不能对 O 加任何锁。

即读写锁之间的关系可以概括为：多读单写

多事务执行: 脏读、不可重复读、幻读

当数据库上有多个事务同时执行的时候，就可能出现脏读（dirty read）、不可重复读（non-repeatable read）、幻读（phantom read）的问题。

  • 脏读 对于两个事务A与B，A读取了已经被B更新但是还没有提交的字段之后，若此时B回滚，A读取的内容就是临时并且无效的。
  • 不可重复读 如果事务A按照一定条件进行搜索，期间事务B删除了符合条件的某一条数据，导致事务A再次读取时数据少了一条。这种情况归为不可重复读。 对于两个事务T1和T2，T1读取了一个字段，然后T2更新了该字段并提交之后，T1再次提取同一个字段，值便不相等了。
  • 幻读 事务A按照一定条件进行数据读取，期间事务B插入了相同搜索条件的新数据，事务A再次按照原先条件进行读取时，发现了事务B新插入的数据称为幻读。

隔离性与隔离级别

为了解决这些问题，就有了“隔离级别”的概念。 SQL标准的事务隔离级别包括：读未提交(read uncommitted)、读已提交(read committed)、 可重复读(repeatable read)和串行化(serialized)

  • 读未提交：一个事务还没提交时，它做的变更就能被别的事务看到 解决更新丢失问题。如果一个事务已经开始写操作，那么其他事务则不允许同时进行写操作，但允许其他事务读此行数据。该隔离级别可以通过“排他写锁”实现，即事物需要对某些数据进行修改必须对这些数据加 X 锁，读数据不需要加 S 锁。
  • 读提交： 一个事务提交之后，它做的变更才会被其他事务看到。 解决了脏读问题。读取数据的事务允许其他事务继续访问该行数据，但是未提交的写事务将会禁止其他事务访问该行。这可以通过“瞬间共享读锁”和“排他写锁”实现， 即事物需要对某些数据进行修改必须对这些数据加 X 锁，读数据时需要加上 S 锁，当数据读取完成后立刻释放 S 锁，不用等到事物结束。
  • 可重复读：一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。当然在可重复读隔离级别下，未提交变更对其他事务也是不可见的。 禁止不可重复读取和脏读取，但是有时可能出现幻读数据。读取数据的事务将会禁止写事务(但允许读事务)，写事务则禁止任何其他事务。Mysql默认使用该隔离级别。这可以通过“共享读锁”和“排他写锁”实现，即事物需要对某些数据进行修改必须对这些数据加 X 锁，读数据时需要加上 S 锁，当数据读取完成并不立刻释放 S 锁，而是等到事物结束后再释放。
  • 串行化：对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行 解决了幻读的问题的。提供严格的事务隔离。它要求事务序列化执行，事务只能一个接着一个地执行，不能并发执行。仅仅通过“行级锁”是无法实现事务序列化的，必须通过其他机制保证新插入的数据不会被刚执行查询操作的事务访问到。

MySQL 隔离级别的实现

上面的内容解释了一些数据库理论的概念，但是在MySQL、ORACLE这样的数据库中，为了性能的考虑并不是完全按照上面介绍的理论来实现的。

MVCC

多版本并发控制(Multi-Version Concurrency Control,MVCC)是MySQL中基于乐观锁理论实现隔离级别的方式，用于实现读已提交和可重复读。

具体实现

引入两个概念

  • 系统版本号：一个递增的数字，每开始一个新的事务，系统版本号就会递增。
  • 事务版本号：事务开始时的系统版本号。 在MySQL中，会在表中每一条数据后面添加两个字段：
  • 创建版本号：创建一行数据时，将当前系统版本号作为创建版本号赋值
  • 删除版本号：删除一行数据时，将当前系统版本号作为删除版本号赋值
  1. select select时读取数据的规则为：创建版本号<=当前事务版本号，删除版本号为空或>当前事务版本号。 创建版本号<=当前事务版本号保证取出的数据不会有后启动的事物中创建的数据。这也是为什么 在开始的示例中我们不会查出后来添加的数据的原因。 删除版本号为空或>当前事务版本号保证了至少在该事物开启之前数据没有被删除，是应该被查出来的数据。
  2. insert insert时将当前的系统版本号赋值给创建版本号字段。
  3. update 插入一条新纪录，保存当前事务版本号为行创建版本号，同时保存当前事务版本号到原来删除的行，实际上这里的更新是通过delete和insert实现的。
  4. delete 删除时将当前的系统版本号赋值给删除版本号字段，标识该行数据在那一个事物中会被删除，即使实际上在位commit时该数据没有被删除。根据select的规则后开启懂数据也不会查询到该数据。
MVCC真的解决了幻读吗？

从最开始我们的测试示例和上面的理论支持来看貌似在MySQL中通过MVCC就解决了幻读的问题，那既然这样串行化读貌似就没啥意义了，带着疑问继续测试。

测试前数据：

img

image.png

本来我们希望得到的结果只是第一条数据的部门改为财务，但是结果确实两条数据都被修改了。这种结果告诉我们其实在MySQL可重复读的隔离级别中并不是完全解决了幻读的问题，而是解决了读数据情况下的幻读问题。而对于修改的操作依旧存在幻读问题，就是说MVCC对于幻读的解决时不彻底的。

快照读和当前读

出现了上面的情况我们需要知道为什么会出现这种情况。在查阅了一些资料后发现在RR级别中，通过MVCC机制，虽然让数据变得可重复读，但我们读到的数据可能是历史数据，不是数据库最新的数据。这种读取历史数据的方式，我们叫它快照读 (snapshot read)，而读取数据库最新版本数据的方式，叫当前读 (current read)。

  1. select快照读 当执行select操作是innodb默认会执行快照读，会记录下这次select后的结果，之后select 的时候就会返回这次快照的数据，即使其他事务提交了不会影响当前select的数据，这就实现了可重复读了。快照的生成当在第一次执行select的时候，也就是说假设当A开启了事务，然后没有执行任何操作，这时候B insert了一条数据然后commit,这时候A执行 select，那么返回的数据中就会有B添加的那条数据。之后无论再有其他事务commit都没有关系，因为快照已经生成了，后面的select都是根据快照来的。
  2. 当前读 对于会对数据修改的操作(update、insert、delete)都是采用当前读的模式。在执行这几个操作时会读取最新的记录，即使是别的事务提交的数据也可以查询到。假设要update一条记录，但是在另一个事务中已经delete掉这条数据并且commit了，如果update就会产生冲突，所以在update的时候需要知道最新的数据。也正是因为这样所以才导致上面我们测试的那种情况。
疑问说明

所以要明白实际上对数据进行了增删改查等操作才开启了一个事务

如何解决幻读

很明显可重读隔离级别并没有办法彻底解决幻读的问题， 解决幻读的两个方法：

  1. 使用串行化读的隔离级别
  2. MVCC+next-key locks：next-key locks由record locks(索引加锁) 和 gap locks(间隙锁，每次锁住的不光是需要使用的数据，还会锁住这些数据附近的数据)

实际上很多的项目中是不会使用到上面的两种方法的，串行化读的性能太差，而且其实幻读很多时候是我们完全可以接受的。

事务的启动方式

MySQL 的事务启动方式有以下几种：

  1. 显式启动事务语句， begin 或 start transaction。配套的提交语句是 commit，回滚语句是 rollback。
  2. set autocommit=0，这个命令会将这个线程的自动提交关掉。意味着如果你只执行一个 select 语句，这个事务就启动了，而且并不会自动提交。这个事务持续存在直到你主动执行 commit 或 rollback 语句，或者断开连接。

有些客户端连接框架会默认连接成功后先执行一个 set autocommit=0 的命令。这就导致接下来的查询都在事务中，如果是长连接，就导致了意外的长事务。因此，建议总是使用 set autocommit=1, 通过显式语句的方式来启动事务。

参考博文： https://blog.csdn.net/weixin_36380516/article/details/115291399 https://www.cnblogs.com/liyus/p/10556563.html