I was reading official PHP documentation few days ago. While searching around for some information I found something really surprising. To be specific I found an example of using PDO::commmit() which was quite funny for me. Few years ago I was wondering during code review where some (really bad) ideas come from. Now I finally understand because when we can see this really "useful" example in PHP official guide, so at least I don't need to wonder anymore. I took the original code and changed it slightly to prepare the example for newcomers in this area for better understanding what's going on and why this example is bad. Original code:


<?php
/* Begin a transaction, turning off autocommit */
$dbh->beginTransaction();

/* Change the database schema */
$sth = $dbh->exec("DROP TABLE fruit");

/* Commit the changes */
$dbh->commit();

/* Database connection is now back in autocommit mode */
?>

New code:


<?php

try {
  $dbh = new PDO("oci:dbname=//nnn.nnn.nnn.nnn:1521/test", "usr", "pwd");

  // Check contents of "fruit" table

  echo "First check: ";

  $sth = $dbh->prepare("SELECT id FROM fruit");
  $sth->execute();
  $err = $sth->errorInfo();
  echo "<font color=\"red\">".$err[2]."";

  while($row = $sth->fetch()) {
       echo $row["ID"]."";
  }

  // ==== BEGIN OF ORIGINAL CODE ====

  /* Begin a transaction, turning off autocommit */
  $dbh->beginTransaction();

  /* Change the database schema */
  $sth = $dbh->exec("DROP TABLE fruit");

  /* Replaced Commit to Rollback */
  $dbh->rollback();

  /* Database connection is now back in autocommit mode */

  // ==== END OF THE ORIGINAL CODE ====

  // check contents of "fruit" table again
  echo "Second check: ";

  $sth = $dbh->prepare("SELECT id FROM fruit");
  $sth->execute();
  $err = $sth->errorInfo();
  echo "<font color=\"red\">".$err[2]."";

  while($row = $sth->fetch()) {
       echo $row["ID"] . "";
  }
} catch(PDOException $e) {
       echo $e->getMessage();
}

?>

So we have a table called namaed "FRUIT" which contains ten rows (increasing value from 1 to 10). The script (as shown above) selects and shows all rows from table "FRUIT" then executes the original code and then selects all rows from "FRUIT" table again. There is just one difference in original code. I changed COMMIT to ROLLBACK for better understanding of uselessnes of the Original code shown in example on PHP.net documentation.


First check:

1
2
3
4
5
6
7
8
9
10
Second check:
OCIStmtExecute: ORA-00942: table or view does not exist(...)

So as we can see we did do DROP TABLE and even we rollbacked the transaction the table remains dropped. Yes, this is pretty correct behaviour as DROP TABLE is DDL statement and you can only rollback (or commit) DML statement (INSERT, UPDATE, DELETE, MERGE). So if ROLLBACK doesn't work thus it means we don't need to use COMMIT at all. The second point is that DDL statement almost always does implicit commit before and after (I used the "almost" word because this is not true for all DDL statements for example for "ALTER SESSION" statement). So no matter we do before DDL it will be commited, so in case of possible rollback DML executed before DDL within a transaction will be not rollbacked. There are couple of things or statements which could be used as example but they used the worst ever and created totally useless example.