mysql事务学习笔记

MYSQL事务

原子性： 事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用；

一致性： 执行事务前后，数据保持一致，多个事务对同一个数据读取的结果是相同的；

隔离性： 并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；

持久性：一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。

**脏读（Dirty read）: **当一个事务正在访问数据并且对数据进行了修改，而这种修改还没有提交到数据库中，这时另外一个事务也访问了这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是“脏数据”，依据“脏数据”所做的操作可能是不正确的。

**丢****失修改（Lost to modify）: **指在一个事务读取一个数据时，另外一个事务也访问了该数据，那么在第一个事务中修改了这个数据后，第二个事务也修改了这个数据。这样第一个事务内的修改结果就被丢失，因此称为丢失修改。例如：事务1读取某表中的数据A=20，事务2也读取A=20，事务1修改A=A-1，事务2也修改A=A-1，最终结果A=19，事务1的修改被丢失。

**不可重复读（Unrepeatableread）: **指在一个事务内多次读同一数据。在这个事务还没有结束时，另一个事务也访问该数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样。这就发生了在一个事务内两次读到的数据是不一样的情况，因此称为不可重复读。

**幻读（Phantom read）: **幻读与不可重复读类似。它发生在一个事务（T1）读取了几行数据，接着另一个并发事务（T2）插入了一些数据时。在随后的查询中，第一个事务（T1）就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。

SQL 标准定义了四个隔离级别：

· READ-UNCOMMITTED(读取未提交)：最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。

· READ-COMMITTED(读取已提交)：允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。

· REPEATABLE-READ(可重复读)：对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。

· SERIALIZABLE(可串行化)：最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。

 

MVCC原理

一般是在数据表中加上一个数据版本号version字段，表示数据被修改的次数，当数据被修改时，version值会加一。当线程A要更新数据值时，在读取数据的同时也会读取version值，在提交更新时，若刚才读取到的version值为当前数据库中的version值相等时才更新，否则重试更新操作，直到更新成功。

举一个简单的例子： 假设数据库中帐户信息表中有一个 version 字段，当前值为 1 ；而当前帐户余额字段（ balance ）为 $100 。

1. 操作员 A 此时将其读出（ version=1 ），并从其帐户余额中扣除100-$50 ）。

2. 在操作员 A 操作的过程中，操作员B 也读入此用户信息（ version=1 ），并从其帐户余额中扣除100-$20 ）。

3. 操作员 A 完成了修改工作，将数据版本号加一（ version=2 ），连同帐户扣除后余额（ balance=$50 ），提交至数据库更新，此时由于提交数据版本大于数据库记录当前版本，数据被更新，数据库记录 version 更新为 2 。

4. 操作员 B 完成了操作，也将版本号加一（ version=2 ）试图向数据库提交数据（ balance=$80 ），但此时比对数据库记录版本时发现，操作员 B 提交的数据版本号为 2 ，数据库记录当前版本也为 2 ，不满足 “ 提交版本必须大于记录当前版本才能执行更新 “ 的乐观锁策略，因此，操作员 B 的提交被驳回。

这样，就避免了操作员 B 用基于 version=1 的旧数据修改的结果覆盖操作员A 的操作结果的可能。

 

innoDB采用MVCC来支持高并发，实现4个高标准隔离级别，默认是可重复读，并且通过next-key locking 策略防止幻读的，间隙锁使得innodb不仅仅锁定查询涉及的行，还会对索中的间隙进行锁定，以防止幻影行的插入。

innoDB采用两阶段锁定协议，在事务执行过程中，随时可执行锁定，只有在执行commit或rollback的时候才会释放，并且所有的锁是在同一时记得释放。

特定的语句进行显示锁定：

select .... lock in share mode ；select ... for update;也支持lock tables 和unlock tables这是在服务器层实现的，和存储引擎无关，有自己的用为途，不能替代事务处理。

MVCC是行级锁的一个变种，实现了非阻塞的读操作，写操作也锁定必要的行。

MVCC是通过保存数据在某个时间点的快照来实现的。也就是说，不管需要执行多长时间，每个事务看到的数据是一致的。根据事务开始的先后时间的不同，每个事务对同一张表，同一时刻看到的数据可能是不一样的。

 

InnoDB的MVCC，是通过在每行记录后面保存两个隐藏的列来实现的，一列保存了行创建的时间，一个保存行的过期时间（或删除时间），当然存储的并不是实际的时间值，存储的是系统版本号。每开始一个新的事务，版本号都会递增。事务开始时刻的系统版本号会作为事务的版本号，用来和查询到的每行记录的版本号进行比较。

Select :

a.只查找早于当前事务版本的数据行（也就是，行的系统版本号小于或等于事务的系统版本号），这样可以确保事务读取的行，要么在事务开始前已经存在，要么是事务自身插入或修改过的。

b.行的删除版本要么未定义，要么大于当前事务版本号，这可以确保事务读取到的行在事务开始之前未被删除。

Insert: InnoDB为新插入的行保存当前系统版本号为行版本号

Delete:为删除的行保存当前系统版本号作为行删除标识

Update:为插入一行新记录，保存当前系统版本号作为行版本号，同时保存当前系统版本号到原来的行作为原来的行作为删除标识。

保存这两个额外的系统版本号，使大多数读操作都可以不用加锁。读操作很简单，性能很好，并且能保证只会读取符合标准的行。不足：每行记录都需要额外的存储空间，需要更多的行检查工作。

MVCC只在repeatable read 和read commited这两个隔离级别下工作。

begin/start transaction命令并不是一个事务的起点，在执行到它们之后的第一个操作innodb表的语句时事务才真正启动。如果马上启动一个事务，使用start transaction with consistant snapshot。update/delete/insert在autocmmit=1的情况下，本身就是一个事务，语句完成时会自动提交。

 

所有的更新（增删改）操作都是先读后写的，这个读只能是当前读，还有select * from table for update（排他锁） 、select * from table lock in share mode（加了读锁）也都是当前读。

在autocommit=0时，如果只执行一个select语句，这个事务就启动了，而且不会自动提交，这个事务持续存在直到你主动执行commit或rollback语句或者断开连接。

commit work and chain提交事务并自动启动下一个事务。

事务的可重复读的能力是怎么实现的？可重复读的核心就是一致性读，而事务更新数据的时候只能用当前读，如果当前记录的行锁被其他事务占用的话，就需要进行锁等待。

 

事务不能嵌套

保存点 savepoint 保存点名称 --设置一个事务保存点

rollback to savepoint 保存点名称 --回滚到保存点

release savepoint 保存点名称 --删除保存点