PDO::beginTransaction

    (PHP 5 >= 5.1.0, PHP 7, PHP 8, PECL pdo >= 0.1.0)

    PDO::beginTransaction — Startet eine Transaktion

    Beschreibung

    public PDO::beginTransaction(): bool

    Deaktiviert den Autocommit-Modus. Wenn der Autocommit-Modus deaktiviert ist, werden Änderungen, die über die Instanz eines PDO-Objekts an der Datenbank vorgenommen werden, erst dann bestätigt, wenn die Transaktion durch den Aufruf von PDO::commit() beendet wird. Wenn PDO::rollBack() aufgerufen wird, werden alle Änderungen an der Datenbank rückgängig gemacht und die Verbindung wird wieder in den Autocommit-Modus versetzt.

    Einige Datenbanken, darunter auch MySQL, führen automatisch ein implizites COMMIT durch, wenn innerhalb einer Transaktion eine DDL- (Database Definition Language) Anweisung wie DROP TABLE oder CREATE TABLE ausgeführt wird. Durch das implizite COMMIT wird verhindert, dass andere Änderungen, die in dieser Transaktion vorgenommen wurden, rückgängig gemacht werden können.

    Parameter-Liste

    Diese Funktion besitzt keine Parameter.

    Rückgabewerte

    Gibt bei Erfolg true zurück. Bei einem Fehler wird false zurückgegeben.

    Fehler/Exceptions

    Wenn bereits eine Transaktion gestartet wurde oder der Treiber keine Transaktionen unterstützt, wird eine PDOException ausgelöst.

    Hinweis: Eine Exception wird auch dann ausgelöst, wenn das Attribut PDO::ATTR_ERRMODE nicht PDO::ERRMODE_EXCEPTION ist.

    Beispiele

    Beispiel #1 Rückgängigmachen (Rollback) einer Transaktion

    Im folgenden Beispiel wird eine Transaktion gestartet und es werden zwei Anweisungen ausgeführt, die die Datenbank verändern, bevor die Änderungen rückgängig gemacht werden. Bei MySQL wird die Transaktion jedoch durch die Anweisung DROP TABLE automatisch bestätigt, sodass keine der Änderungen dieser Transaktion rückgängig gemacht wird.

    <?php
    /* Starten einer Transaktion, wobei Autocommit deaktiviert wird */
    $dbh->beginTransaction();

    /* Ändern des Datenbankschemas und der Daten */
    $sth = $dbh->exec("DROP TABLE fruit");
    $sth = $dbh->exec("UPDATE dessert
    SET name = 'hamburger'"    );

    /* Erkennen eines Fehlers und Zurücknehmen der Änderungen */
    $dbh->rollBack();

    /* Die Datenbankverbindung ist nun wieder im Autocommit-Modus */
    ?>

    Siehe auch

    Improve This Page

    Learn How To Improve This Page • Submit a Pull Request • Report a Bug
    ＋add a note

    User Contributed Notes 13 notes

    up
    56
    steve at fancyguy dot com
    9 years ago
    The nested transaction example here is great, but it's missing a key piece of the puzzle. Commits will commit everything, I only wanted commits to actually commit when the outermost commit has been completed. This can be done in InnoDB with savepoints.

    <?php

    class Database extends PDO
    {

    protected     $transactionCount = 0;

    public function     beginTransaction()
    {
    if (!    $this->transactionCounter++) {
    return     parent::beginTransaction();
    }
    $this->exec('SAVEPOINT trans'.$this->transactionCounter);
    return     $this->transactionCounter >= 0;
    }

    public function     commit()
    {
    if (!--    $this->transactionCounter) {
    return     parent::commit();
    }
    return     $this->transactionCounter >= 0;
    }

    public function     rollback()
    {
    if (--    $this->transactionCounter) {
    $this->exec('ROLLBACK TO trans'.$this->transactionCounter + 1);
    return     true;
    }
    return     parent::rollback();
    }

    }
    up
    30
    bitluni
    12 years ago
    You can generate problems with nested beginTransaction and commit calls.
    example:

    beginTransaction()
    do imprortant stuff
    call method
    beginTransaction()
    basic stuff 1
    basic stuff 2
    commit()
    do most important stuff
    commit()

    Won't work and is dangerous since you could close your transaction too early with the nested commit().

    There is no need to mess you code and pass like a bool which indicate if transaction is already running. You could just overload the beginTransaction() and commit() in your PDO wrapper like this:

    <?php
    class Database extends \\PDO
    {
    protected     $transactionCounter = 0;
    function     beginTransaction()
    {
    if(!    $this->transactionCounter++)
    return     parent::beginTransaction();
    return     $this->transactionCounter >= 0;
    }

    function     commit()
    {
    if(!--    $this->transactionCounter)
    return     parent::commit();
    return     $this->transactionCounter >= 0;
    }

    function     rollback()
    {
    if(    $this->transactionCounter >= 0)
    {
    $this->transactionCounter = 0;
    return     parent::rollback();
    }
    $this->transactionCounter = 0;
    return     false;
    }
    //...
    }
    ?>
    up
    16
    drm at melp dot nl
    16 years ago
    In response to "Anonymous / 20-Dec-2007 03:04"

    You could also extend the PDO class and hold a private flag to check if a transaction is already started.

    class MyPDO extends PDO {
    protected $hasActiveTransaction = false;

    function beginTransaction () {
    if ( $this->hasActiveTransaction ) {
    return false;
    } else {
    $this->hasActiveTransaction = parent::beginTransaction ();
    return $this->hasActiveTransaction;
    }
    }

    function commit () {
    parent::commit ();
    $this->hasActiveTransaction = false;
    }

    function rollback () {
    parent::rollback ();
    $this->hasActiveTransaction = false;
    }

    }
    up
    13
    kesler dot alwin at gmail dot com
    8 years ago
    please fix in answer #116669:

    $this->exec('ROLLBACK TO trans'.$this->transactionCounter + 1);

    with

    $this->exec('ROLLBACK TO trans'.($this->transactionCounter + 1));
    up
    12
    rjohnson at intepro dot us
    15 years ago
    If you are using PDO::SQLITE and need to support a high level of concurrency with locking, try preparing your statements prior to calling beginTransaction() and you may also need to call closeCursor() on SELECT statements to prevent the driver from thinking that there are open transactions.

    Here's an example (Windows, PHP version 5.2.8). We test this by opening 2 browser tabs to this script and running them at the same time. If we put the beginTransaction before the prepare, the second browser tab would hit the catch block and the commit would throw another PDOException indicating that transactions were still open.

    <?php
    $conn     = new PDO('sqlite:C:\path\to\file.sqlite');
    $stmt = $conn->prepare('INSERT INTO my_table(my_id, my_value) VALUES(?, ?)');
    $waiting = true; // Set a loop condition to test for
    while($waiting) {
    try {
    $conn->beginTransaction();
    for(    $i=0; $i < 10; $i++) {
    $stmt->bindValue(1, $i, PDO::PARAM_INT);
    $stmt->bindValue(2, 'TEST', PDO::PARAM_STR);
    $stmt->execute();
    sleep(1);
    }
    $conn->commit();
    $waiting = false;
    } catch(    PDOException $e) {
    if(    stripos($e->getMessage(), 'DATABASE IS LOCKED') !== false) {
    // This should be specific to SQLite, sleep for 0.25 seconds
    // and try again. We do have to commit the open transaction first though
    $conn->commit();
    usleep(250000);
    } else {
    $conn->rollBack();
    throw     $e;
    }
    }
    }

    ?>
    up
    4
    cristian at crishk dot com
    8 years ago
    OK I'm finding a solution for "NESTED" transactions in MySQL, and as you know in the MySQL documentation says that it's not possible to have transactions within transactions. I was trying to use the Database class propossed here in http://php.net/manual/en/pdo.begintransaction.php but unfortunately that's wrong for many things related to the control flow that I have been solved with the following code (LOOK THE EXAMPLE AT THE END, CarOwner)

    <?php

    class TransactionController extends \\PDO {
    public static     $warn_rollback_was_thrown = false;
    public static     $transaction_rollbacked = false;
    public function     __construct()
    {
    parent :: __construct( ... connection info ... );
    }
    public static     $nest = 0;
    public function     reset()
    {
    TransactionController :: $transaction_rollbacked = false;
    TransactionController :: $warn_rollback_was_thrown = false;
    TransactionController :: $nest = 0;
    }
    function     beginTransaction()
    {
    $result = null;
    if (    TransactionController :: $nest == 0) {
    $this->reset();
    $result = $this->beginTransaction();
    }
    TransactionController :: $nest++;
    return     $result;
    }

    public function     commit()
    {

    $result = null;

    if (    TransactionController :: $nest == 0 &&
    !    TransactionController :: $transaction_rollbacked &&
    !    TransactionController :: $warn_rollback_was_thrown) {
    $result = parent :: commit();
    }
    TransactionController :: $nest--;
    return     $result;
    }

    public function     rollback()
    {
    $result = null;
    if (    TransactionController :: $nest >= 0) {
    if (    TransactionController :: $nest == 0) {
    $result = parent :: rollback();
    TransactionController :: $transaction_rollbacked = true;
    }
    else {
    TransactionController :: $warn_rollback_was_thrown = true;
    }
    }
    TransactionController :: $nest--;
    return     $result;
    }

    public function     transactionFailed()
    {
    return     TransactionController :: $warn_rollback_was_thrown === true;
    }
    // to force rollback you can only do it from $nest = 0
    public function forceRollback()
    {
    if (    TransactionController :: $nest === 0) {
    throws new \PDOException();
    }
    }
    }

    ?>
    up
    9
    ludwig dot green at gmail dot com
    14 years ago
    be aware that you also can not use TRUNCATE TABLE as this statement will trigger a commit just like CREATE TABLE or DROP TABLE

    it is best to only use SELECT, UPDATE and DELETE within a transaction, all other statements may cause commits thus breaking the atomicity of your transactions and their ability to rollback

    obviously you can use DELETE FROM <table> instead of TRUNCATE TABLE but be aware that there are differences between both statements, for example TRUNCATE resets the auto_increment value while DELETE does not.
    up
    3
    Sbastien
    3 years ago
    A way to use transaction and prepared statement to speed-up bulk INSERTs :

    <?php

    // ...

    $insert = $pdo->prepare('INSERT INTO table (c1, c2, c3) VALUES (?, ?, ?)');
    $bulk = 3_000; // To adjust according to your data/system
    $rows = 0;

    $pdo->beginTransaction();
    while (    $entry = fgetcsv($fp)) {
    $insert->execute($entry);
    if (++    $rows % $bulk === 0) {
    $pdo->commit();
    $pdo->beginTransaction();
    }
    }
    if (    $pdo->inTransaction()) { // Remaining rows insertion
    $pdo->commit();
    }
    up
    4
    dbeecher at tekops dot com
    16 years ago
    // If you need to set an ISOLATION level or LOCK MODE it needs to be done BEFORE you make the BeginTransaction() call...
    //
    // **note** you should always check result codes on operations and do error handling. This sample code
    // assumes all the calls work so that the order of operations is accurate and easy to see
    //
    // THIS IS using the PECL PDO::INFORMIX module, running on fedora core 6, php 5.2.4
    //
    // This is the correct way to address an informix -243 error (could not position within table) when there
    // is no ISAM error indicating a table corruption. A -243 can happen (if the table/indexes, etc., are ok)
    // if a row is locked. The code below sets the LOCK MODE to wait 2 minutes (120 seconds) before
    // giving up. In this example you get READ COMMITTED rows, if you don't need read committed
    // but just need to get whatever data is there (ignoring locked rows, etc.) instead of
    // "SET LOCK MODE TO WAIT 120" you could "SET ISOLATION TO DIRTY READ".
    //
    // In informix you *must* manage how you do reads because it is very easy to trigger a
    // lock table overflow (which downs the instance) if you have lots of rows, are using joins
    // and have many updates happening.
    //

    // e.g.,

    $sql= "SELECT FIRST 50 * FROM mytable WHERE mystuff=1 ORDER BY myid"; /* define SQL query */

    try /* create an exception handler */
    {
    $dbh = new PDO("informix:host=......");

    if ($dbh) /* did we connect? */
    {
    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $dbh->query("SET LOCK MODE TO WAIT 120")

    # ----------------
    # open transaction cursor
    # ----------------
    if ( $dbh->beginTransaction() ) # explicitly open cursor
    {
    try /* open exception handler */
    {
    $stmt = $dbh->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL));

    $stmt->execute();

    while ($row = $stmt->fetch(PDO::FETCH_NUM, PDO::FETCH_ORI_NEXT))
    {
    $data = $row[0] . "\t" . $row[1] . "\t" . $row[2] . "\t" . $row[3] . "\t" . $row[4] . "\t" . $row[5] . "\t" . $row[6] . "\t" . $row[7] . "\n" . $row[8] ;
    //print $data;
    print_r($row);
    };

    $stmt = null;
    }
    catch (PDOException $e)
    {
    print "Query Failed!\n\n";

    print "DBA FAIL:" . $e->getMessage();
    };

    $dbh->rollback(); # abort any changes (ie. $dbh->commit()
    $dbh = null; # close connection
    }
    else
    {
    # we should never get here, it should go to the exception handler
    print "Unable to establish connection...\n\n";
    };
    };
    }
    catch (Exception $e)
    {
    $dbh->rollback();
    echo "Failed: " . $e->getMessage();
    };
    up
    2
    geompse at gmail dot com
    14 years ago
    With Oracle, any structure statement will do an implicit commit.

    So : ALTER TABLE "my_table" DROP COLUMN "my_column";
    Can't be rolled back !

    Hope this will save time for others
    up
    1
    eddi13
    9 years ago
    after TRUNCATE TABLE `table` just as DELETE FROM `table`, so if whole table was deleted, aborts the transaction. And the rollback will not be passible.
    up
    1
    Steel Brain
    9 years ago
    The example is misleading, Typically data definition language clauses (DDL) will trigger the database engine to automatically commit. It means that if you drop a table, that query will be executed regardless of the transaction.
    Ref-Mysql:
    http://dev.mysql.com/doc/refman/5.0/en/implicit-commit.html
    up
    -3
    arth dot inbox at mail dot ru
    10 years ago
    Strange behavior with pdo_firebird driver:

    <?php
    $dbh     = new PDO ('','','',array( PDO :: ATTR_PERSISTENT=>true));
    $dbh->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION );
    $dbh -> exec ( "select 1;" ); //should be autocommitted
    try
    {
    //$dbh->commit/rollback here fixes the issue;
    $dbh -> beginTransaction (); // <- fails "with there is already an active transaction"
    $dbh -> exec ( "select 1;" );
    $dbh -> commit ();
    }
    catch (     Exception $e )
    {
    $dbh -> rollBack ();
    }
    ?>
    ＋add a note
    To Top