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).

Answer 2

You use transactions whenever you need a group of related queries to be executed as if it was only 1 query.

For example:

In a shopping cart someone ads a product, you update the store inventory (decrease existences), and updates the customer's cart (Increase products on cart). In this case 2 operations take place, and that's why you create a transaction, if one of the queries fails (insuficient products on the store) the second query should not happen (add product to customer's cart). In this case, the 2 queries conform a unit of work, meaning that the database should process both or none.

Now, taking into consideration your points, I don't see a solid reason for using transactions to execute unrelated queries (unless there is a special need). Depending on the database engine you are using, and the system you are developing unnecessary transactions will create a performance bottleneck.

On the other hand, there may be some applications where you may need to perform multiple transactions, but that is something you should decide on a case by case as there is no general rule.

Regarding your concern of limiting the amount of transactions per page, or create only one transaction per page I can only say that this depends on what you are trying to acomplish. You may may need to perform 10 related queries wich should be rolled back together upon failure then you need one transaction. If you need to run many unrelated queries with no need to roll back means you don't even need a transaction.

You can also check wikipedia's entry about this subject:

http://en.wikipedia.org/wiki/Database_transaction