PHP mysqli::autocommit VS "START TRANSACTION"

Question

In our database layer object, we have always managed transactions with "START TRANSACTION", "ROLLBACK" and "COMMIT" SQL statements executed via mysqli::query.

Doing some research today, I discovered this mention in the MySQL Manual regarding using API level calls to manage a transaction VS using straight SQL:

Important

Many APIs used for writing MySQL client applications (such as JDBC) provide their own methods for starting transactions that can (and sometimes should) be used instead of sending a START TRANSACTION statement from the client. See Chapter 20, Connectors and APIs, or the documentation for your API, for more information.

And, upon looking closer at mysqli, found the mysqli::autocommit, mysqli::rollback and mysqli::commit methods for managing a transaction (http://us.php.net/manual/en/class.mysqli.php).

My question: is it better to use these mysqli equivalents and why? I can find no mention anywhere that says if or why these functions perform better than their straight SQL counterparts.

Answer 1

Well, appearantly these methods and your queries are interchangeable. Maybe the methods are faster for they can send the request more quicker to the server, without a query having to be parsed?

Answer 2

It's possible there might be reasons why a data access wrapper would need to know when a transaction was started/ended. For example off the top of my head, a connection-pooling scheme would have to know when a previously-used connection was finished with in the middle of a transaction, and not return it to the connection pool for re-use in that case.

I'm not aware of any issues in mysqli that would require you to use the native method rather than the SQL version, but it's possibly in the general case... that's why it's only “sometimes should”. In any case, the PHP method is easier to read than the SQL-string version, so I'd say go with that anyway.