Re: mysql_ping before transaction commit

[Rick James]

The transaction will be rolled back, and any UPDATEs/INSERTs will be undone.

Your "transaction" lasts minutes? Maybe even hours? I think it would be better to avoid transactions altogether. Instead, each time there is something to update, connect, update, and disconnect.

Note: When two processes are in transactions, it is possible for them to decide to lock the same record. In this case, one transaction will wait until the other finishes before continuing.

Worse than that... Suppose you have two connections, A and B, and the do these things in this order:
1. A BEGIN
2. B BEGIN
3. A acquire a lock on record 123
4. B acquire a lock on record 987
5. A tries to acquire a lock on record 987, but is told to wait
6. B tries to acquire a lock on record 123 --
We now have a deadlock! InnoDB will pick one of the transactions to ROLLBACK. The other will then proceed to finish.

Such nasties encourage you (or at least, me) to make each transaction as fast/short as possible. Or avoid transactions. Or be able to recover from an unexpected error.