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 believe you are using a savepoint to avoid failed INSERTs to rollback the whole transaction. But in that case, the best way to do so is to use INSERT IGNORE. If it fails you will get a warning instead of an error, and nothing will rollback.

Since you don't need a rollback, I suggest you don't use a transaction.

SAVEPOINTs are great if you may want to rollback some successful statements (but not all statements). For example:

1. START TRANSACTION;
2. Relatively slow query that you don't want to run often
3. INSERT INTO a
4. SAVEPOINT one;
5. SELECT id INTO @id FROM slot WHERE status = 'free' ORDER BY timestamp LIMIT 1;
6. INSERT INTO b (slot_id, ...) VALUES (@id, ...)
7. INSERT INTO c (slot_id, ...) VALUES (@id, ...)
8. INSERT INTO d (slot_id, ...) VALUES (@id, ...)
9. COMMIT;

If the SELECT returns nothing, you may run a global ROLLBACK. If any of the following INSERTs fails, you may ROLLBACK TO SAVEPOINT one and pick another id. Obviously, in a case like this, you want to implement a maximum number of attempts in your code.

Answer 2

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.