Transactions in MySql

[sorin21us]

Someone please help me to understand transaction. In the following PDO code where should I put the try, catch code with the transactions, too?

$dbh->exec("CREATE TABLE animal (name char(40), category char(40)");
$sth = $dbh->prepare ("INSERT INTO animal (name, category)
VALUES (?, ?)");
$sth->execute (array ("ant", "insect"));
                
$sth = $dbh->query ("SELECT name, category FROM animal");

while ($row = $sth->fetch (PDO::FETCH_OBJ)){
printf ("Name: %s, Category: %s\n", $row->name, $row->category);
echo "<br>";
}

[DaveMaxwell]

You only need to put a transaction around a change to the database. So in your case, around the insert.

[sorin21us]

You mean something like this?

try {    
    $dbh->beginTransaction ();
    $sth = $dbh->prepare ("INSERT INTO animal (name, category)
    VALUES (?, ?)");
    $sth->execute (array ("porumbel", "pasare"));
    
    $dbh->commit ();
                    
    $sth = $dbh->query ("SELECT name, category FROM animal");
    while ($row = $sth->fetch (PDO::FETCH_OBJ)){
        printf ("Name: %s, Category: %s\n", $row->name, $row->category);
        echo "<br>";
    }
}catch (PDOException $e) {
    echo ("Transaction failed: " . $e->getMessage () . "\n");
    $sth->rollback ();
}

[DaveMaxwell]

You really only need it around the insert. The select and loop can be skipped, because those would be totally different errors

[sorin21us]

If this is the right code, I don’t understand, when I get an insert error, the rollback() will run automatically and undo the operation?
I ask you this because I didn’t do transactions before.

try {    
    $dbh->beginTransaction ();
    $sth = $dbh->prepare ("INSERT INTO animal (name, category)
    VALUES (?, ?)");
    $sth->execute (array ("ant", "insect"));
    
    $dbh->commit ();
                    
    
}catch (PDOException $e) {
    echo ("Transaction failed: " . $e->getMessage () . "\n");
    $sth->rollback ();
}

[TheRedDevil]

That depends on the error you receive on the insert. If it is a fatal one, yes. If it is just a failed insert due to duplicate keys etc, no.

With this in mind, you always have to check for the case you look for, and then do a rollback if something that was not expected happened.

[sorin21us]

I see. Thank you.
Someone told me that you use transaction only on the projects like e-commerce, where, for ex, you have 2 books to sell, but 3 buyers. And you take the money from all 3, but when you see that you don’t have books for all 3, you use rollback to send the money back to the last person.
Is this true, that transaction are used only on projects like this and not small project?

[TheRedDevil]

The example you mention would be what is called a “race condition”. In the exact example you mention a rollback would not help, since you cannot rollback the credit card transaction. And if you rollback the order, you suddenly lost all information you can use to track and fix this case. Instead you would need to have additional logic to handle and refund the third customer if this happen. Usually this is handled by a “out of stock” notice, and then the order is handled manually by the staff.

On a normal web store it is almost impossible that a race condition like the one above can happen, as it will require that all three orders are processed at the exact same time (or if there is a logic issue in the code, around the same time).

For larger web stores you normally apply fail safes to prevent this to happen if you have limited stock items (i.e. where you cannot order additional ones), where you reduce the available stock when someone add the product to their chart. This is a temporary reduce, which is released again after XX minutes if the order has not been completed by then.

Going back to database transactions. I strongly recommend that you always use them on every location where one insert/update query depend on another query.

For example, let say you create a user account, to do this you first need to create the main account record, and then add the user’s address to the address record. If you do this without using transactions, and the insert to the address record fails, you actually have an incomplete user account which may not work as intended.

In this case, it is recommended that you roll back if the insert to the address record fails and do not create the account at all. There is nothing worse than incomplete records, or orphan records in the database. Having that can have the developer team looking for a bug in the code which is not there and is caused by the database records being incomplete.