PHP PDO - There is no active transaction

Question

I am having problem with transactions in php script. I would like to make multiply queries and be able to recall them all, if at least one of them fails. Below you can find a simple example of the script I am using:

$tags_input = array(6,4,5);
$conn = new PDO('mysql:host='.DB_HOST.';dbname='.DB_NAME.';charset=utf8',  
DB_USER, DB_PASSW, array(  
    PDO::ATTR_EMULATE_PREPARES => false,  
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'utf8'"));

    $conn->beginTransaction();

    $sql = "INSERT INTO projects (id, pr_id, enabled) VALUES ( :val0, :val1, :val2)";
    $stmt = $conn->prepare($sql);  
    if(count($tags_input)>0){
            for($i = 0;$i<count($tags_input);$i++){
                    $stmt->bindValue(':val0', 57); 
                    $stmt->bindValue(':val1', $tags_input[$i]); 
                    $stmt->bindValue(':val2', 'Y'); 
                    $result = $stmt->execute();

            }

    }

    $res1 = $conn->commit();
    $conn->rollBack();

Now, this example generates an error:

Uncaught exception 'PDOException' with message 'There is no active transaction'

If I erase the line $conn->rollBack();, the error disappears. Therefore I cannot understand, why pdo object can't see open transaction (begintransaction and commit do not generate any errors). I also tried putting rollBack() inside the transaction, but made no difference. I was still getting an error 'There is no active transaction'.

I am running PHP 5.6 and Mysql tables on InnoDB.

Sequential

You are commiting the transaction here: ` $res1 = $conn->commit();` so you can't rollback – Telemotor

try wrapping your transaction code to a try-catch statement as in the 1st example in php.net/manual/en/pdo.transactions.php – Lukey

phpdelusions.net/pdo#transactions – Nidifugous

@Telemotor Thanks for input. Does it mean that once you commit transaction it cannot be rollback? In that case I am not sure I see the sens in rollbacks as if I got an error i just don't commit transaction.. – Sequential

As a side note: you do not need to start/stop the transaction at all when the number of inserts to execute is either 0 (no sql statement to execute) or 1 (a single statement to execute). In high-concurrency situations, this might help a tiny bit – Maffei

Answer 1

Wrap your transaction code inside a try-catch statement.

//try {
$tags_input = array(6,4,5);
$conn = new PDO('mysql:host='.DB_HOST.';dbname='.DB_NAME.';charset=utf8',  
DB_USER, DB_PASSW, array(  
    PDO::ATTR_EMULATE_PREPARES => false,  
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'utf8'"));
} catch (Exception $e) {
  die("Unable to connect: " . $e->getMessage());
}    
try {  
    $conn->beginTransaction();   
    $sql = "INSERT INTO projects (id, pr_id, enabled) VALUES ( :val0, :val1, :val2)";
    $stmt = $conn->prepare($sql);  
    if(count($tags_input)>0){
            for($i = 0;$i<count($tags_input);$i++){
                    $stmt->bindValue(':val0', 57); 
                    $stmt->bindValue(':val1', $tags_input[$i]); 
                    $stmt->bindValue(':val2', 'Y'); 
                    $result = $stmt->execute();
            }
    }
$res1 = $conn->commit();    
} catch (Exception $e) {
  $conn->rollBack();
  echo "Failed: " . $e->getMessage();
}

EDIT

A really well-based and straight-forward explanation of the answer was provided by Richard as a comment.

The reason you got error is because you were trying to close a transaction when it was already closed. beginTransaction opens one, and EITHER rollBack OR commit closes it. You have to avoid doing BOTH actions, meaning commit/rollback, for a single beginTransaction statement, or you'll get an error. The above try/catch code ensures that only one closing statement is executed.

Lukey

Hi, Thank you for quick answer. It seems to work! However, I am not sure why. Is it impossible to use transations without try/catch block? In addition, I think rollback here is irrelevant, as I tried to take it out of the code and also did not get any new records in database (which was the idea, but not sure why it is happening) – Sequential

It is for the same reason as @Telemotor wrote in a comment. I am glad it solved your problem merry Christmas :) – Lukey

Just to add to this, the reason you got errors is because you were trying to CLOSE a transaction when it was already closed. "beginTransaction" opens one, and EITHER "rollBack" OR "commit" closes it. You have to avoid doing BOTH a commit/rollback for a single "beginTransaction" statement, or you'll get an error. The above try/catch code ensures ONLY 1 "closing" statement is executed. – Miele

@RichardDuerr thank you,very nice and quick explanation, i have added it in the edit above. – Lukey

Answer 2

Peter and Richards answers are already correct, but there is one little mistake in the code from the transaction structure (and i can't add a comment).

The $connection->beginTransaction() must be outside of the try-catch block. When you're start the beginTransaction() in the try-block and your Database Operations throws an exception, the catch-block doesn't know something from an active transaction. So, you get the same error:

"There is no active transaction".

So the structure should be as well:

1. Get the Connection.
2. Start the Transaction with $connection->beginTransaction()
3. Open the try-catch block.

The try-block contains the $connection->commit() after DB Operations.

The catch-block contains the $connection->rollback() before a throw Exception.

So your code should look like this:

$tags_input = array(6,4,5);
$conn = new PDO('mysql:host='.DB_HOST.';dbname='.DB_NAME.';charset=utf8',  
DB_USER, DB_PASSW, array(  
    PDO::ATTR_EMULATE_PREPARES => false,  
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'utf8'"));
} catch (Exception $e) {
  die("Unable to connect: " . $e->getMessage());
}    
//Begin Transaction
$conn->beginTransaction();   
try {  
    $sql = "INSERT INTO projects (id, pr_id, enabled) VALUES ( :val0, :val1, :val2)";
    $stmt = $conn->prepare($sql);  
    if(count($tags_input)>0){
            for($i = 0;$i<count($tags_input);$i++){
                    $stmt->bindValue(':val0', 57); 
                    $stmt->bindValue(':val1', $tags_input[$i]); 
                    $stmt->bindValue(':val2', 'Y'); 
                    $result = $stmt->execute();
            }
    }
$res1 = $conn->commit();    
} catch (Exception $e) {
  $conn->rollBack();
  echo "Failed: " . $e->getMessage();
}

Starcrossed

Answer 3

As a refinement to the above both valid answers, it is worth pointing out that if you are catching a "broad" exception such as Exception, then it could well be that a transaction has not begun and the exception was thrown for a different reason. In such a case, the rollback() call will fail for the same reason.

See the below contrived example for illustration, and a proposed better way to deal with it:

try {
    $conn = new PDO('mysql:host=example.local;dbname=MyDB;charset=utf8',  
        DB_USER, DB_PASSW, array(  
        PDO::ATTR_EMULATE_PREPARES => false,  
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'utf8'")
    );  

    //do some other stuff that could throw an exception
    /*
    @throws UnexpectedException
    */
    OtherLibrary::doSomething();

    $conn->beginTransaction();
    
    //... execute some queries

    //finally, commit the transaction
    $conn->commit()
} catch (Exception $e) {
    //this branch could be reached due to an exception thrown by OtherLibrary::doSomething(), so you should check before you attempt to rollback.
    if ($conn->inTransaction()){
        $conn->rollBack()
    }
    //handle the exception
}

Kwabena