PHP 5.5.16 is released

PDO::beginTransaction

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

PDO::beginTransaction Démarre une transaction

Description

public bool PDO::beginTransaction ( void )

PDO::beginTransaction() désactive le mode autocommit. Lorsque l'autocommit est désactivé, les modifications faites sur la base de données via les instances des objets PDO ne sont pas appliquées tant que vous ne mettez pas fin à la transaction en appelant la fonction PDO::commit(). L'appel de PDO::rollBack() annulera toutes les modifications faites à la base de données et remettra la connexion en mode autocommit.

Quelques bases de données, dont MySQL, exécuteront automatiquement un COMMIT lorsqu'une requête de définition de langage de base de données (DDL) comme DROP TABLE ou CREATE TABLE est exécutée dans une transaction. Ce COMMIT implicite vous empêchera d'annuler toutes autres modifications faites dans cette transaction.

Valeurs de retour

Cette fonction retourne TRUE en cas de succès ou FALSE si une erreur survient.

Exemples

Exemple #1 Annule une transaction

L'exemple suivant commence une transaction et exécute deux requêtes qui modifient la base de données avant d'annuler les modifications. Sur MySQL, cependant, la requête DROP TABLE validera automatiquement la transaction, donc, aucune des modifications de la transaction ne sera annulée.

<?php
/* Démarre une transaction, désactivation de l'auto-commit */
$dbh->beginTransaction();

/* Modification du schéma de la base ainsi que des données */
$sth $dbh->exec("DROP TABLE fruit");
$sth $dbh->exec("UPDATE dessert
SET name = 'hamburger'"
);

/* On s'aperçoit d'une erreur et on annule les modifications */
$dbh->rollBack();

/* Le connexion à la base de données est maintenant de retour en mode auto-commit */
?>

Voir aussi

add a note add a note

User Contributed Notes 9 notes

up
13
bitluni
2 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
3
ludwig dot green at gmail dot com
4 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
1
rjohnson at intepro dot us
5 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
2
drm at melp dot nl
6 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
1
Anonymous
6 years ago
beginTransaction will through a PDOException if you execute it while a PDO transaction is already active.  Additionally the PDO engine doesn't seem to provide any way of determining if there is a transaction "in flight" so if you might be calling a function from within another function that starts a transaction you'll have to wrap the beginTransaction () call in a try .. catch block.
up
1
arth dot inbox at mail dot ru
4 months 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 ();
}
?>
up
1
geompse at gmail dot com
4 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
0
dbeecher at tekops dot com
6 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
-1
mtjo62 at gmail dot com
9 months ago
For the PDO driver for the  Firebird server, you have to explicitly disable autocommit to initiate the  transaction as well as explicitly enable autocommit to commit it.

$dbh->setAttribute( PDO::ATTR_AUTOCOMMIT, 0 );
$dbh->beginTransaction();

/** Query block */

$dbh->commit();
$dbh->setAttribute( PDO::ATTR_AUTOCOMMIT, 1 );
To Top