测试mysql事物_测试MySQL事务管理

朴灿烈づ我的快乐病毒、 2022-10-25 14:10 139阅读 0赞

1.MySQL 版本

mysql> selectversion();+------------+

| 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.29sec)

mysql> show table status like 'test\_trans%';+------------+--------+---------+------------+------+----------------+-------------+-

\+------------+--------+---------+------------+------+----------------+-------------+-

| 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');

ERROR1064 (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 2mysql> insert into test\_trans values (1,'segment'),(2,'tablespace');

Query OK,2 rows affected (0.21sec)

Records:2 Duplicates: 0 Warnings: 0mysql> select \* fromtest\_trans;+------+------------+

| id | name\_ |

\+------+------------+

| 1 | segment |

| 2 | tablespace |

\+------+------------+

2 rows in set (0.00sec)

mysql> rollback;

Query OK,0 rows affected (0.00sec)

mysql> select \* fromtest\_trans;+------+------------+

| id | name\_ |