SQL语言学习小结（三）数据库事务

数据库事务(Database Transaction) ：是指作为单个逻辑工作单元执行的一系列操作，要么完整地执行，要么完全地不执行。 事务处理可以确保除非事务性单元内的所有操作都成功完成，否则不会永久更新面向数据的资源。通过将一组相关操作组合为一个要么全部成功要么全部失败的单元，可以简化错误恢复并使应用程序更加可靠。一个逻辑工作单元要成为事务，必须满足所谓的ACID（原子性、一致性、隔离性和持久性）属性。

原子性（Atomic）（Atomicity）：事务必须是原子工作单元；对于其数据修改，要么全都执行，要么全都不执行。通常，与某个事务关联的操作具有共同的目标，并且是相互依赖的。如果系统只执行这些操作的一个子集，则可能会破坏事务的总体目标。原子性消除了系统处理操作子集的可能性。

一致性（Consistent）（Consistency）：事务在完成时，必须使所有的数据都保持一致状态。在相关数据库中，所有规则都必须应用于事务的修改，以保持所有数据的完整性。事务结束时，所有的内部数据结构（如 B 树索引或双向链表）都必须是正确的。某些维护一致性的责任由应用程序开发人员承担，他们必须确保应用程序已强制所有已知的完整性约束。例如，当开发用于转帐的应用程序时，应避免在转帐过程中任意移动小数点。

隔离性（Insulation）（Isolation）：由并发事务所作的修改必须与任何其它并发事务所作的修改隔离。事务查看数据时数据所处的状态，要么是另一并发事务修改它之前的状态，要么是另一事务修改它之后的状态，事务不会查看中间状态的数据。这称为隔离性，因为它能够重新装载起始数据，并且重播一系列事务，以使数据结束时的状态与原始事务执行的状态相同。当事务可序列化时将获得最高的隔离级别。在此级别上，从一组可并行执行的事务获得的结果与通过连续运行每个事务所获得的结果相同。由于高度隔离会限制可并行执行的事务数，所以一些应用程序降低隔离级别以换取更大的吞吐量。

持久性（Duration）（Durability）：事务完成之后，它对于系统的影响是永久性的。该修改即使出现致命的系统故障也将一直保持。

功能实现的语法：

1.SET @@autocommit = 0；（1为自动提交；0为手动提交）
  COMMIT ；ROLLBACK；

2.START TRANSACTION ;启动事务
 COMMIT；成功后提交
 ROLLBACK；回到事务启动前的状态
 SAVEPOINT  点名；设置标记，用于使用“ROLLBACK  TO 点名” 回滚到此点状态

以银行转账为例：

账户A有1000元，账户B也有1000元。现在账户A向账户B转500元，则账户A先减去500元，账户B再增加500元，这两不必须保证同时成功或者同时失败。

第一种方式：

MySQL在执行SQL语句时，会先在事务日志里面执行，之后才将数据写入磁盘（数据库）。

autocommit = 1 时为自动提交（默认为1），autocommit = 0 时为手动提交。（提交方式的切换需要我们手动处理）

首先我们需要将MySQL的默认的自动提交方式改为手动提交

<span style="font-family:Microsoft YaHei;">mysql> set @@autocommit = 0;</span>

账户A减去500元

<span style="font-family:Microsoft YaHei;">mysql> update account_a set money = money - 500;</span>

查询结果

<span style="font-family:Microsoft YaHei;">mysql> select * from account_a, account_b;
+---------+-------+---------+-------+
| account | money | account | money |
+---------+-------+---------+-------+
| A       | 500   | B       | 1000  |
+---------+-------+---------+-------+</span>

此时得到的结果是从事务日志中取出来的，并非从数据库中取出

我们再开一个MySQL客户端，执行如上查询语句

<span style="font-family:Microsoft YaHei;">mysql> select * from account_a, account_b;
+---------+---------+---------+---------+
| account | money   | account | money   |
+---------+---------+---------+---------+
| A       | 1000.00 | B       | 1000.00 |
+---------+---------+---------+---------+</span>

从查询结果我们能够看出，此时数据库中的真实数据并没有被修改

在第一个MySQL客户端中继续执行如下语句

<span style="font-family:Microsoft YaHei;">update account_b set money = money + 500;</span>

<span style="font-family:Microsoft YaHei;"><pre name="code" class="sql">mysql> select * from account_a, account_b;
+---------+-------+---------+-------+
| account | money | account | money |
+---------+-------+---------+-------+
| A       | 500   | B       | 1500  |
+---------+-------+---------+-------+</span>

打开第二个MySQL客户端

<span style="font-family:Microsoft YaHei;">mysql> select * from account_a, account_b;
+---------+---------+---------+---------+
| account | money   | account | money   |
+---------+---------+---------+---------+
| A       | 1000.00 | B       | 1000.00 |
+---------+---------+---------+---------+</span>

此时数据库中的数据依然没有改变

在第一个MySQL客户端中再执行如下语句

<span style="font-family:Microsoft YaHei;">commit;</span>

打开第二个MySQL客户端

<span style="font-family:Microsoft YaHei;">mysql> select * from account_a, account_b
+---------+--------+---------+---------+
| account | money  | account | money   |
+---------+--------+---------+---------+
| A       | 500.00 | B       | 1500.00 |
+---------+--------+---------+---------+</span>

这里我们知道数据库中的数据被修改了。

rollback的作用则是将之前的操作都作废。

第二种方式：

利用start transaction开启一个事务，这种方式开启事务不必我们手动切换提交方式。

账户A还有500元，账户B1500元，现在账户B将转账500元到账户A。

<span style="font-family:Microsoft YaHei;">mysql> update account_b set money = money - 500;</span>

<span style="font-family:Microsoft YaHei;">mysql> update account_a set money = money + 500;
</span><pre name="code" class="sql"><span style="font-family:Microsoft YaHei;">mysql> select * from account_a, account_b;
+---------+-------+---------+-------+
| account | money | account | money |
+---------+-------+---------+-------+
| A       | 1000  | B       | 1000  |
+---------+-------+---------+-------+</span>

再次开启第二个MySQL客户端

<span style="font-family:Microsoft YaHei;">mysql> select * from account_a, account_b
+---------+--------+---------+---------+
| account | money  | account | money   |
+---------+--------+---------+---------+
| A       | 500.00 | B       | 1500.00 |
+---------+--------+---------+---------+</span>

我们看出数据库中的数据依然没有改变，表名我们上面的操作依然是在事务日志中进行的，还未提交到数据库。

在第一个MySQL客户端中执行如下语句

<span style="font-family:Microsoft YaHei;">mysql> commit;</span>

第二个客户端，

<span style="font-family:Microsoft YaHei;">mysql> select * from account_a, account_b;
+---------+---------+---------+---------+
| account | money   | account | money   |
+---------+---------+---------+---------+
| A       | 1000.00 | B       | 1000.00 |
+---------+---------+---------+---------+</span>

commit之后，数据库中的数据被修改了。

rollback的作用依然是回滚到事务开始前的状态。

这种方式下，在start transaction 之后，我们可以利用savepoint设置一个或多个标志点，例如：savepoint a;

当我们知道执行语句有错时，就可以利用rollback to 来回滚到标志点处，例如：rollback to a;

只有commit或者rollback会结束事务。