PHP + MySQL: How much programming can or should I include within a db transaction?
How much PHP code can / should I include within a single MySQL transaction? What is the best practice for transactions in terms of calling other classes & methods, manipulating or creating instances, etc, within a single transaction?
What I mean here by "include" is enter between beginTransaction(); and commit();
I am used to use one transaction for one element and all its dependencies.
For example, I have a forum where users can change their nickname. But they also can remove their account and I do not want to see posts removed. So I need to save somewhere in each post the name of my user in a VARCHAR or such.
If a user changes his nickname, I will begin a transaction, changes his nickname everywhere it could be and if there were no errors, so I commit. I do not look at the number of requests at this point, because if one request fails I should be able to rollback everything regarding this change.
But in a case that an admin changes 2 nicknames in an admin panel and validate once, I will open 2 transactions (one per user) to avoid having troubles with a single transaction.