When should I use transactions in my queries?

mysql transactions pdo data-transfer
vitto picture vitto · Jan 29, 2010 · Viewed 15.7k times · Source

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

Answer

Nick Craver picture Nick Craver · Jan 29, 2010

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.

Related questions

How do detect that transaction has already been started?

I am using Zend_Db to insert some data inside a transaction. My function starts a transaction and then calls another method that also attempts to start a transaction and of course fails(I am using MySQL5). So, the question …

Read More
How to debug Lock wait timeout exceeded on MySQL?

In my production error logs I occasionally see: SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded; try restarting transaction I know which query is trying to access the database at that moment but is there a way to find out …

Read More
Fixing "Lock wait timeout exceeded; try restarting transaction" for a 'stuck" Mysql table?

From a script I sent a query like this thousands of times to my local database: update some_table set some_column = some_value I forgot to add the where part, so the same column was set to the same …

Read More