Tuesday, 12 October 2021

SAVEPOINT and ROLLBACK sql videos in telugu 80

 https://youtu.be/5cnaBFM_y7M

---------------------------------------------------------
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>

Auto Commit on ddl statements sql videos in telugu 79

 https://youtu.be/tEgye8yoz0w

Transactions and auto Commit statement sql videos in telugu 78

 https://youtu.be/UHO5K0KHERI

Commit statement sql videos in telugu 77

 https://youtu.be/cyD76Jijt_0

Sql commands categorys sql videos in telugu 76

 https://youtu.be/AT41OB5y6kQ

Python with mysql delete,update records sql videos in telugu 73

 https://youtu.be/4A2ICXnyiH4

Import and export tables in command line sql videos in telugu 75

 https://youtu.be/KqulmawNGdA

Alter table sql videos in telugu 74

 https://youtu.be/ic1B0QseeK0