MySQL How to properly use transactions?

Question

I encounter in my project a need to implement transactions - thing that I never did before. I already checked that my aytocommit is set to 1 - and I'm not sure if I nneed to touch it at all?

Right now I have set of scripts that all does include function that connnects to database first. There is a perfect place to put mysqli_begin_transaction($link);, and mysqli_autocommit($link, FALSE);, so I'd have transactions everywhere regardless if specific script does need it, or not, and turn off autocommit - the documentation on php.net if very poor there but AFAIR I should do this. So my question no. 1 & 2 would be: Is it fine to start transaction everywhere regardless if script does need it or not? And should i disable autocommit like this as well?

Now let's say, that I have such script (sorry for not providing actual code, but my question is about how transaction works, not about code itself):

~insert and/or update things
~do something aka "line 2"
~insert and/or update things again 

Seems like example taken right from the book. I obviously want all inserts and updates, or none to happen. Since I already started transaction, I assume, that nothing will commit, unless I call mysqli_commit ($link);. But here we have a little problem: I do not include any 'footer' at the end of my scripts and doing so seems like a nightmare now, so I don't have any place to put commit. So question no. 3 is: Will my queries commit automaticly after script ends (or I call exit; or die();) even if i set autocommit to false? Or do I need to call commit/do not turn off autocommit?

Now comes time for case when something fails and I need to rollback. Same as above - do I need to call mysqli_rollback (mysqli $link);, or pure fact that I did not call commit will be sufficient? I'm refering here to an situation where script does not end normally. Situations like power off server while working on "line 2", or was stopped because it took to much time (set_time_limit stopped it).

Answer 1

This is a somewhat broad question, so I'll try to cover all the things as much as I can.

At first you can ignore the mysqli api (the api specific transaction functions are just wrappers), and go straight to the MySQL manual. The important thing here is that disabling autocommit and starting a transaction are the same thing. Also a single query (including modifications by triggers) is always a transaction.

The answer you question 1 & 2 is "probably not". It very much depends on what your existing code assumes about the database connection, and how your application is structured.

From what you mentioned in the question, the answer would be: it will be better if you only put transactions in the places that need them.

For question 3: it will not commit automatically. You can however make it do so, by using register_shutdown_function, although I don't recommend doing that.

There are statements (implicit commits) which will commit the transaction automatically. These include all DDL statements (CREATE,ALTER...) and also TRUCNATE, LOCK TABLES and others. This basically means those statements can't be used in transactions.

MySQL rolls back transactions when the connection is terminated.

I would recommend to add transactions only to the code which needs them (to be safe you can do this for all code which does more than one write query to the db).

The classic approach is:

START TRANSACTION
query
other things
another query
some other stuff
3-rd query
...
COMMIT

The main thing here is to make sure you only commit if no errors have occurred.

Leave the rollback to either connection termination (or register_shutdown_function if you are using persistent connections), because making sure each and every script will have a correctly working rollback logic is hard :)

This will make sure that nothing is committed if bad things happen (exceptions, fatal errors, time/mem limits, power outages, meteors...).

It is also possible to have transactions at a function/method level (nested and stack-like), but thats out of the scope for this question.