How i can use transactions with SQL-Objects?

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 “” (insert2) is already in the database, the code have to undo the insert of “” (insert1). The code catches the exception and return the correct error “Transaction failed! Error: Duplicate entry ‘’ 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' => '',

$insert2 = $sql->insert('mails')->values([
    'email' => '',


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()) {
        echo "Transaction complete!";
    } else {
        echo "Transaction failed!";
} catch (\Exception $e) {
    echo "Transaction failed! Error: " . $e->getMessage();

Your example works:

Transaction failed! Error: Statement could not be executed
(23000 - 1062 - Duplicate entry '' 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

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!

This would not have happened with PostgreSQL. :wink:

1 Like