how can we use commit, rollback and savepoint in mysql ?
The specified SQL savepoint also remains active after the ROLLBACK TO statement has been executed. This means the ROLLBACK TO statement can be executed in the same transaction more than once by specifying the same SQL savepoint name.
COMMIT − to save the changes. ROLLBACK − to roll back the changes. SAVEPOINT − creates points within the groups of transactions in which to ROLLBACK.
You can only roll back to the most recently marked savepoint. An implicit savepoint is marked before executing an INSERT , UPDATE , or DELETE statement. If the statement fails, a rollback to the implicit savepoint is done.
MySQL InnoDB provides support for the statements SAVEPOINT, ROLLBACK TO SAVEPOINT, RELEASE SAVEPOINT. The SAVEPOINT statement is used to set a save point for the transaction with the specified name.
CREATE TABLE t_test (id INT NOT NULL PRIMARY KEY) ENGINE=InnoDB;
START TRANSACTION;
INSERT
INTO t_test
VALUES (1);
SELECT *
FROM t_test;
id
---
1
SAVEPOINT tran2;
INSERT
INTO t_test
VALUES (2);
SELECT *
FROM t_test;
id
---
1
2
ROLLBACK TO tran2;
SELECT *
FROM t_test;
id
---
1
ROLLBACK;
SELECT *
FROM t_test;
id
---
mysql> start transaction;
mysql> savepoint id;
Here you alter the table data and then:
mysql> rollback to savepoint id;
View the data and finally:
mysql> release savepoint id;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With