What differences do I have to take into account if using MySQL Transactions?

Question

I was recently told that I should use transactions in my application as they can help run code quicker and prevent MySQL code from executing before a whole page has loaded (e.g. if someone is just refreshing on my page and the whole page doesn't load, no MySQL calls will start and it reduces load on the server.)

I'm wondering if I wanted to switch my site to transactions, how difficult would this be, and what changes would I have to make?

Also, I'm using PDO as opposed to mysql_*, if that would make any difference?

Answer 1

Transactions, in web applications, are generally used for Atomicity and for Isolation.

They don't "prevent MySQL code from executing before a whole page has loaded" : MySQL queries are still executed when you call mysql_query (or any equivalent using PDO or mysqli_*). But they will all be "cancelled" if you do not commit the transaction.

They also don't have that much to do with helping "run code quicker" ; even if they can sometimes have that effect, it's only because if you don't explicitly use a transaction for a group of queries (resulting in only one transaction), MySQL will implicitly use one transaction for each query (resulting in several transactions, which might put some additionnal load on the DB server)

Notes :

• of course, this is using a DB engine that supports transactions -- like InnoDB.
• for the "quicker" point, I've seen it a couple of times ; but it was in special situations, where I needed speed more than ACID -- or maybe it was just a matter of load on the server (which means if you want to go down that path, you have to profile, to know what's faster in your specific case ! )

Using PDO, mysql_*, or mysqli_* shouldn't make much of a difference, here -- even if it's recommended not to use mysql_* (old, and doesn't support some features of MySQL >= 4.1, such as prepared statements).

Answer 2

Transactions provide ACID, not make your "code quicker".

If anything, they'll make it slower. I suggest you do more research on transactions before jumping to such a conclusion.

Answer 3

Actually, you're using transactions already when you use an RDBMS. However, unless you're explicit about when transactions start and finish, you're implicitly allowing each SQL statement to start and finish a separate transaction. This is also called "auto-commit."

Transactions are a good tool for making logical sets of changes by multiple SQL statements, so you can commit or rollback the whole set of changes as a unit, and ensure your changes leave the database in a consistent state. Transactions are not primarily intended to improve performance.

There's a small overhead to start and finish any transaction. This is magnified if you use implicit transactions for high-volume changes, incurring the overhead for every statement. For instance this becomes significant if you run thousands of INSERT statements to perform a bulk data load, each INSERT implicitly in a separate transaction. In other cases, the performance benefit of using transactions versus not using transactions is trivial, especially in a language like PHP that has a "share-nothing" architecture.

Answer 4

I'm not aware of the transactional speed of MySQL but in SQLite it's generally faster to use transactions, I believe however that in MySQL they should slow you down.

Regarding the changes you need to make, well... Almost none:

BEGIN TRANSACTION
$result = [execution of several queries]
if $result fails -> ROLLBACK, else COMMIT

I also suggest you use PDO over mysqli or mysql.