Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How can we use nested transactions in MySQL?
We can work with nested transactions in MySQL with the help of SAVEPOINT.
Firstly, create a table. After that, begin the transaction.
Now, insert records in the table created above. Use SAVEPOINT statement to set a named transaction savepoint with a name of identifier.
Here are all the steps shown in the form of query −
Create table
mysql> create table NestedTransactionDemo -> ( -> Name varchar(200) -> ); Query OK, 0 rows affected (0.63 sec)
Start the transaction −
mysql> START TRANSACTION; Query OK, 0 rows affected (0.00 sec)
Now, insert a record in the table
mysql> insert into NestedTransactionDemo values('John');
Query OK, 1 row affected (0.04 sec)Display the record added above −
mysql> select *from NestedTransactionDemo; +------+ | Name | +------+ | John | +------+ 1 row in set (0.00 sec)
Let us begin with working on transactions to create nested transactions −
mysql> savepoint transaction2;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into NestedTransactionDemo values('David');
Query OK, 1 row affected (0.00 sec)
mysql> select *from NestedTransactionDemo;
+-------+
| Name |
+-------+
| John |
| David |
+-------+
2 rows in set (0.00 sec)
mysql> rollback to transaction2;
Query OK, 0 rows affected (0.00 sec)
mysql> select *from NestedTransactionDemo;
+------+
| Name |
+------+
| John |
+------+
1 row in set (0.00 sec)
mysql> rollback to transaction2;
Query OK, 0 rows affected (0.00 sec)
Updated on: 30-Jul-2019
1K+ Views
Advertisements