測試環境為 CentOS8 (虛擬機)
參考文章 – https://mariadb.com/kb/en/start-transaction/
- START TRANSACTION [transaction_property [, transaction_property] …] | BEGIN [WORK]
- COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
- ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
- SET autocommit = {0 | 1}
先建立等一下要測試用的資料庫與資料表.
[root@localhost ~]# mysql -u root -p Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 9 Server version: 10.3.17-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> CREATE DATABASE testdb; Query OK, 1 row affected (0.00 sec) MariaDB [(none)]> USE testdb; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed MariaDB [testdb]> CREATE TABLE tmp_table ( id integer primary key auto_increment, student VARCHAR(100)); Query OK, 0 rows affected (0.005 sec)
須注意預設資料庫引擎 (Engine) InnoDB 有支援 Rollback 與 Commit ( MyISAM 則不支援 ) , 但 DDL (Data Definition Language) – CREATE, ALTER, DROP 以及 administrative statements (FLUSH, RESET, OPTIMIZE, ANALYZE, CHECK, REPAIR, CACHE INDEX 是無法 Rollback 的.
以下可以看到預設的 資料庫引擎 (Engine) 是 InnoDB .
MariaDB [testdb]> SHOW ENGINES; +--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+ | Engine | Support | Comment | Transactions | XA | Savepoints | +--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+ | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO | | MRG_MyISAM | YES | Collection of identical MyISAM tables | NO | NO | NO | | CSV | YES | Stores tables as CSV files | NO | NO | NO | | BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO | | MyISAM | YES | Non-transactional engine with good performance and small data footprint | NO | NO | NO | | ARCHIVE | YES | gzip-compresses tables for a low storage footprint | NO | NO | NO | | FEDERATED | YES | Allows to access tables on other MariaDB servers, supports transactions and more | YES | NO | YES | | PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO | | SEQUENCE | YES | Generated tables filled with sequential values | YES | NO | YES | | InnoDB | DEFAULT | Supports transactions, row-level locking, foreign keys and encryption for tables | YES | YES | YES | | Aria | YES | Crash-safe tables with MyISAM heritage | NO | NO | NO | +--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+ 11 rows in set (0.000 sec)
autocommit
系統預設為 autocommit , 如要使用 Roolback 需先關閉,也可以使用 transaction management statements BEGIN 或是 START TRANSACTION 讓使用者可以自行決定 Rollback 或是 Commit (下面範例).
MariaDB [testdb]> SHOW VARIABLES LIKE 'autocommit'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | autocommit | ON | +---------------+-------+ 1 row in set (0.000 sec)
MariaDB [testdb]> SET AUTOCOMMIT=0; Query OK, 0 rows affected (0.000 sec) MariaDB [testdb]> SHOW VARIABLES LIKE 'autocommit'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | autocommit | OFF | +---------------+-------+ 1 row in set (0.001 sec)
Rollback 與 Commit
這邊來看一下 Rollback 與 Commit 的使用方式.
使用 START TRANSACTION 或是 BEGIN , 後面可以透過 COMMIT 或是 ROLLBACK (使用 ROLLBACK WORK 是一樣的指令) 來確定這些動作(資料)是否需要被確認(寫入).
MariaDB [testdb]> BEGIN; Query OK, 0 rows affected (0.000 sec)
新增一筆資料.
MariaDB [testdb]> INSERT INTO tmp_table (student) VALUES('Ben');
Query OK, 1 row affected (0.001 sec)
目前是看的到資料.
MariaDB [testdb]> SELECT * FROM tmp_table; +----+---------+ | id | student | +----+---------+ | 1 | Ben | +----+---------+ 1 row in set (0.000 sec)
下 Roolback 後,原先輸入的資料就不存在了.
MariaDB [testdb]> ROLLBACK; Query OK, 0 rows affected (0.001 sec) MariaDB [testdb]> SELECT * FROM tmp_table; Empty set (0.000 sec)
這次下 Commit 後資料的確寫到資料庫中了.
MariaDB [testdb]> INSERT INTO tmp_table (student) VALUES('Ben');
Query OK, 1 row affected (0.000 sec)
MariaDB [testdb]> SELECT * FROM tmp_table;
+----+---------+
| id | student |
+----+---------+
| 2 | Ben |
+----+---------+
1 row in set (0.000 sec)
MariaDB [testdb]> COMMIT;
Query OK, 0 rows affected (0.001 sec)
MariaDB [testdb]> ROLLBACK;
Query OK, 0 rows affected (0.000 sec)
MariaDB [testdb]> SELECT * FROM tmp_table;
+----+---------+
| id | student |
+----+---------+
| 2 | Ben |
+----+---------+
1 row in set (0.000 sec)
SAVEPOINT
我們可以建立 儲存點 ( SAVEPOINT 名稱 ) 來依據情況來決定要回到哪一個還原點.
一樣使用前面建立好的資料表,但先清空剛剛的資料.
MariaDB [testdb]> TRUNCATE tmp_table; Query OK, 0 rows affected (0.005 sec) MariaDB [testdb]> SELECT * FROM tmp_table; Empty set (0.000 sec)
建立了 3 個還原點 Test1 , Test2 與 Test3 .
MariaDB [testdb]> START TRANSACTION;
Query OK, 0 rows affected (0.000 sec)
MariaDB [testdb]> SAVEPOINT Test1;
Query OK, 0 rows affected (0.000 sec)
MariaDB [testdb]> INSERT INTO tmp_table (student) VALUES('Ben Test1');
Query OK, 1 row affected (0.000 sec)
MariaDB [testdb]> SAVEPOINT Test2;
Query OK, 0 rows affected (0.000 sec)
MariaDB [testdb]> INSERT INTO tmp_table (student) VALUES('Ben Test2');
Query OK, 1 row affected (0.000 sec)
MariaDB [testdb]> SAVEPOINT Test3;
Query OK, 0 rows affected (0.000 sec)
MariaDB [testdb]> INSERT INTO tmp_table (student) VALUES('Ben Test3');
Query OK, 1 row affected (0.000 sec)
MariaDB [testdb]> SELECT * FROM tmp_table;
+----+-----------+
| id | student |
+----+-----------+
| 1 | Ben Test1 |
| 2 | Ben Test2 |
| 3 | Ben Test3 |
+----+-----------+
3 rows in set (0.000 sec)
回到 Test2 還原點,回去後就無法再回到在 Test2 之後的 Test3 .
MariaDB [testdb]> ROLLBACK TO Test2; Query OK, 0 rows affected (0.000 sec) MariaDB [testdb]> SELECT * FROM tmp_table; +----+-----------+ | id | student | +----+-----------+ | 1 | Ben Test1 | +----+-----------+ 1 row in set (0.000 sec) MariaDB [testdb]> ROLLBACK TO Test3; ERROR 1305 (42000): SAVEPOINT Test3 does not exist
可以繼續回到 Test1 還原點.
MariaDB [testdb]> ROLLBACK TO Test1; Query OK, 0 rows affected (0.001 sec) MariaDB [testdb]> SELECT * FROM tmp_table; Empty set (0.000 sec) MariaDB [testdb]> COMMIT; Query OK, 0 rows affected (0.000 sec)
Note : 使用 RELEASE SAVEPOINT 名稱 可以刪除回原點.
沒有解決問題,試試搜尋本站其他內容