SitePoint Sponsor

User Tag List

Results 1 to 10 of 10

Thread: pdo->rollback()

  1. #1
    SitePoint Addict Banana Man's Avatar
    Join Date
    Dec 2005
    Posts
    391
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    pdo->rollback()

    Hi,

    I'm using pdo->rollback() for the first time. If i understand it correctly, after i begin a pdo transaction i should check the returned boolean of execute() and if it is successful run pdo->commit(), else call pdo->rollback(). In the below example i want to run pdo->execute() and then pdo->lastInsertId(). The way i have it currently setup what would happen if the pdo->execute() ran ok but the pdo->lastInsertId() had an error. I presume with this setup the pdo->commit() is going to run anyway? How would i set this up to check that the pso->execute and pdo->lastInsertId() both ran without errors before commiting and also rollback both of these if any error happens?

    Code:
            $this->pdo->beginTransaction();
    
            // prepare the pdo query
    	$stmt = $this->pdo->prepare($sql);
    				
    	// execute the query with its parameters
    	if ($stmt->execute($params)) {	
    	        // get the row id of the first query performed.  This will be added as the foreign key of all child tables
    	        $first_row_id = $this->pdo->lastInsertId();
    
                   // commit all the queries to the database
                  $this->pdo->commit();  
           } else {
                 $stmt->pdo->rollback();
           }
    Thanks,
    Ronan

  2. #2
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,223
    Mentioned
    153 Post(s)
    Tagged
    0 Thread(s)
    What error did you get, as I checked the manual, you are doing it right.
    http://php.net/manual/en/pdo.lastinsertid.php

    Granted, I would use a try/catch instead of checking the result of execute(). This way when your lastInsertId fails you can also rollback.

  3. #3
    SitePoint Addict Banana Man's Avatar
    Join Date
    Dec 2005
    Posts
    391
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Ah yes, try/catch makes more sense.

    Thanks!

    Code:
    try {
            $this->pdo->beginTransaction();
    
            // prepare the pdo query
    	$stmt = $this->pdo->prepare($sql);
    				
    	// execute the query with its parameters
    	$stmt->execute($params);	
    	        
           // get the row id of the first query performed.  This will be added as the foreign key of all child tables
           $lastInsertId = $this->pdo->lastInsertId();
    
           // commit all the queries to the database
           $this->pdo->commit();  
     } catch (PDOException $e) {
           $stmt->pdo->rollback();
           print $e;
    }

  4. #4
    SitePoint Addict Banana Man's Avatar
    Join Date
    Dec 2005
    Posts
    391
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    If the catch Exception is called before getting to the pdo->commit() is a rollback performed automatically even if you dont call pdo->rollback() inside the catch? I have read different answers on this. Is the rollback performed automatically but its better coding practice to call it anyway? I have tested it myself and it seems to be running the pdo->rollback() if i call it or not.

    Thanks!

  5. #5
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,223
    Mentioned
    153 Post(s)
    Tagged
    0 Thread(s)
    From: http://www.php.net/manual/en/pdo.transactions.php (emphasis is mine)
    When the script ends or when a connection is about to be closed, if you have an outstanding transaction, PDO will automatically roll it back. This is a safety measure to help avoid inconsistency in the cases where the script terminates unexpectedly--if you didn't explicitly commit the transaction, then it is assumed that something went awry, so the rollback is performed for the safety of your data.
    So only if your script terminates without explicitly calling rollback or commit, the transaction will be rolled back. Now think about that carefully. As the following scenario could (I haven't tested this) produce unwanted results:
    PHP Code:
    try {
            
    $this->pdo->beginTransaction();

            
    // prepare the pdo query
        
    $stmt $this->pdo->prepare($sql);
                    
        
    // execute the query with its parameters
        
    $stmt->execute($params);    
                
           
    // get the row id of the first query performed.  This will be added as the foreign key of all child tables
           
    $lastInsertId $this->pdo->lastInsertId();

           
    // commit all the queries to the database
           
    $this->pdo->commit();  
     } catch (
    PDOException $e) {
            print 
    $e;
    }
    try {
            
    $this->pdo->beginTransaction();

            
    // prepare the pdo query
        
    $stmt $this->pdo->prepare($sql);
                    
        
    // execute the query with its parameters
        
    $stmt->execute($params);    
                
           
    // get the row id of the first query performed.  This will be added as the foreign key of all child tables
           
    $lastInsertId $this->pdo->lastInsertId();

           
    // commit all the queries to the database
           
    $this->pdo->commit();  
     } catch (
    PDOException $e) {
           
    $stmt->pdo->rollback();
           print 
    $e;

    Assume the first try/catch fails at lastInsertId, so it hits the catch but doesn't rollback. Now we start the next try/catch and that query succeeds and successfully calls commit. What will happen to your first failed transaction?

    Or better yet
    PHP Code:
    try {
            
    $this->pdo->beginTransaction();

            
    // prepare the pdo query
        
    $stmt $this->pdo->prepare($sql);
                    
        
    // execute the query with its parameters
        
    $stmt->execute($params);    
                
           
    // get the row id of the first query performed.  This will be added as the foreign key of all child tables
           
    $lastInsertId $this->pdo->lastInsertId();

           
    // commit all the queries to the database
           
    $this->pdo->commit();  
     } catch (
    PDOException $e) {
            print 
    $e;
    }
    try {
            
    // prepare the pdo query
        
    $stmt $this->pdo->prepare($sql);
                    
        
    // execute the query with its parameters
        
    $stmt->execute($params);    
                
           
    // get the row id of the first query performed.  This will be added as the foreign key of all child tables
           
    $lastInsertId $this->pdo->lastInsertId();

     } catch (
    PDOException $e) {
           print 
    $e;

    For the first query you run into, a transaction is desired and necessary, but the second query doesn't need it (as we really don't care if a value is inserted or not). What happens if the first query fails on lastInsertId and the second query succeeds but we are now at the end of the script? Both will likely rollback.

    Therefore it is of my own opinion, it is better to explicitly call it, then allow it to be automatically called at the termination of the script.

  6. #6
    SitePoint Enthusiast adear11's Avatar
    Join Date
    Oct 2003
    Location
    Richland MS
    Posts
    56
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    For the first bit, I may be wrong about this don't think I am though, if you call PDO::beginTransaction() while another transaction is active - hasn't been committed or rolled back- an exception will be thrown.

    On the whole though, you are quite right. You don't need to be relying on PHP/PDO to cleanup or rollback your transactions. The only way to properly ensure data integrity is to implicitly call rollback() or commit() as needed.

  7. #7
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,223
    Mentioned
    153 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by adear11 View Post
    For the first bit, I may be wrong about this don't think I am though, if you call PDO::beginTransaction() while another transaction is active - hasn't been committed or rolled back- an exception will be thrown.
    Yeah, I was thinking that too when I wrote it, which is why I added the second set of code which wouldn't do that and would still perform unwanted results. Glad I wasn't the only person who was thinking that (nested transactions just sound dangerous).

    However, it seems it may allow it... http://php.net/manual/en/pdo.begintransaction.php (see first comment) yikes!

  8. #8
    SitePoint Enthusiast adear11's Avatar
    Join Date
    Oct 2003
    Location
    Richland MS
    Posts
    56
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by cpradio View Post
    Yeah, I was thinking that too when I wrote it, which is why I added the second set of code which wouldn't do that and would still perform unwanted results. Glad I wasn't the only person who was thinking that (nested transactions just sound dangerous).

    However, it seems it may allow it... http://php.net/manual/en/pdo.begintransaction.php (see first comment) yikes!
    There is another comment on that same page that says an exception will be thrown.

    I just did a quick test:

    Code:
    <?php
    require 'htdocs/content/classes/MyDB.class.php';
    
    $db = MyDB::getInstance();
    
    $db->beginTransaction();
    
    $db->beginTransaction();
    This throws the exception:

    Code:
    Fatal error: Uncaught exception 'PDOException' with message 'There is already an active transaction'

  9. #9
    SitePoint Addict Banana Man's Avatar
    Join Date
    Dec 2005
    Posts
    391
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Ok, thanks for the help with this!

  10. #10
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,223
    Mentioned
    153 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by adear11 View Post
    There is another comment on that same page that says an exception will be thrown.

    I just did a quick test:

    Code:
    <?php
    require 'htdocs/content/classes/MyDB.class.php';
    
    $db = MyDB::getInstance();
    
    $db->beginTransaction();
    
    $db->beginTransaction();
    This throws the exception:

    Code:
    Fatal error: Uncaught exception 'PDOException' with message 'There is already an active transaction'
    Ah, I missed that one


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •