MariaDB TRANSACTION

MariaDB TRANSACTION

Transaction—A block of SQL statements
Rollback—The process of undoing specified SQL statements
Commit—Writing unsaved SQL statements to the database tables
Savepoint—A temporary placeholder in a transaction set to which you can issue a rollback (as opposed to rolling back an entire transaction)

始终以START TRANSACTION为新起点遇到最后的ROLLBACK savepoint或COMMIT时自动关闭

ROLLBACK

(jlive)[crashcourse]>SELECT * FROM ordertotals; START TRANSACTION;

+-----------+---------+

| order_num | total |

+-----------+---------+

| 20005 | 158.86 |

| 20006 | 58.30 |

| 20007 | 1060.00 |

| 20008 | 132.50 |

| 20009 | 40.78 |

+-----------+---------+

6 rows in set (0.00 sec)

(jlive)[crashcourse]>START TRANSACTION;

Query OK, 0 rows affected (0.00 sec)

(jlive)[crashcourse]>DELETE FROM ordertotals;

Query OK, 6 rows affected (0.00 sec)

(jlive)[crashcourse]>SELECT * FROM ordertotals;

Empty set (0.00 sec)

(jlive)[crashcourse]>ROLLBACK;

Query OK, 0 rows affected (0.00 sec)

(jlive)[crashcourse]>SELECT * FROM ordertotals;

+-----------+---------+

| order_num | total |

+-----------+---------+

| 20005 | 158.86 |

| 20006 | 58.30 |

| 20007 | 1060.00 |

| 20008 | 132.50 |

| 20009 | 40.78 |

+-----------+---------+

6 rows in set (0.00 sec)

COMMIT

START TRANSACTION;
DELETE FROM orderitems WHERE order_num = 20010;

DELETE FROM orders WHERE order_num = 20010;

COMMIT;

SAVEPOINT

(jlive)[crashcourse]>START TRANSACTION;

Query OK, 0 rows affected (0.00 sec)

(jlive)[crashcourse]>SELECT * FROM ordertotals;

+-----------+---------+

| order_num | total |

+-----------+---------+

| 20005 | 158.86 |

| 20006 | 58.30 |

| 20007 | 1060.00 |

| 20008 | 132.50 |

| 20009 | 40.78 |

+-----------+---------+

6 rows in set (0.00 sec)

(jlive)[crashcourse]>SAVEPOINT delete1;

Query OK, 0 rows affected (0.00 sec)

(jlive)[crashcourse]>DELETE FROM ordertotals WHERE order_num = 20009;

Query OK, 2 rows affected (0.00 sec)

(jlive)[crashcourse]>SELECT * FROM ordertotals;

+-----------+---------+

| order_num | total |

+-----------+---------+

| 20005 | 158.86 |

| 20006 | 58.30 |

| 20007 | 1060.00 |

| 20008 | 132.50 |

+-----------+---------+

4 rows in set (0.01 sec)

(jlive)[crashcourse]>SAVEPOINT delete2;

Query OK, 0 rows affected (0.00 sec)

(jlive)[crashcourse]>DELETE FROM ordertotals WHERE order_num = 20005;

Query OK, 1 row affected (0.01 sec)

(jlive)[crashcourse]>SELECT * FROM ordertotals;

+-----------+---------+

| order_num | total |

+-----------+---------+

| 20006 | 58.30 |

| 20007 | 1060.00 |

| 20008 | 132.50 |

+-----------+---------+

3 rows in set (0.00 sec)

(jlive)[crashcourse]>SAVEPOINT delete3;

Query OK, 0 rows affected (0.00 sec)

(jlive)[crashcourse]>DELETE FROM ordertotals WHERE order_num = 20008;

Query OK, 1 row affected (0.00 sec)

(jlive)[crashcourse]>SELECT * FROM ordertotals;

+-----------+---------+

| order_num | total |

+-----------+---------+

| 20006 | 58.30 |

| 20007 | 1060.00 |

+-----------+---------+

2 rows in set (0.00 sec)

(jlive)[crashcourse]>ROLLBACK TO delete3;

Query OK, 0 rows affected (0.01 sec)

(jlive)[crashcourse]>SELECT * FROM ordertotals;

+-----------+---------+

| order_num | total |

+-----------+---------+

| 20006 | 58.30 |

| 20007 | 1060.00 |

| 20008 | 132.50 |

+-----------+---------+

3 rows in set (0.00 sec)

(jlive)[crashcourse]>ROLLBACK TO delete2;

Query OK, 0 rows affected (0.00 sec)

(jlive)[crashcourse]>SELECT * FROM ordertotals;

+-----------+---------+

| order_num | total |

+-----------+---------+

| 20005 | 158.86 |

| 20006 | 58.30 |

| 20007 | 1060.00 |

| 20008 | 132.50 |

+-----------+---------+

4 rows in set (0.00 sec)

(jlive)[crashcourse]>ROLLBACK TO delete1;

Query OK, 0 rows affected (0.00 sec)

(jlive)[crashcourse]>SELECT * FROM ordertotals;

+-----------+---------+

| order_num | total |

+-----------+---------+

| 20005 | 158.86 |

| 20006 | 58.30 |

| 20007 | 1060.00 |

| 20008 | 132.50 |

| 20009 | 40.78 |

+-----------+---------+

6 rows in set (0.00 sec)

修改默认的COMMIT方式

(jlive)[crashcourse]>SHOW VARIABLES LIKE 'autocommit';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| autocommit | ON |

+---------------+-------+

1 row in set (0.00 sec)

(jlive)[crashcourse]>SET autocommit = 0;

Query OK, 0 rows affected (0.00 sec)

(jlive)[crashcourse]>SHOW VARIABLES LIKE 'autocommit';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| autocommit | OFF |

+---------------+-------+

1 row in set (0.00 sec)