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