SAVEPOINT a;
SAVEPOINT and ROLLBACK TO b;
A transaction is a unit of work that is performed against a database. Transactions are units or sequences of work accomplished in a logical order, whether in a manual fashion by a user or automatically by some sort of a database program
Properties of Transactions
Transactions have the following four standard properties, usually referred to by the acronym ACID.
Atomicity − ensures that all operations within the work unit are completed successfully. Otherwise, the transaction is aborted at the point of failure and all the previous operations are rolled back to their former state.
Consistency − ensures that the database properly changes states upon a successfully committed transaction.
Isolation − enables transactions to operate independently of and transparent to each other.
Durability − ensures that the result or effect of a committed transaction persists in case of a system failure.
Enter password: *******
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 20
Server version: 8.0.25 MySQL Community Server - GPL
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use ram;
Database changed
mysql> select * from orders;
+----+------------+--------+-------------+
| id | order_date | amount | customer_id |
+----+------------+--------+-------------+
| 12 | 2021-05-10 | 199.99 | 300 |
| 13 | 2021-09-11 | 321.50 | 301 |
| 14 | 2021-06-12 | 789.67 | 303 |
| 15 | 2021-01-03 | 102.50 | 304 |
| 16 | 2021-04-11 | 850.25 | 305 |
| 17 | 2021-03-13 | 689.25 | 500 |
| 18 | 2021-02-11 | 31.50 | 301 |
| 19 | 2021-06-12 | 79.67 | 303 |
| 20 | 2021-08-03 | 102.50 | 304 |
| 21 | 2021-07-11 | 80.25 | 305 |
| 22 | 2021-12-13 | 669.25 | 500 |
+----+------------+--------+-------------+
11 rows in set (0.00 sec)
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> update orders set amount=200 where id=12;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> savepoint a;
Query OK, 0 rows affected (0.00 sec)
mysql> update orders set amount=300 where id=13;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> savepoint b;
Query OK, 0 rows affected (0.00 sec)
mysql> update orders set amount=4000 where id=14;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from amounts;
ERROR 1146 (42S02): Table 'ram.amounts' doesn't exist
mysql> select * from orders;
+----+------------+---------+-------------+
| id | order_date | amount | customer_id |
+----+------------+---------+-------------+
| 12 | 2021-05-10 | 200.00 | 300 |
| 13 | 2021-09-11 | 300.00 | 301 |
| 14 | 2021-06-12 | 4000.00 | 303 |
| 15 | 2021-01-03 | 102.50 | 304 |
| 16 | 2021-04-11 | 850.25 | 305 |
| 17 | 2021-03-13 | 689.25 | 500 |
| 18 | 2021-02-11 | 31.50 | 301 |
| 19 | 2021-06-12 | 79.67 | 303 |
| 20 | 2021-08-03 | 102.50 | 304 |
| 21 | 2021-07-11 | 80.25 | 305 |
| 22 | 2021-12-13 | 669.25 | 500 |
+----+------------+---------+-------------+
11 rows in set (0.00 sec)
mysql> rollback to b;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from orders;
+----+------------+--------+-------------+
| id | order_date | amount | customer_id |
+----+------------+--------+-------------+
| 12 | 2021-05-10 | 200.00 | 300 |
| 13 | 2021-09-11 | 300.00 | 301 |
| 14 | 2021-06-12 | 789.67 | 303 |
| 15 | 2021-01-03 | 102.50 | 304 |
| 16 | 2021-04-11 | 850.25 | 305 |
| 17 | 2021-03-13 | 689.25 | 500 |
| 18 | 2021-02-11 | 31.50 | 301 |
| 19 | 2021-06-12 | 79.67 | 303 |
| 20 | 2021-08-03 | 102.50 | 304 |
| 21 | 2021-07-11 | 80.25 | 305 |
| 22 | 2021-12-13 | 669.25 | 500 |
+----+------------+--------+-------------+
11 rows in set (0.03 sec)
mysql> update orders set amount=400 where id=14;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from orders;
+----+------------+--------+-------------+
| id | order_date | amount | customer_id |
+----+------------+--------+-------------+
| 12 | 2021-05-10 | 200.00 | 300 |
| 13 | 2021-09-11 | 300.00 | 301 |
| 14 | 2021-06-12 | 400.00 | 303 |
| 15 | 2021-01-03 | 102.50 | 304 |
| 16 | 2021-04-11 | 850.25 | 305 |
| 17 | 2021-03-13 | 689.25 | 500 |
| 18 | 2021-02-11 | 31.50 | 301 |
| 19 | 2021-06-12 | 79.67 | 303 |
| 20 | 2021-08-03 | 102.50 | 304 |
| 21 | 2021-07-11 | 80.25 | 305 |
| 22 | 2021-12-13 | 669.25 | 500 |
+----+------------+--------+-------------+
11 rows in set (0.00 sec)
mysql> rollback to a;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from orders;
+----+------------+--------+-------------+
| id | order_date | amount | customer_id |
+----+------------+--------+-------------+
| 12 | 2021-05-10 | 200.00 | 300 |
| 13 | 2021-09-11 | 321.50 | 301 |
| 14 | 2021-06-12 | 789.67 | 303 |
| 15 | 2021-01-03 | 102.50 | 304 |
| 16 | 2021-04-11 | 850.25 | 305 |
| 17 | 2021-03-13 | 689.25 | 500 |
| 18 | 2021-02-11 | 31.50 | 301 |
| 19 | 2021-06-12 | 79.67 | 303 |
| 20 | 2021-08-03 | 102.50 | 304 |
| 21 | 2021-07-11 | 80.25 | 305 |
| 22 | 2021-12-13 | 669.25 | 500 |
+----+------------+--------+-------------+
11 rows in set (0.00 sec)
mysql> rollback to b;
ERROR 1305 (42000): SAVEPOINT b does not exist
mysql>
No comments:
Post a Comment
Note: only a member of this blog may post a comment.