php - What kind of codes can be into PDO transactions?

50

Here is my script:

$id    = $_GET['id'];
$value = $_GET['val'];

// database connection here

try{
    $db_conn->beginTransaction();
    // inserting
    $stm1 = $db_conn->prepare("INSERT into table1 (col) VALUES (?)");
    $stm1->execute(array($value));

    // updating
    $stm2 = $db_conn->prepare("UPDATE table2 SET col = "a new row inserted" WHERE id = ?");
    $stm2->execute(array($id));

    $db_conn->commit();
}
catch(PDOException $e){
    $db_conn->rollBack();
}

All I want to know, can I use anif statement in the codes which are betweenbeginTransaction() andcommit() ? Something like this:

$id    = $_GET['id'];
$value = $_GET['val'];

// database connection here

try{
    $db_conn->beginTransaction();
    // inserting
    $stm1 = $db_conn->prepare("INSERT into table1 (col) VALUES (?)");
    $stm1->execute(array($value));

    // updating
    if (/* a condition here */){
        $stm2 = $db_conn->prepare("UPDATE table2 SET col = "a new row inserted" WHERE id = ?");
        $stm2->execute(array($id));
    }

    $db_conn->commit();
}
catch(PDOException $e){
    $db_conn->rollBack();
}

Can I ?


Actually I asked that because here is a sentence which says you can't and doing that is dangerous:

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

395

Answer

Solution:

There is no problem with your transaction structure. The comment on php.net only means, that MySQL does not support nested transactions. In order to your further question, you can query any data (SQL), manipulate data (DML), but not modify any database structures (DDL - data definition language).

/*won't work*/
START TRANSACTION;
/*statement*/
START TRANSACTION; /*nested not supported, auto commit*/
/*statement*/
COMMIT;
/*statement dependend on 1st transaction won't work*/
COMMIT;

See also MySQL ref

Transactions cannot be nested. This is a consequence of the implicit commit performed for any current transaction when you issue a START TRANSACTION statement or one of its synonyms.

901

Answer

Solution:

You can do everything within a transaction, the only thing you cannot do is nest transactions.

Not theif clause itself is the problem in your linked comment, but the fact there is anotherbeginTransaction /commit pair inside.

People are also looking for solutions to the problem: php - How to delete the folder and contents using Yii2?

Source

Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.

Ask a Question

Write quick answer

Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.

Similar questions

Find the answer in similar questions on our website.