How i can use transactions with SQL-Objects?

Questions Components & MVC
1

When I work with statements in laminas-db, no transactions work. I think execute doesn’t use the same connection. Here’s a small example with a table “mails” that has a unique key on the field “email”. If the user “user@example.com” (insert2) is already in the database, the code have to undo the insert of “user2@example.com” (insert1). The code catches the exception and return the correct error “Transaction failed! Error: Duplicate entry ‘user@example.com’ for key ‘email’” but doesn’t undo the insert1. Do you have any tips for me?

use Laminas\Db\Adapter\Adapter;
use Laminas\Db\Adapter\Driver\ResultInterface;
use Laminas\Db\Sql\Sql;

$config = [
    'driver'   => 'Mysqli',
    'database' => 'database',
    'username' => 'root',
    'password' => 'pass',
    'hostname' => 'localhost',
];

$adapter =  new Adapter($config);
$sql = new Sql($adapter);

$insert1 = $sql->insert('mails')->values([
    'email' => 'user2@example.com',
]);

$insert2 = $sql->insert('mails')->values([
    'email' => 'user@example.com',
]);

$adapter->getDriver()->getConnection()->beginTransaction();

try {
    $statement1 = $sql->prepareStatementForSqlObject($insert1);
    $result1 = $statement1->execute();

    $statement2 = $sql->prepareStatementForSqlObject($insert2);
    $result2 = $statement2->execute();

    if ($result1 instanceof ResultInterface && $result1->getAffectedRows()
        && $result2 instanceof ResultInterface && $result2->getAffectedRows()) {
        $adapter->getDriver()->getConnection()->commit();
        echo "Transaction complete!";
    } else {
        $adapter->getDriver()->getConnection()->rollback();
        echo "Transaction failed!";
    }
} catch (\Exception $e) {
    $adapter->getDriver()->getConnection()->rollback();
    echo "Transaction failed! Error: " . $e->getMessage();
}
2

Your example works:

Transaction failed! Error: Statement could not be executed
(23000 - 1062 - Duplicate entry 'user@example.com' for key 'mails_email_uindex')

There is only one connection and it is used in $sql and the statements are create from this object.

1 Like
3

Oh my God. I wasted so much time on this. The database table was MyISAM and not InnoDB :confused: Thanks for your time and for testing this. Because it worked for you, I now checked the database for the problem. Thanks alot!

4

This would not have happened with PostgreSQL. :wink:

1 Like