数据库事务(InnoDB)

事务

​ 用户定义的一个数据库操作序列，这些操作要么全做要么全不做，是一个不可分割的工作单位。

事务的四个特性(ACID)

原子性(A)

​ 整个数据库事务是不可分割的工作单位。只有使事务中所有的数据库操作都执行成功，才算整个事务成功。事务中任何一个sql语句执行失败，已经执行成功的sql也必须撤销，数据库状态应该退回到执行事务前的状态。

一致性©

​ 指事务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态。在事务开始之前和结束之后，数据库的完整性约束没有被破坏。

隔离性(I)

​ 一个事务的执行不能被其他事务干扰。即一个事务内部的操作和使用的数据对其他并发事务时隔离的，并发执行的事务之间不能相互干扰。

持久性(D)

​ 事务一旦提交，它对数据库中数据的改变时永久性的。

事务的实现

​ 事务的隔离性用数据库锁来实现。原子性、一致性和持久性通过redo log和undo log来完成。

​ redo和undo均可视为一种恢复操作，redo恢复提交事务修改的也操作，而undo回滚行记录到某个特定版本。因此两者记录的内容不同，redo通常时物理日志，记录的是页的物理修改操作。undo是逻辑日志，根据每行记录进行修改。

redo

​ 重做日志用来实现事务的持久性。即ACID总的D。由两部分组成：内存中的重做日志缓冲(redo log buffer是易失的)和重做日志文件(redo log file是持久的)。

​ InnoDB是事务的存储引擎，通过Force Log at Commit(提交时强制记录)机制实现事务的持久性，即当事务提交时，必须先将该事务的所有日志写入到日志重做文件进行持久化，等事务的commit操作完成才算完成。这里的日志指重做日志，在InnoDB中由redo log和undo两部分组成。redo log用来保证事务的完整性，undo log用来帮助事务回滚和MVCC(多版本并发控制Multi-Version Concurrency Control)功能。

undo

​ 重做日志记录了事务的行为，可以很好地通过其对页进行"重做"操作。但是事务有需要回滚操作，这时就需要undo。InnoDB在事务运行的时候不仅记录了redo，还会记录一定的undo。这样如果用户执行的事务由于某种原因需要回滚，就可以利用undo信息将数据库恢复到修改之前的样子。

​ 用户通常对undo有这样的误解：undo用于将数据库无力的恢复到执行语句或事务之前的样子。事实并非如此，undo时逻辑日志，因此只是将数据库逻辑的恢复到原来的样子。所有的修改都被逻辑的取消了，但是数据结构和页本身在回滚之后可能大不相同。因为在多用户高并发的系统中，同时运行着成百上千的事务。数据库的主要任务就是协调对数据记录的并发访问。比如一个事务在修改某页的几条记录，同时还有别的事务对同一页中的另几条记录进行修改。因此不能将一个页的记录回滚到事务开始的样子，这样会影响其他事务正在进行的工作。
​ 例如：用户执行了insert语句，插入了10W的记录的事务，这个事务会导致分配一个新的断，即表空间会增大。在用户执行Rollback时，会将插入的事务回滚，但是表空间的大小并不会因此再缩小到之前的大小。因此，当InnoDB回滚时，实际上做的时和之前相反的工作。对于每个Insert，InnoDB会完成一个Delete;对于update会执行一个相反的update。

事务的控制语句

​ 在Mysql命令行的默认设置下，事务都是自动提交(Auto commit)的，即执行完sql就会立马执行Commit操作。所有要显式的开启一个事务，需要使用命令Begin、Start Transaction，或者执行命令SET AOTUCOMMIT=0来禁止当前会话自动提交。

START TRANSACTION|BEGIN：显示的开启一个事务；
COMMIT：提交事务，并使得已对数据库的修改成为永久性的；
ROLLBACK：回滚事务，结束当前事务，并撤销正在执行的所有未提交的修改；
SAVEPOINT identifier：SAVEPOINT运行在事务中创建一个保存点，一个事务中可以有多个保存点；
ROLLBACK TO[SAVEPOINT]identifier：和SAVEPOINT命令一起使用，可以把事务回滚到标记点，而不回滚此标记之前的任何操作；
SET TRANSACTION：设置事务的隔离级别；

事务的隔离级别

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

• READ UNCOMMITTED
• READ COMMITTED
• REPEATABLE READ
• SERIALIZABLE

READ UNCOMMITTED

​ 事务最低的隔离级别，运行另外一个事务可以看到事务未提交的数据。这种隔离界别会产生脏读，不可重复度和幻读。

*首先设置当前客户端的隔离级别

*事务A：启动一个事务

*事务B：启动一个事务，在B事务中执行更新语句，不提交

*事务A：查询表里面的数据，发现读取到了事务B没有提交的数据

*事务B：回滚，未提交修改

*事务A：在事务A中重新查看，发现B没有提交数据

脏读意味着事务A中读取到了事务B中未提交的数据。

READ COMMITTED

​ 保证A事务只有在B事务提交之后才能读取到数据，但是在提交之前B事务可以读取到未提交的数据。

*首先设置当前客户端的隔离级别

*事务A：启动一个事务

*事务B：启动一个事务，在B事务中执行更新语句，不提交

*事务A：在事务A中不能查看到B未提交的更新

*事务B：提交修改

*事务A：因为事务B已经提交，此时事务A中可以读取到B的修改

同一个事务(事务A)中，相同的sql语句，得到的结果却不一样。

REPEATABLE READ

​ 这种事务隔离级别可以防止脏读，不可重复读。但是可能出现幻读。InnoDB存储引擎默认的隔离级别。

*首先设置当前客户端的隔离级别

*事务A：启动一个事务

*事务B：启动一个事务，在B事务中执行更新语句，并提交

*事务A：在事务A中不能查看到B已经提交的更新，解决了不可重复读的问题。

*事务A：提交事务A，才能查看到数据更新

SERIALIZABLE

​ 最高的隔离级别，强制事务进行排序，穿行化执行。解决幻读问题。

不好的事务习惯

循环中提交事务

​ 如果在循环中提交事务：1.如果发生异常，数据库会停留在一个位置的位置。2.每次提交都要写一次重做日志文件，效率低下。

CREATE PROCEDURE load1(count INT UNSIGNED)
BEGIN
DECLARE s INT UNSIGNED DEFAULT 1;
DECLARE c CHAR(20) DEFAULT REPEAT('a'，20);
WHILE s <= count DO
INSERT INTO t1 SELECT NULL,c;
COMMIT;
SET s = s+1;
END WHILE;
END;

CREATE PROCEDURE load2(count INT UNSIGNED)
BEGIN
DECLARE s INT UNSIGNED DEFAULT 1;
DECLARE c CHAR(20) DEFAULT REPEAT('a'，20);
START TRANSACTION;
WHILE s <= count DO
INSERT INTO t1 SELECT NULL,c;
SET s = s+1;
END WHILE;
COMMIT;
END;

显然load2要比load1快很多，因为在load1中实际写重做日志的次数为10000次，而load2中只写了一次。

使用自动提交

​ 不同语言的API对自动提交的默认是不一样的。Mysql C Api默认的提交方式是自动提交，而Mysql python Api则会自动执行SET AUTOCOMMIT=0来禁止自动提交。所以最好还是将事务的控制全掌握在开发端，在程序中显式的进行事务的开始和结束。