首页 > 代码库 > 测试MySQL事务管理
测试MySQL事务管理
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 2mysql> insert into test_trans values (1,‘segment‘),(2,‘tablespace‘);Query OK, 2 rows affected (0.21 sec)Records: 2 Duplicates: 0 Warnings: 0mysql> 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: 0mysql> 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 transactionmysql> 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: 0mysql> 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>
测试MySQL事务管理
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。