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)