Syntax error How can we use nested transactions in MySQL?

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: 2019-07-30T22:30:24+05:30

2K+ Views

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements