php - When should I use MySQL transactions?

Question

I read some articles about when I should use transactions. I read:

When should I use transactions? Basically any time you have a unit of work that is either sensitive to outside changes or needs the ability to rollback every change, if an error occurs or some other reason.

But can someone explain it better to me?

• Should I start a transaction when I execute two or more delete/update/insert queries?
• Should I also start a transaction when I just have one delete/update/insert query?
• Should I start a transaction like 10 times on a page, or better only once for the whole page, or do you recommend a max for each page (for example 5)?

Thanks for your help!

Answer 1

Transactions are used when you have a group of queries that all depend on one another.

For example, a bank:

• Bank customer "John" transfers $100 to the account of "Alice".
• For this example, there are 2 queries (I'm not showing logging or transaction history...etc). You need to deduct $100 from John's balance and add that to Alice's balance.
• Start transaction
• Deduct from John
  • UPDATE accounts SET balance=balance-100 WHERE account='John'
• Add to Alice
  • UPDATE accounts SET balance=balance+100 WHERE account='Alice'
• commit

A transaction isn't saved until you commit it. So if there was an error in either query, you could call rollback and undo any queries that have run since the transaction was started. If for some reason the query for adding $100 to Alice failed, you could rollback and not deduct $100 from John. It is a way of ensuring that you can undo queries automatically if needed.

---

• Should I start a transaction when I execute two or more delete/update/insert queries?

  Depends on what the queries are doing.

• Should I also start a transaction when I just have one delete/update/insert query?

  Not necessary unless you needed a way to rollback (undo) the query like you want to do an update and validate it before calling commit (save).

• Should I start a transaction like 10 times on a page, or better only once for the whole page, or do you recommend a max for each page (for example 5)?

  Start as many as you need. I would doubt that you have multiple transactions per page as you would most likely be doing one thing on each page load (i.e. transferring money).