I’ve got a transaction to insert an order and related tables (customer, order_product etc) which, on error, rolls back. Fine.
However I’m also using the database to log details about orders and errors. This process itself is using a transaction (it’s a 2-part process of insert into log entry, and optionally insert into a log detail table). So when the logging function runs COMMIT, the entire order insert process is committed. Even if the log entry wasn’t running COMMIT, the log entries would be rolled back in the event of an error, which I don’t want.
Any suggestions of how to get around this? The only one I can think of is to have the log tables as MYISAM, so that they use auto commit, and not using any transaction logic in the logging process.
Perhaps you could log anything first and only then start processing the order? Not sure if this is a viable option for your situation though.
I personally wouldn’t go for you MyISAM option. It might sound tempting, but what if someone else comes along and changes the engine to InnoDB, not knowing your specific reason for using MyISAM, then your whole idea is screwed.
Maybe you could use a completely different database for logging? I personally like MongoDB a lot for logging, since you can just pass it any datastructure you want; it doesn’t have traditional tables. So you can just put any arbitrary structure in there, which is handy for logging, because sometimes you want to add a user_id, sometimes you want to an order_id, sometimes you want to add something else, etc.
Well, I didn’t want to put any constraints on where you’re allowed to log things - it seemed like a pretty arbitrary rule to force on other devs and it was sure to go wrong.
In the end I changed the application code to store up the log items, and write to DB at the end of execution. Seems to be working so far.