测试MySQL事务管理

浏览数：53 / 时间：2015年06月12日

1.MySQL 版本

mysql> select version();
+------------+
| version()  |
+------------+
| 5.5.37-log |
+------------+
1 row in set (0.00 sec)

2.创建测试表

mysql> create table test_trans(id int ,name_ varchar(10));
Query OK, 0 rows affected (0.29 sec)

mysql> show table status like ‘test_trans%‘;
+------------+--------+---------+------------+------+----------------+-------------+-
| Name       | Engine | Version | Row_format | Rows | Avg_row_length | Data_length |
+------------+--------+---------+------------+------+----------------+-------------+-
| test_trans | InnoDB |      10 | Compact    |    0 |              0 |       16384 |
+------------+--------+---------+------------+------+----------------+-------------+-
1 row in set (0.00 sec)

3.测试事物

MySQL通过SET AUTOCOMMIT, START TRANSACTION, COMMIT和ROLLBACK等语句支持本地
事务。
语法：
START TRANSACTION | BEGIN [WORK]
COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
SET AUTOCOMMIT = {0 | 1}

默认情况下，mysql是autocommit的，如果需要通过明确的commit和rollback来提交和
回滚事务，需要通过明确的事务控制命令来开始事务，这是和oracle的事务管理明显不
同的地方。START TRANSACTION或BEGIN语句可以开始一项新的事务。
COMMIT和ROLLBACK用来提交或者回滚事务。

START TRANSACTION或BEGIN语句可以开始一项新的事务。
COMMIT和ROLLBACK用来提交或者回滚事务。
CHAIN和RELEASE子句分别用来定义在事务提交或者回滚之后的操作，chain会立即启动
一个新事物，并且和刚才的事务具有相同的隔离级别，release则会断开和客户端的连接

mysql> begin
    -> insert into test_trans values(1,‘segment‘),(2,‘tablespace‘);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘insert into test_trans values(1,‘segment‘),(2,‘tablespace‘)‘ at line 2
mysql> insert into test_trans values (1,‘segment‘),(2,‘tablespace‘);
Query OK, 2 rows affected (0.21 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from test_trans;
+------+------------+
| id   | name_      |
+------+------------+
|    1 | segment    |
|    2 | tablespace |
+------+------------+
2 rows in set (0.00 sec)

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

mysql> select * from test_trans;
+------+------------+
| id   | name_      |
+------+------------+
|    1 | segment    |
|    2 | tablespace |
+------+------------+
2 rows in set (0.00 sec)

a.没有成功，报错了，发现 begin 语句不是这样写的，之后再测试
mysql> truncate table  test_trans;
Query OK, 0 rows affected (0.06 sec)

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

mysql> insert into test_trans values (1,‘segment‘),(2,‘tablespace‘);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from test_trans;
+------+------------+
| id   | name_      |
+------+------------+
|    1 | segment    |
|    2 | tablespace |
+------+------------+
2 rows in set (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.08 sec)

mysql> select * from test_trans;
Empty set (0.00 sec)
mysql>

b.测试下 start transaction
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test_trans values (1,‘segment‘),(2,‘tablespace‘);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from test_trans;
+------+------------+
| id   | name_      |
+------+------------+
|    1 | segment    |
|    2 | tablespace |
+------+------------+
2 rows in set (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.07 sec)

mysql> select * from test_trans;
Empty set (0.00 sec)

mysql>