MySQL事务简介

Contents

1. 1. mysql事务
2. 2. 使用mysql事务
3. 3. mysql事务举例
4. 4. completion_type & savepoint & rollback

本文将会介绍什么是mysql事务，以及如何用commit和rollback来管理事务。

mysql事务

为了方便理解，通过向样例数据库中添加一个订单来说明什么是事务：

• 查询最新的订单号，并使用下一数字作为新增订单号
• 在order表中增加订单
• 在订单详情表orderdetails中添加订单货物
• 查询order和orderdetails表验证结果

想象一下如果上述步骤中途因为数据库锁或其它原因出错会有什么后果？你可能会得到一个空订单并毫无察觉，你之后可能会要花费巨大的精力去修复这个错误。

mysql事务就能对付这种情况。所谓事务就是一组绝不会部分生效mysql操作：如果中途出错，那么状态会回滚至修改前；如果未出错，当然是一切变动都提交到数据库中。

使用mysql事务

在用事务实现上述用例之前，先了解一下最常用的语句。
start transaction开始事务，rollback撤销操作。
需要注意的是有一些语句是不能在事务中使用的，大部分是数据定义语句。

CREATE / ALTER / DROP DATABASE
CREATE /ALTER / DROP / RENAME / TRUNCATE TABLE
CREATE / DROP INDEX
CREATE / DROP EVENT
CREATE / DROP FUNCTION
CREATE / DROP PROCEDURE

commit将事务中的变动提交到数据库中，mysql会默认提交，如果要改为不自动提交，可以

SET autocommit = 0;

mysql事务举例

使用事务之前，你首先得你的语句分组并决定数据何时应该提交或回滚。我们再回顾一下文章开头的用例并加入事务语句。

• start transaction
• 查询最新订单号，并使用下一数字作为新订单号
• 向orders表添加订单
• 向orderdetails表添加订单物品
• commit
• 查询orders和orderdetails确认修改

以下是上述步骤的sql语句

-- start a new transaction
start transaction;
 
-- get latest order number
select @orderNumber := max(orderNUmber) 
from orders;
-- set new order number
set @orderNumber = @orderNumber  + 1;
 
-- insert a new order for customer 145
insert into orders(orderNumber,
                   orderDate,
                   requiredDate,
                   shippedDate,
                   status,
                   customerNumber)
values(@orderNumber,
       now(),
       date_add(now(), INTERVAL 5 DAY),
       date_add(now(), INTERVAL 2 DAY),
       'In Process',
        145);
-- insert 2 order line items
insert into orderdetails(orderNumber,
                         productCode,
                         quantityOrdered,
                         priceEach,
                         orderLineNumber)
values(@orderNumber,'S18_1749', 30, '136', 1),
      (@orderNumber,'S18_2248', 50, '55.09', 2); 
-- commit changes    
commit;       
 
-- get the new inserted order
select * from orders a 
inner join orderdetails b on a.ordernumber = b.ordernumber
where a.ordernumber = @ordernumber;

completion_type & savepoint & rollback

completion_type为1的时候，commit时会生动开启一个新的事务
completion_type为2的时候，commit时会同时释放连接。

mysql> set names utf8;  
Query OK, 0 rows affected (0.00 sec)  
  
mysql> create table t(a int, primary key (a))engine=innodb;  
Query OK, 0 rows affected (0.29 sec)  
  
mysql> select @@autocommit;  
+--------------+  
| @@autocommit |  
+--------------+  
|            1 |  
+--------------+  
1 row in set (0.00 sec)  
  
mysql> set @@completion_type=1;  
Query OK, 0 rows affected (0.00 sec)  
  
mysql> begin;  
Query OK, 0 rows affected (0.00 sec)  
  
mysql> insert into t select 1;  
Query OK, 1 row affected (0.00 sec)  
Records: 1  Duplicates: 0  Warnings: 0  
  
mysql> commit work;  
Query OK, 0 rows affected (0.00 sec)  
  
mysql> insert into t select 2;  
Query OK, 1 row affected (0.00 sec)  
Records: 1  Duplicates: 0  Warnings: 0  
  
mysql> insert into t select 2;  
ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY'  
mysql> rollback;  
Query OK, 0 rows affected (0.00 sec)  
  
# 回滚之后只有1这个记录，而没有2这个记录  
mysql> select * from t;  
+---+  
| a |  
+---+  
| 1 |  
+---+  
1 row in set (0.00 sec)

completion_type设置为2时，commit后继续操作将报错。

mysql> set @@completion_type=2;  
Query OK, 0 rows affected (0.00 sec)  
  
mysql> begin  
    -> ;  
Query OK, 0 rows affected (0.00 sec)  
  
mysql> insert into t select 3;  
Query OK, 1 row affected (0.00 sec)  
Records: 1  Duplicates: 0  Warnings: 0  
  
mysql> commit work;  
Query OK, 0 rows affected (0.00 sec)  
  
mysql> select @@versison;  
ERROR 2006 (HY000): MySQL server has gone away  
No connection. Trying to reconnect...  
Connection id:    205656  
Current database: test  
  
ERROR 1193 (HY000): Unknown system variable 'versison'

savepoint identifier：在事务中创建一个保存点，一个事务允许有多个保存点
release savepoint identifier：删除事务中的保存点，当时一个保存点也没有时执行这个命令，会报错抛出一个异常。

innodb存储引擎中的事务是原子性的，但是一条语句失败并不会导致前一条执行的语句自动回滚，他们的工作会保留，需要你手动commit或者rollback。

mysql>  create table t(a int, primary key (a))engine=innodb;  
Query OK, 0 rows affected (0.24 sec)  
  
mysql> begin  
    -> ;  
Query OK, 0 rows affected (0.00 sec)  
  
mysql> insert into t select 1;  
Query OK, 1 row affected (0.00 sec)  
Records: 1  Duplicates: 0  Warnings: 0  
  
mysql> insert into t select 1;  
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'  
mysql> select * from t;  
+---+  
| a |  
+---+  
| 1 |  
+---+  
1 row in set (0.00 sec)

可以看到，插入第二条记录的时候，db抛出了1062错误，但是并没有自动回滚，能查出前一条insert的记录，这个时候需要我们手动commit或者rollback

rollback to [savepoint] identifier：与savepoint一起使用，可以把事务回滚到标记点

set transaction：设置事务的隔离级别，4种事务隔离级别：read uncommitted，read committed，repeatable read，serializable。
start transaction与begin都可以在mysql命令行下显式地开启一个事务，但是在存储过程中MySQL会自动将begin识别成begin ... end，因此在存储过程中，只能用start transaction。