Using savepoints

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:

BEGIN;
SELECT balance INTO @a.bal FROM account WHERE account_number='A';
UPDATE account SET balance=@a.bal-100 WHERE account_number='A';
UPDATE account SET balance=balance+100 WHERE account_number='B';

SAVEPOINT transfer_to_b;

Query OK, 0 rows affected (0.00 sec)

SELECT balance INTO @a.bal FROM account WHERE account_number='A';
UPDATE account SET balance=balance+100 WHERE account_number='C';

Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

Since there are no rows updated, meaning there is no account with C, you can rollback the transaction to SAVEPOINT where transfer to B is successful. Then A will get back 100 which was deducted to transfer to C. If you wish not to use the save point, you should do these in two transactions.

ROLLBACK TO transfer_to_b;
COMMIT;

SELECT balance FROM account WHERE account_number='A';

+---------+
| balance |
+---------+
|     400 |
+---------+
1 row in set (0.00 sec)

SELECT balance FROM account WHERE account_number='B';

+---------+
| balance |
+---------+
|     600 |
+---------+
1 row in set (0.00 sec)