Using savepoints, you can roll back to certain points in the transaction without terminating the transaction. You can use SAVEPOINT identifier
to set a name for the transaction and use the ROLLBACK TO identifier
statement to roll back a transaction to the named savepoint without terminating the transaction.
Suppose A
wants to transfer to multiple accounts; even if a transfer to one account fails, the others should not be rolled back:
mysql> BEGIN; Query OK, 0 rows affected (0.00 sec) mysql> SELECT balance INTO @a.bal FROM account WHERE account_number='A'; Query OK, 1 row affected (0.01 sec) mysql> UPDATE account SET [email protected] WHERE account_number='A'; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> UPDATE account SET balance=balance+100 WHERE account_number='B'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> SAVEPOINT transfer_to_b; Query OK, 0 rows affected (0...