測試MySQL交易管理

標籤：style   blog   io   ar   color   sp   for   on   div   

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交易管理