MariaDB – START TRANSACTION

測試環境為 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 名稱 可以刪除回原點.

沒有解決問題,試試搜尋本站其他內容

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *

這個網站採用 Akismet 服務減少垃圾留言。進一步了解 Akismet 如何處理網站訪客的留言資料