-
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)
(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 |
| 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 |
| 20009 | 40.78 |
+-----------+---------+
6 rows in set (0.00 sec)
START TRANSACTION;
DELETE FROM orderitems WHERE order_num = 20010;
DELETE FROM orders WHERE order_num = 20010;
COMMIT;
(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 |
| 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 |
| 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)