Real possibilities of using MySQL savepoints in web services?

Question

Does anyone have experience that they can share savepoints with MySQL (directly or through ORM), especially in a non-trivial web service? Where did you use them? Are they reliable enough (assuming you are ready to run a fairly recent version of MySQL) or are too bleeding or expensive?

Finally, does anyone have experience with something like the following use case and have you used savepoints for it? Let's say the main point of a particular unit of work is to add a row to the Orders table (or something else, not necessarily related to order), and update the OrdersAuditInfo table in the same transaction. It is very important that Orders updated if at all possible, but the OrdersAuditInfo table OrdersAuditInfo not so significant (for example, just register an error in the file, but continue working with the general 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 (ie, 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, would there possibly be a better (or at least more general) idiom? You could insert the OrdersAuditInfo tab into a completely different transaction, but it would be nice to ensure that the OrdersAuditInfo table was not written until the final COMMIT executed.

Answer 1

I usually try to avoid SAVEPOINT, as this can make the code quite difficult to understand and verify.

In the event that you send, the wrapper in one transaction will depend on whether the availability of OrdersAudit records that exactly correspond to Orders are part of your business rules.

EDIT: Just re-read your question, and you have no requirement for a guaranteed fit between OrdersAudit and Orders . Therefore, I would not use a transaction to insert OrdersAudit records.