CODEWITHSUNDEEP
phpmysqlpdotransactions
Nate
Nate

Reputation: 28434

PDO transaction functions vs MySQL transaction statements?

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.

Upvotes: 8

Views: 948

Answers (2)

Air
Air

Reputation: 8615

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.

Upvotes: 10

Stijn Bernards
Stijn Bernards

Reputation: 1091

The difference between the PDO and mysql transaction is nothing. EXCEPT

You can for example start your transaction, make some querys run some php code do more querys based on your code and such, and you could rollback at the end of that code simply execute $PDO->rollback(); this is way easier than creating 2 - 3 more querys instead of using $pdo->beginTransaction();

Also using $pdo->rollback(); is a few lines shorter and in my opinion it's also clearer than creating another query and executing it.

Upvotes: 2

Related Questions