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>