事务控制

事务(Transaction) 是访问和更新数据库的基本执行单元，一个事务中可能会包含多个 SQL 语句，事务中的这些 SQL 语句要么都执行，要么都不执行，而 MySQL 它是一个关系型数据库，它自然也是支持事务的。事务同时也是区分关系型数据库和非关系型数据库的一个重要的方面。

在 MySQL 事务中，主要涉及的语法包含 SET AUTOCOMMIT、START TRANSACTION、COMMIT 和 ROLLBACK 等。

自动提交

在 MySQL 中，事务默认是自动提交(Autocommit)的，如下所示

show variables like 'autocommit';


在自动提交的模式下，每个 SQL 语句都会当作一个事务执行提交操作，例如我们上面使用的更新语句

update cxuan005 set info='cxuan' where id = 111;

如果想要关闭数据库的自动提交应该怎么做呢？

其实，MySQL 是可以关闭自动提交的，你可以执行

set autocommit = 0;


然后我们再看一下自动提交是否关闭了，再次执行一下 show variables like 'autocommit' 语句

可以看到，自动提交已经关闭了，再次执行

set autocommit = 1;

会再次开启自动提交。

这里注意一下特殊操作。

在 MySQL 中，存在一些特殊的命令，如果在事务中执行了这些命令，会马上强制执行 commit 提交事务；比如 DDL 语句(create table/drop table/alter/table)、lock tables 语句等等。

不过，常用的 select、insert、update 和 delete命令，都不会强制提交事务。

手动提交

如果需要手动 commit 和 rollback 的话，就需要明确的事务控制语句了。

典型的 MySQL 事务操作如下

start transaction;
... # 一条或者多条语句
commit;


上面代码中的 start transaction 就是事务的开始语句，编写 SQL 后会调用 commit 提交事务，然后将事务统一执行，如果 SQL 语句出现错误会自动调用 Rollback 进行回滚。

下面我们就通过示例来演示一下 MySQL 的事务，同样的，我们需要启动两个窗口来演示，为了便于区分，我们使用 mysql01 和 mysql02 来命名。

我们用 start transaction 命令启动一个事务，然后再 cxuan005 表中插入一条数据，此时 mysql02 不做任何操作。涉及的 SQL 语句如下。

start transaction;

然后执行

select * from cxuan005;


查询一下 cxuan005 中的数据

嗯。。。很多长度太长了，现在我们把所有的 info 数据都更新为 cxuan 。

update cxuan005 set info='cxuan';

更新完毕后，我们先不提交事务，分别在 mysql01 和 mysql02 中进行查询，发现只有 mysql01 窗口中的查询已经生效，而 mysql02 中还是更新前的数据

现在我们在 mysql01 中 commit 当前事务，然后在 mysql02 中查询，发现数据已经被修改了。

除了 commit 之外，MySQL 中还有 commit and chain 命令，这个命令会提交当前事务并且重新开启一个新的事务。如下代码所示

start transaction; # 开启一个新的事务
insert into cxuan005(id,info) values (555,'cxuan005'); # 插入一条数据
commit and chain; # 提交当前事务并重新开启一个事务


上面是一个事务操作，在 commit and chain 键入后，我们可以再次执行 SQL 语句

update cxuan005 set info = 'cxuan' where id = 555;
commit;

然后再次查询

select * from cxuan005;


执行后，可以发现，我们仅仅使用了一个 start transaction 命令就执行了两次事务操作。

如果在手动提交的事务中，你发现有一条 SQL 语句写的不正确或者有其他原因需要回滚，那么此时你就会用到 rollback 语句，它会回滚当前事务，相当于什么也没发生。如下代码所示。

start transaction;
delete from cxuan005 where id = 555;
rollback;

这里切忌一点：delete 删除语句一定要加 where ，不加 where 语句的删除就是耍流氓。

在同一个事务操作中，最好使用相同存储引擎的表，如果使用不同存储引擎的表后，rollback 语句会对非事务类型的表进行特别处理，因此 commit 、rollback 只能对事务类型的表进行提交和回滚。

我们提交的事务一般都会被记录到二进制的日志中，但是如果一个事务中包含非事务类型的表，那么回滚操作也会被记录到二进制日志中，以确保非事务类型的表可以被复制到从数据库中。

这里解释一下什么是事务表和非事务表

事务表和非事务表

事务表故名思义就是支持事务的表，支不支持事务和 MySQL 的存储类型有关，一般情况下，InnoDB 存储引擎的表是支持事务的，关于 InnoDB 的知识，我们会在后面详细介绍。

非事务表相应的就是不支持事务的表，在 MySQL 中，存储引擎 MyISAM 是不支持事务的，非事务表的特点是不支持回滚。

对于回滚的话，还要讲一点就是 SAVEPOINT，它能指定事务回滚的一部分，但是不能指定事务提交的一部分。SAVEPOINT 可以指定多个，在满足不同条件的同时，回滚不同的 SAVEPOINT。

需要注意的是，如果定义了两个相同名称的 SAVEPOINT，则后面定义的 SAVEPOINT 会覆盖之前的定义。如果 SAVEPOINT 不再需要的话，可以通过 RELEASE SAVEPOINT 来进行删除。删除后的 SAVEPOINT 不能再执行 ROLLBACK TO SAVEPOINT 命令。

我们通过一个示例来进行模拟不同的 SAVEPOINT

首先先启动一个事务 ，向 cxuan005 中插入一条数据，然后进行查询，那么是可以查询到这条记录的

start transaction;
insert into cxuan005(id,info) values(666,'cxuan666');
select * from cxuan005 where id = 666;


查询之后的记录如下

然后我们定义一个 SAVEPOINT，如下所示

savepoint test;

然后继续插入一条记录

insert into cxuan005(id,info) values(777,'cxuan777');


此时就可以查询到两条新增记录了，id 是 666 和 777 的记录。

select * from cxuan005 where id = 777;

那么我们可以回滚到刚刚定义的 SAVEPOINT

rollback to savepoint test;


再次查询 cxuan005 这个表，可以看到，只有 id=666 的这条记录插入进来了，说明 id=777 这条记录已经被回滚了。

此时我们看到的都是 mysql01 中事务还没有提交前的状态，所以这时候 mysql02 中执行查询操作是看不到 666 这条记录的。

然后我们在 mysql01 中执行 commit 操作，那么此时在 mysql02 中就可以查询到这条记录了。