PDO transaction functions vs MySQL transaction statements?

Question

PDO provides functions to initiate, commit, and roll back transactions:

$dbh->beginTransaction();
$sth = $dbh->prepare('
    ...
');
$sth->execute(); // in real code some values will be bound
$dbh->commit();

Is there any reason to use the PDO functions over simply using the transaction statements in MySQL? I.e:

$sth = $dbh->prepare('
    START TRANSACTION;
    ...
    COMMIT;
');
$sth->execute(); // in real code some values will be bound

UPDATE: Just a note to anyone else looking into this, after some testing I actually found the second case above (using START TRANSACTION and COMMIT in prepare()) will result in an exception being thrown. So in order to use transactions with a prepared statement, you must use the PDO functions shown in the first case.

Answer 1

From a portability standpoint, you're better off using the interface that PDO provides in case you ever want to work with a different DBMS, or bring on another team member who's used to another DBMS.

For example, SQLite uses a slightly different syntax; if you were to move to an SQLite database from MySQL, you would have to change every string in your PHP code that contains the statement START TRANSACTION; because it would no longer be valid syntax for your database. SQL Server 2014 is another example that doesn't use this syntax.

Of course, you can also use BEGIN; in MySQL to start a transaction, and that would work fine in SQLite and SQL Server. You could just use that instead.

You'll often be able to find a syntax that you like and that is reasonably portable but why spend the time and energy to even think about it if you don't have to? Take advantage of the fact that there are a dozen PDO drivers available to make your life easier. If you care at all about consistency, favor the API over implementation-specific SQL syntax.