mysql事务

young 589 2022-06-21

数据库事务

事务定义

事务是数据库管理系统DBMS执行过程中的逻辑单位，由一个有限的数据库操作序列构成。

  • 所谓的逻辑单位，以为着它是数据库最小的工作单元，是不可以再分的

  • 它可能包含了一个或者一些列的DML语句，包括 insert，update，delete。

    单条DDL(create，drop)和DCL(grant，revoke也会有事务)

MySQL中支持事务的引擎

MySQL的引擎中，只有InnoDB支持事务

事务的四大特性

原子性 atomicity

意味着数据库的一系列操作，要么都是成功的，要么都是失败的，不可能出现部分成功或者部分失败的情况。

原子性在InnoDB中是通过undo log来实现的，它记录了数据修改之前的值（逻辑日志），一旦发生异常，可以用undo log来实现回滚。

隔离性 Isolation

在数据库里会有很多的事务同时去操作同一张表或者同一行数据，必然会产生一些并发或者干扰的操作。

对隔离性的定义，就是这写很多个的事务，对表或者行进行并发操作，应该是透明的，互相不干扰的。

持久性 Durability

我们对数据库的任意的操作，增删改，只要事务提交成功，那么结果就是永久性的，不可能因为数据库掉电、宕机、意外重启又变成原来的状态。

持久性是通过redo log和double write buffer 来实现的，操作数据的时候，会先写到内存的buffer pool中，同时记录redo log，如果刷盘前出现异常，重启的时候可以读取redo log的内容，写入到磁盘，保证数据的持久性。

恢复成功的前提是数据源本身没有被破坏，是完整的，通过双写缓冲保证。

一致性 consistent

指的是数据库的完整性约束没有被破坏，事务执行的前后都是合法的数据状态。

数据库什么时候出现事务

update student set name = 'young' where id = 1;

事实上，执行这条SQL的时候。他不仅开启了一个事务，而且还自动提交了，最终写入了磁盘。

增删改的语句会自动开启事务，一条SQL一个事务。每个事务都是有编号的，这个编号是个证书，有递增的特性。

如果要把多条SQL放在一个事务里，则需要进行手动开启事务。手动开启事务的方式有两种，一种是用begin;，一种是用start transaction。

结束事务的方式也有两种，一种是回滚事务rollback，一种是提交事务commit。

InnoDB中有一个autocommit的参数，分为两个级别，session级别和global级别。

show variables like 'autocommit';

默认是开启的（ON），表示开启自动提交事务，如果将它设置为false/off，那么数据库的事务就需要进行手动的rollback或者commit。

当客户端断开连接时，事务也会结束。

事务并发带来的问题

脏读

假设有两个事务，一个事务编号是1，一个事务编号是2，当事务1通过条件where id = 1 去查询行，返回age = 10，此时事务2通过update 语句，将id=1这条数据的age值改成了12，但是没有提交。

此时，如果事务1再去查询where id=1，它拿到的数据中age变成了12。这种情况下，在一个事务中，由于其他事务修改了数据并且没有提交，导致前后两次查询返回数据不一致，这种情况就叫做脏读。

不可重复读

假设有两个事务，一个事务编号是1，一个事务编号是2，当事务1通过条件where id = 1 去查询行，返回age = 10，此时事务2通过update 语句，将id=1这条数据的age值改成了12，然后commit提交事务。

此时第一个事务读取到了其他事务已经提交的数据，导致前后两次数据不一致的情况，叫做不可重复读。

幻读

假设有两个事务，一个事务编号是1，一个事务编号是2，事务1进行了一个范围查询，此时满足条件的数据只有1条。事务2此时进行了一个插入（insert）操作，并且commit提交事务。在事务1中再次进行查询，发现多了一行数据。

一个事务前后两次读取数据不一致，是由于其他事务插入导致的，这种情况叫做幻读

不可重复读和幻读的区别

修改或者删除导致的读不一致叫做不可重复的，插入导致的读不一致叫做幻读。

事务隔离级别定义

由美国国家标准协会（ANSI）指定的SQL标准，定义了4种事务隔离级别

  1. 读未提交（Read Uncommitted）

    一个事务可以读取到其他事务未提交的数据，会出现脏读，它没有解决任何问题

  2. 读已提交（Read Committed）

    事务只能读取其他事务已经提交的数据，不能读取到其他事务未提交的数据，解决了脏读问题，但是会出现不可重复读问题

  3. 可重复读（Repeatable Read）

    解决了不可重复读的问题，也就是在一个事务中，多次读取同样的数据，结果是一样的，在这个级别下，没有定义解决幻读的问题

  4. 串行化（Serializable）

    在这个隔离级别中，所有的事务都是串行执行的，也就是对数据的操作需要排队，不存在并发操作，所以解决了所有问题。

事务隔离级别是可以修改的

set global transaction isolation level read uncommitted;
set global transaction isolation level read committed;
set global transaction isolation level repeatable read;
set global transaction isolation level serializable;

InnoDB对隔离级别的支持

事务隔离级别 脏读 不可重复读 幻读
读未提交 可能 可能 可能
读已提交 不可能 可能 可能
可重复的 不可能 不可能 InnoDB不可能
串行化 不可能 不可能 不可能

InnoDB解决了幻读问题。

读一致性的解决方案

LBCC

基于锁的并发控制Lock Base Concurency Control。

如果仅仅是基于锁来实现事务隔离，一个事务读取的时候不允许其他事务修改，那就意味着不支持并发读写操作，这样会极大的影响操作数据库的效率。

MVCC

如果要让一个事务前后两次读取的数据保持一致，那么我们可以在修改数据的之前给它简历一个备份或者快照，后面再来读取这个快照就行了。这种方案叫做多版本的并发控制Mulit Version Concurrency Control

MVCC的原则

一个事务能看到的数据版本：

  1. 第一次查询之前已经提交的是事务的修改
  2. 本事务的修改

一个事务不能看见的数据版本

  1. 在本事务第一次查询之后创建的事务（事务ID比自身的事务ID大）
  2. 活跃的（未提交的）事务的修改

MVCC的效果

我可以查到在我这个事务开始前已近存在的数据，即时它在后面被修改或者删除了。而在我这个事务之后新增的数据，是看不到的。

所以把它叫做快照，不管别的事务做任何操作，他只能看到第一次查询时看到的数据版本。

mysql