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.
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.
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.
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.
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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With