How often do you use transactions?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
JellyFish
DevNet Resident
Posts: 1361
Joined: Tue Feb 14, 2006 7:18 pm
Location: San Diego, CA

How often do you use transactions?

Post by JellyFish »

I just recently discovered MySQL transactions, and thought to MySelf: "How could I have not known about this? How often do developers use this?". So now I ask you, how often do you take advantage of the transaction feature in MySQL? Would you say it's a must for multiple queries that should all execute successfully?

Now that I've asked that, I have some other questions regarding transactions. If you're interfacing with the MySQL via PHP, would the following script be the way to go about using transactions?

Code: Select all

 
mysql_query("START TRANSACTION");
mysql_query("...");
...
mysql_query("COMMIT");
 
Would all the queries between the "START TRANSACTION" query and the "COMMIT" query be apart of the transaction?
User avatar
Darhazer
DevNet Resident
Posts: 1011
Joined: Thu May 14, 2009 3:00 pm
Location: HellCity, Bulgaria

Re: How often do you use transactions?

Post by Darhazer »

Use transaction whenever you need a transaction. Keep in mind that MySQL does not support nested transaction, i.e. starting a transaction will commit the previous one. Queries that alter table/database structure will commit transaction too. Locking a table can commit transaction too. Also, transactions are not supported by all engines. InnoDB supports transactions, MyISAM does not.

Having in mind that nested transaction are not supported, useful feature is the savepoint and rollback to savepoint.

executing the mysql_query() statements like shown will create and execute a transaction.
User avatar
JellyFish
DevNet Resident
Posts: 1361
Joined: Tue Feb 14, 2006 7:18 pm
Location: San Diego, CA

Re: How often do you use transactions?

Post by JellyFish »

Thank you for your reply, Darhazer. When would I need to use a transaction? I would think that I'd need to use a transaction to make sure all my queries get executed successfully with integrity. But then what would I do in the case where I'm using a MyISAM table? I guess a good solution is to never use a MyISAM table. But I do use one MyISAM table for the FULLTEXT search feature. I wonder if there's another way for MyISAM tables that has the same effect as transactions do.
Post Reply