When should I use transactions in my queries?

Question

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

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

Today I'm working on a page with various INSERT statements for a relational database, and I wanted 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 lost (apart from coder errors) so I'm always worried about when I should use them.

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

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

thanks

Brianbriana

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.

Kazan

thanks for help, I've also found some tecnique to work with transactions here kennynet.co.uk/2008/12/02/php-pdo-nested-transactions – Brianbriana

"Look here for some excellent answers" — actually there are several really bad answers, though high-voted. – Marc

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.

Stomodaeum

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

Seasick

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.

Discover

Answer 5

In some frameworks, e.g. Spring, automatic transactions allow to re-execute a transaction if it failed.

Arching

Answer 6

Mostly use for multiple same curd cases, where you want save changes for valid entries, for a multi steps form with multiple input for each step of those form s submit cases, and form submit after getting an external api feedback.. mainly when you want rollback your query for some return value.

Reddish

What happens if two users try to submit the same form? Won't you get a locking error? – Antherozoid

Answer 7

When fiddling with a sensitive database manually, it could be also a good idea to use a transaction as a safety / validation step to validate that the changes you made to a table were correct and as intended. This could save you the trouble of saving bad data by mistake.

Example:

I update table A and set field x to a wrong value unintentionally overriding the previous correct value.

If I were inside a transaction I would be able to select my row and check my change, spot the error and rollback. If my row looked correct then I would commit

If I were not inside a transaction, I would have lost the correct value.

Rosol

Isn't it easier to verify the data before update? – Hormone

Of course it is and you should, I am talking about "unintentionally" setting a bad value, have you never made a mistake? That's what I mean, you can rollback a mistake. Nobody makes a mistake on purpose. – Rosol

look, it is not about "mistakes". It's about where you check for them. In your scenario, you set the wrong value, select it back and rollback. It just makes no sense when you can simply check the value and don't insert at all. – Hormone

Agree, your opinion holds true for simple operations and for really careful experienced users however some scenarios can be more subtle to detect e.g accidentally omitting/forgetting the where clause, I am talking about noob/beginner mistakes. – Rosol

Now I got you. This is not for the code, where we are usually implementing transactions, but for the situation when you are tasked to do a one time change on a live database. – Hormone