0

Suppose my server PHP end point follows something along the lines of the following:

  1. Client sends request to server.
  2. Server updates a record
  3. Server might do some PHP processing...
  4. Server updates record again

What to do if the server crashes whilst performing either of the SQL commands (either 2 or 4) or during 3.

Is it just a case that I need to code defensively, making sure that I'm aware of the consequences of each possible result of a crash at critical code areas, i.e. to perform a sort of crash analysis to determine the worst outcomes, and make sure my work flow is able to deal with each case.

Or is there some other mechanism I can use to help with such cases?

  • 1
    What's your transactional model and AUTO_COMMIT settings? – πάντα ῥεῖ Jun 5 '18 at 17:01
  • @πάνταῥεῖ Okay, I'm using MyISAM, with no reference to auto commit, so I guess at the moment my SQL commands are executing consecutively. Also, I don't think MyISAM supports the transactional model, so I may need to switch to InnoDB ? Thanks for the hint. – Antinous Jun 5 '18 at 17:23
  • This is a solved problem en.wikipedia.org/wiki/Database_transaction – esoterik Jun 5 '18 at 23:17
  • Yup, I know that now after doing a bit of research due to the hint. Wasn't obvious before. Learning lots. SQL is really powerful. – Antinous Jun 6 '18 at 6:30
2

Given the hint in the comments I was able to discern the right course of action. Switching from the MyISAM SQL database engine to the InnoDB database engine, I can use transactions to safe guard against introducing errors into my database:

START TRANSACTION
INSERT, UPDATE, ETC...
COMMIT

The InnoDB engine will not make any changes until the commit command is reached, so if anything should go wrong before that line then the database will not be affected.

Furthermore, according to the excellent W3Resources introduction on Transactions,

MySQL (here we maintain version 5.6) supports local transactions (within a given client session)

so it would seem that a single PHP SQL connection supports the transaction model, presumably with the ability to perform intermittent PHP processing if necessary between SQL queries, and indeed I confirmed this last night.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.