transaction (2)—mysql进阶（五十八)

上篇文章说了acid四个事务的特性，原子性保证要不两个sql一起执行，要么不执行，隔离性，两个事务之间必须互不干扰，一致性，两边的数据必须保持一致，可以说一致性的前提是原子性和隔离性必须正常，但原子性和隔离性都正常，就能保证一致性吗？并不是，还必须满足其他一些约束，比如金额不能为负数。持久性就是必须持久化到磁盘才算事务成功。

​​transaction (1)—mysql进阶（五十七)​​

Mysql中事务语法

前面我们重点介绍了理论知识，那么我们在mysql里如何使用呢？

开启事务

我们可以用命令开启事务：

mysql> BEGIN;

Query OK, 0 rows affected (0.00 sec)

mysql> START TRANSACTION;

Query OK, 0 rows affected (0.00 sec)

Begin开启事务或者start transaction开启事务。

不过start transaction 语句后面可以跟随几个修饰符，就是他们几个，

Read only：表示当前事务是只能读取数据，不能修改数据。（如果我们使用create temporary table创建的临时表，由于他们在当前会话可见，所以在只读事务里，也可以对临时表增，删，改）

Read write：表示当前事务是一个读写事务，也可以修改表。

With consistent snapshot ：启动一致性读。

//开启一个事务

START TRANSACTION READ ONLY;

//开启多个事务

START TRANSACTION READ ONLY, WITH CONSISTENT SNAPSHOT;

注意：一个事务不能同时设置只读和读写，这样就有冲突了，另外，如果我们没有显式指定开启什么访问模式，那么默认就是读写访问模式。

提交事务

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE account SET balance = balance - 10 WHERE id = 1;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> UPDATE account SET balance = balance + 10 WHERE id = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 

mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)

一个完整的事务过程，开启之后，必须commit之后，才会刷新到磁盘。

手动中止事务

如果我们在执行sql的时候，写到一半，发现错了怎么办呢？比如狗给猫转10元，但是写成了转100，这时候我们只要吧上面的commit换成rollback就好。

mysql> ROLLBACK;

Query OK, 0 rows affected (0.00 sec)

值得注意的是，如果在平时开发中，遇到异常，mysql会自动帮我们回滚，这里的回滚是我们自己手动的。

支持事务的存储引擎

我们前面说了，mySIAM是不支持事务的，而支持事务的存储引擎是innoDB,如果开启事务后，mySIAM存储引擎insert into一条数据，然后rollback，这时候 数据不会回滚，查询会查到刚那条数据。但如果是innoDB开启事务后，insert into一条数据，然后rollback，这时候数据会回滚，不会查询到那条数据。

自动提交

Mysql有个系统变量autocommit，

mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.00 sec)

这里默认是on，但这个on代表的是每条sql语句有一个独立的事务，这种特性称为事务的自动提交。加入狗给猫转了10元钱，猫加了10元钱，这两个sql语句是在不同的独立事务进行的。

当然如果想关闭这种自动提交，让他们在同一个事务里，可以显式用begin或者start straction启动，或者set autocommit 为off。

这样我们写的sql都在同一个事务里面，直到我们显式commit或者rollback。

隐式提交

前面说过，当我们显式start transaction 和begin ，或者吧系统变量autocommit设置成off，则不会自动提交事务，但如果我们 没有写commit或者rollback，有的语句也会触发提交事务，这些就称为隐式提交。

当我们定义或者修改数据库对象的数据定义语言(DDL)：所谓的数据库对象，指的是数据库，表，视图，存储过程等等这些东西，当我们使用create ，alter，drop等语法对这些数据库对象进行操作的时候，就会隐式提交事务。

BEGIN;

SELECT ... # 事务中的一条语句

UPDATE ... # 事务中的一条语句

... # 事务中的其它语句

CREATE TABLE ... # 此语句会隐式的提交前边语句所属于的事务

隐式使用或修改mysql数据库的表

当我们使用alert user，create user，drop user，grant，rename user，revoke，set password等语句也会隐式提交这些内容。

事务控制或关于锁定的语句

当我们事务开没提交，在sql之后，又写了一个start transaction或者begin，会隐式提交前面的sql。

如果吧autocommit 改为on，也会提交事务。

如果有lock tables，unlock tables，也会提交事务。

Mysql复制的语句

使用start slave，stop slave，reset slave，change master to 等语句也会触发提交。

还有很多其他语句也会触发。

保存点

当我们写了一大堆sql，发现其中一个sql写错，然后就rollback，全部恢复，这样总有一夜回到解放前的感觉，怎么解决这个困境呢？

我们可以在sql执行完一段时候，写一个保存点，然后rollback  to 保存点。

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> update user set uid = '55' where id = '3';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> savepoint s1;
Query OK, 0 rows affected (0.00 sec)

mysql> update user set uid = '55' where id = '4';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> rollback to s1;
Query OK, 0 rows affected (0.00 sec)

大家可以看到，这里就回到了保存点，保存点前面的sql全部都执行了。

RELEASE SAVEPOINT 保存点名称;