Write a MySQL statement for rollback commit and save points

Like other databases, MySQL also provides facility for rollback commit and save points. In this article, we will explain these in details with examples.

MySQL Rollback

The MySQL ROLLBACK command is used to restore the database to the last committed state. Like, suppose we deleted some records from MySQL database and later realised that we have deleted necessary record, then we can easily rollback this by using ROLLBACK command. This is effective only when the session has not expired and we have not committed the statement.

Syntax of MySQL ROLLBACK

ROLLBACK [To SAVEPOINT_NAME];

Here, SAVEPOINT_NAME is the name given to save point that we rollback.

MySQL Commit

The MySQL COMMIT command is used to save the transaction permanently. As without session expires, we can rollback any changes made by INSERT, UPDATE or DELETE command. As they are not permanent, so with the help of COMMIT command we can make any changes permanently.

Syntax of MySQL COMMIT

COMMIT;

The COMMIT statement is written after the MySQL statement, that we want to be permanent in the database. It returns 'Commit complete.' on successfully committed.

MySQL Save points

The MySQL SAVEPOINTS command is used to save the transaction temporarily, so that we can ROLLBACK this to that point when required.

Syntax of MySQL COMMIT

SAVEPOINT SAVEPOINT_NAME;

Here, SAVEPOINT_NAME is the name given to save point.

Example of MySQL ROLLBACK, COMMIT and SAVEPOINT

Here, we have created an item_order table -

mysql> CREATE TABLE item_order(item_id INT, name CHAR (20), INDEX (item_id));

Next, do a transaction with AUTOCOMMIT turned on -

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.07 sec)

Next, insert data in 'item_order' -

mysql> INSERT INTO item_order VALUES (101, 'Milk');
Query OK, 1 row affected (0.04 sec)

Next, commit this statement, if you want to insert this permanently -

mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)

Let's make the autocommit off -

mysql> SET autocommit=0;

And, check the ROLLBACK functionality -

mysql> INSERT INTO item_order VALUES (102, 'Biscuit');
Query OK, 1 row affected (0.00 sec)
mysql>INSERT INTO item_order VALUES (103, 'Cake');
Query OK, 1 row affected (0.00 sec)

Let's delete one row and call the ROLLBACK statement-

mysql> SAVEPOINT C;
Query OK, 0 rows affected (0.00 sec)
mysql> DELETE FROM item_order WHERE name = 'Biscuit';
Query OK, 1 row affected (0.04 sec)

ROLLBACK TO C;

After ROLLBACK statement call, the records in the database are shown as -

mysql> SELECT * FROM item_order;
+---------+------+
| item_id | name |
+---------+------+
|     101 | Milk |
|     103 | Cake |
+---------+------+

Related MySQL Exercises

MySQL concatenate
MySQL Get Current Date, User, Version
MySQL Get Maximum Value
MySQL Administrator
MySQL where clause
MySQL order by
MySQL get difference between two dates
MySQL Pattern Matching
MySQL Join
MySQL Regular Expression
MySQL delete duplicate row
MySQL update multiple rows
Get nth highest salary using MySQL
Display the nth row from MySQL
MySQL SELECT top 5
MySQL display the alternate rows