Symfony2: transactions fail with "There is no active transaction."

Question

I've spend several hours trying to resolve this issue. Google and Stackoverflow didn't help much either. So any advice is most welcome here.

I'm trying to apply a rollback logic with transactions when updating two tables in relation:

The code in general is:

// ...
$em = $this->getDoctrine()->getEntityManager();
$em->getConnection()->beginTransaction();

foreach($dataArr as $data) {
    $userObj = $em->getRepository('AcmeBundle:User')->find($userId);
    $userObj->setActive(1);
    $em->persist($userObj);
    $em->getConnection()->commit();
}

$storeObj = $em->getRepository('AcmeBundle:Store')->find($storeId);
$storeObj->setIsOpen(1);
$em->persist($storeObj);
$em->getConnection()->commit();

try {

    $em->flush();
    $em->clear();    

} catch(Exception $e) {

    $em->getConnection()->rollback();
    $em->close();
    throw $e;

}

My PDO drivers are enabled, updating without transactions works as expected, but once I beginTransaction() and try to commit() nothing works and I get the There is no active transaction. exception.

Some sources suggested using only commit() without persist() but it doesn't make any difference. I'm probably doing something really stupid here, but I just cannot see what it is.

Answer 1

As @prodigitalson correctly suggested I needed to do a commit() before the flush() in order for the get the queries executed. So the working code now is:

$em = $this->getDoctrine()->getEntityManager();
$em->getConnection()->beginTransaction();

foreach($dataArr as $data) {
    $userObj = $em->getRepository('AcmeBundle:User')->find($userId);
    $userObj->setActive(1);
    $em->persist($userObj);
    // this is no longer needed
    // $em->getConnection()->commit();
}

$storeObj = $em->getRepository('AcmeBundle:Store')->find($storeId);
$storeObj->setIsOpen(1);
$em->persist($storeObj);
// this is no longer needed
// $em->getConnection()->commit();

try {

    // Do a commit before the FLUSH
    $em->getConnection()->commit();
    $em->flush();
    $em->clear();    

} catch(Exception $e) {

    $em->getConnection()->rollback();
    $em->close();
    throw $e;

}

Answer 2

Since the version 1.5.2 of DoctrineBundle, you can configure the connection to use auto_commit in the configuration of your project.

# app/config/config.yml (sf2-3) or config/doctrine.yaml (sf4)
doctrine:
    dbal:
        auto_commit: false

Answer 3

I once accidentally got this error by doing following:

$em->getConnection()->beginTransaction();
try {
    $em->persist($entityA);
    $em->flush();

    $em->persist($entityB);
    $em->flush();

    $em->getConnection()->commit();
    //exception thrown here
    $mailer->send($from, $to, $subject, $text);
} catch (\Exception($ex)) {
    $em->getConnection()->rollback();
}

So, you already have guessed that there should not be any code after commit as in the case when this arbitary code($mailer service in our example) throws an exception transaction would be closed before the moment catch block is executed. Maybe this will save a minute or two to somebody:)

Answer 4

After

$this->em->getConnection()->beginTransaction(); 

you must write:

$this->em->getConnection()->setAutoCommit(false);

It works for me :)