Rollback or Commit with PDO transaction using php and mysql

A transaction should end with either a rollback() or a commit(), (only one of them).
In case you are using MySQL, make sure you are not using MyISAM engine for tables, as it doesn't support transactions. It usually support InnoDB.
Some databases, including MySQL, automatically issue an implicit COMMIT when a database definition language (DDL) statement such as DROP TABLE or CREATE TABLE is issued within a transaction. The implicit COMMIT will prevent you from rolling back any other changes within the transaction boundary.

$dbh = null;
try {
    $dbh = new PDO("mysql:host=HOST_NAME;dbname=DB_NAME", "DB_USER_NAME", "DB_USER_PASSWORD");

    /*** Set the PDO error mode to exception (will throw exception if any error occurred) ***/
    /*** Follow the link for more info: http://php.net/manual/en/pdo.setattribute.php ***/
    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $dbh->setAttribute(PDO::ATTR_ORACLE_NULLS, PDO::NULL_EMPTY_STRING);

    /*** Creating a test table if not exists ***/
    $table = "CREATE TABLE IF NOT EXISTS test_transaction ( " .
        "id MEDIUMINT(8) NOT NULL AUTO_INCREMENT PRIMARY KEY, ".
        "name VARCHAR(25) NOT NULL, designation VARCHAR(25) NULL )";
    $dbh->exec($table);

    /*** Transaction block starting here ***/
    $dbh->beginTransaction();

    /*** Inserting some data ***/
    $dbh->exec("INSERT INTO test_transaction VALUES(NULL, 'Pritom #1', '')");
    $dbh->exec("INSERT INTO test_transaction VALUES(NULL, 'Pritom #2', '')");

    /*** The following query result 2 outputs ***/
    $result = $dbh->query("SELECT * FROM test_transaction");
    echo "<pre>";
    print_r($result->fetchAll());
    echo "</pre>";

    /*** Again inserting some more data ***/
    $dbh->exec("INSERT INTO test_transaction VALUES(NULL, 'Pritom #1', 'Designation #1')");
    $dbh->exec("INSERT INTO test_transaction VALUES(NULL, 'Pritom #2', 'Designation #2')");
    $dbh->exec("INSERT INTO test_transaction VALUES(NULL, 'Pritom #3', 'Designation #3')");

    /*** The following query result 5 outputs ***/
    $result = $dbh->query("SELECT * FROM test_transaction");
    echo "<pre>";
    print_r($result->fetchAll());
    echo "</pre>";

    /*** Throwing an exception to test if transaction really works ***/
    throw new Exception("All data will be erased during this transaction");
}
catch(Exception $ex) {
    $dbh->rollback();

    /*** The following query result 0 outputs as transaction failed ***/
    $result = $dbh->query("SELECT * FROM test_transaction");
    echo "<pre>";
    print_r($result->fetchAll());
    echo "</pre>";

    echo "Exception: ".$ex->getMessage();
    die();
}

Output be as follows:

Array
(
    [0] => Array
        (
            [id] => 29
            [0] => 29
            [name] => Pritom #1
            [1] => Pritom #1
            [designation] => 
            [2] => 
        )

    [1] => Array
        (
            [id] => 30
            [0] => 30
            [name] => Pritom #2
            [1] => Pritom #2
            [designation] => 
            [2] => 
        )

)

Array
(
    [0] => Array
        (
            [id] => 29
            [0] => 29
            [name] => Pritom #1
            [1] => Pritom #1
            [designation] => 
            [2] => 
        )

    [1] => Array
        (
            [id] => 30
            [0] => 30
            [name] => Pritom #2
            [1] => Pritom #2
            [designation] => 
            [2] => 
        )

    [2] => Array
        (
            [id] => 31
            [0] => 31
            [name] => Pritom #1
            [1] => Pritom #1
            [designation] => Designation #1
            [2] => Designation #1
        )

    [3] => Array
        (
            [id] => 32
            [0] => 32
            [name] => Pritom #2
            [1] => Pritom #2
            [designation] => Designation #2
            [2] => Designation #2
        )

    [4] => Array
        (
            [id] => 33
            [0] => 33
            [name] => Pritom #3
            [1] => Pritom #3
            [designation] => Designation #3
            [2] => Designation #3
        )

)

Array
(
)

Exception: All data will be erased during this transaction