MySQL Stored Procedure Programming

Name

SQL-03: Avoid use of savepoints—they can obscure program logic and reduce program efficiency

Synopsis

Savepoints allow you to define a point within a transaction to which you can roll back without losing all of the changes made by the transaction. In essence, a savepoint facilitates the “partial rollback” of a transaction.

Indiscriminate use of savepoints can lead to inefficient and hard-to-maintain code. This is because when you roll back to a savepoint, your program flow is harder to follow, and you have almost by definition wasted system resources by issuing DML that you later aborted.

Quite often, you will find that instead of rolling back to a savepoint, you can simply issue a SELECT statement to validate an operation prior to actually issuing the DML. This technique was demonstrated in Chapter 8.

A valid use of a savepoint is within a stored program that you are using to execute a “nested” transaction without affecting the status of a transaction that may be in progress in the calling program. The “nested” program creates a savepoint and rolls back to that savepoint if any errors occur. In this way the procedure could be safely called by a program that has an open transaction, since any rollback issued in the nested program would affect only statements issued in that program.