Real-world uses of MySQL savepoints in web services?

Question

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.

Answer 1

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.