Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Real-world uses of MySQL savepoints in web services?

Does anyone have experience they can share using MySQL savepoints (directly or via an ORM), especially in a non-trivial web service? Where have you actually used them? Are they reliable enough (assuming you're willing to run a fairly recent version of MySQL) or too bleeding-edge or expensive?

Lastly, does anyone have experience with something like the following use case and did you use savepoints for it? Say the main point of some specific unit of work is to add a row to an Orders table (or whatever, doesn't have to be order-related, of course) and update an OrdersAuditInfo table, in the same transaction. It is essential that Orders be updated if at all possible, but OrdersAuditInfo table is not as essential (e.g., it's ok to just log an error to a file, but keep going with the overall transaction). At a low level it might look like this (warning, pseudo-SQL follows):

BEGIN;

INSERT INTO Orders(...) VALUES (...);
/* Do stuff outside of SQL here; if there are problems, do a
 ROLLBACK and report an error (i.e., Order is invalid in this
 case anyway). */

SAVEPOINT InsertAudit;
INSERT INTO OrdersAudit(...) VALUES(...);
/* If the INSERT fails, log an error to a log file somewhere and do: */
ROLLBACK TO SAVEPOINT InsertAudit;

/* Always want to commit the INSERT INTO Orders: */
COMMIT;

But even here perhaps there'd be a better (or at least more common) idiom? One could do the OrdersAuditInfo insert in a completely different transaction but it would be nice to be guaranteed that the OrdersAuditInfo table were not written to unless the final COMMIT actually worked.

like image 401
Jacob Gabrielson Avatar asked Feb 26 '09 18:02

Jacob Gabrielson


People also ask

What is the use of savepoint in MySQL?

The SAVEPOINT in MySQL is used for dividing (or) breaking a transaction into multiple units so that the user has a chance of roll backing the transaction up to a specified point. That means using Save Point we can roll back a part of a transaction instead of the entire transaction.

What is the use of savepoint statement?

The SAVEPOINT statement names and marks the current point in the processing of a transaction. With the ROLLBACK TO statement, savepoints undo parts of a transaction instead of the whole transaction.

Is it possible to delete a savepoint in MySQL?

Once you delete a save point you cannot commit or rollback to it. If the specified savepoint does not exist an error will be generated. If you invoke a COMMIT or ROLLBACK statement without specifying the name of the savepoint all the savepoints of the current transaction are deleted.

What is Save Point create a savepoint query?

The SAVEPOINT statement is used to set a save point for the transaction with the specified name. If a save point with the given name already exists the old one will be deleted.


1 Answers

I generally tend to avoid SAVEPOINTs, as it can make code quite hard to understand and verify.

In the case you posted, wrapping in a single transaction will depend on whether having OrdersAudit records exactly corresponding with Orders, is part of your business rules.

EDIT: Just re-read your question, and you do not have a requirement for guaranteed correspondence between OrdersAudit and Orders. So I wouldn't use any transaction for the insertion of the OrdersAudit records.

like image 95
Mitch Wheat Avatar answered Sep 20 '22 17:09

Mitch Wheat