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.

How to do it...

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 balance=@a.bal-100 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...