While with most MySQL variables, if you have an idea what it does or what it’s for, but don’t remember the exact name, you can make an educated guess, this one always gets me in a trouble. I do know what it does, and yet, when I need the name, there is no way I can ever find it on the list of MySQL options, and it’s not even good for googling, as the description one can give is too generic (try to google for “transaction commit rollback” and see what happens).
And although I remember it now, I can forget in the future, so here are the values:
NO_CHAIN (or 0)
COMMIT and ROLLBACK are unaffected. This is the default value.
CHAIN (or 1)
COMMIT and ROLLBACK are equivalent to COMMIT AND CHAIN and ROLLBACK AND CHAIN, respectively. (A new transaction starts immediately with the same isolation level as the just-terminated transaction.)
RELEASE (or 2)
COMMIT and ROLLBACK are equivalent to COMMIT RELEASE and ROLLBACK RELEASE, respectively. (The server disconnects after terminating the transaction.)
And this is important, too, as I tend to forget and waste a lot of time trying to figure out why “nothing works”:
completion_type affects transactions that begin with START TRANSACTION or BEGIN and end with COMMIT or ROLLBACK. It does not apply to implicit commits [...] It also does not apply for XA COMMIT, XA ROLLBACK, or when autocommit=1.
Btw, the latter is not exactly true, or I don’t know what they meant by that. Surely it works when autocommit=1, otherwise why would we even need BEGIN or START TRANSACTION?..