Link to home
Create AccountLog in
Avatar of Vampireofdarkness
VampireofdarknessFlag for United Kingdom of Great Britain and Northern Ireland

asked on

SQL Transactions (via PHP / mySQL)

"Simple" question, which is probably personal choice. Do you use SQL transactions, or just implement your own redundancies?

Use: I am redesigning a complete CRM/CMS solution which includes DB rework (myISAM to innoDB, foreign keys, etc...). Normally queries are run by themselves, but occasionally there are a group run at once (maybe 5% of the time). Even if an error occurred we probably wouldn't need to rollback, just alert the user what error and how to resolve.
Avatar of wolfgang_93
wolfgang_93
Flag of Canada image

With InnoDB, you have no choice if an SQL error occurs during a transaction -- the
unit of work is rolled back.

If you want more control, then the application has to keep track. For example,
introduce a new field in crucial tables that is perhaps flagged with a lastchanged
date/time whenever a change is applied.

This lets the application check for and warn a user about to apply a change,
for example, that the record was in the meantime changed by someone
else.

Avatar of Vampireofdarkness

ASKER

I was not aware that innoDB rolled back in the event of an error without prompting. Are you able to cite a source for more reading please?

I tried a quick Google of "innodb transaction rollback" and could not see anything for this, only that the rollback statement exists. Note that I'm not doubting it, I just haven't seen it documented.
ASKER CERTIFIED SOLUTION
Avatar of wolfgang_93
wolfgang_93
Flag of Canada image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account