When should I use transactions in my queries?

Question

Hi, I'm reading on web, very detailed tutorials on how to use transactions with database types and database engines, but I haven't found some guide that teach me when and why I should use them.

I know transactions are usually used for home bankings, so when we work with money data, but I can immagine they are used in many other ways.

Today I'm working on a page with various INSERT statements for a relational database, and I wonder to know if this is one of the cases when I should use them.

I get an impression that I don't know the cases when the data can be partially be lost (coder errors apart) so I'm always worried about know when i should use them.

Can someone explain or give some link with these foundamental rules?

I'm using MySQL 5.0.8, should I use InnoDB for all tables need transactions? If yes, is InnoDB slower than the common MyISAM but I shouldn't worry about that?

thanks

Answer 1

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.

Look here for some excellent answers and their reasons for using them.

Answer 2

In addition to what Nick Craver wrote, you would want to use a transaction when you have a series of writes that need to be performed atomically; that is, they should all succeed or none should succeed.

Answer 3

Transactions should be used when there is the possibility that either failure to complete or someone else reading or writing in the middle of your task could cause damage to the data. These include but are not limited to:

• Reading from a table for subsequent deletion
• Writing related data to multiple tables

Answer 4

You use transactions when you have a group of actions that must be atomic (either all succeed or none succeed) Wrapping these actions in a transaction allows you to rollback actions that have already succeeded when you encounter an error. It also ensures that the data you are working with is consistent as locks will be held until the transaction is complete.