Phalcon PDO transaction is not working correct

Question

It nearly took me a whole day to resolve the problems about transactions, but I failed. My requirement is INSERT a new record both in table topic and table topic_data in one transaction. I have my code like this :

// database connection
$di->set( 'db', function() use( $conf ) {
    return new \Phalcon\Db\Adapter\Pdo\Mysql as DbAdapter( [
        'host' => $conf->db->host,
        'username' => $conf->db->username,
        'password' => $conf->db->password,
        'dbname' => $conf->db->dbname,
        'options' => [
            \PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION,
            \PDO::ATTR_PERSISTENT => true,
            \PDO::ATTR_AUTOCOMMIT => false
        ]
    ] ); 
} );

// transaction code
public function create( $params ) {
    $this->db->begin();

    $id = \Idalloc::next();
    $topic = new Topic();
    $topic->id = $id;
    $topic->ctime = $_SERVER[ 'REQUEST_TIME' ];

    $tags = $params[ 'tags' ];

    $params[ 'tags' ] = implode( ',', $tags );
    $topic->assign( $params );

    if( $topic->save() === false ) {
        $this->db->rollback();
        return false;
    }

    for( $i = 0, $l = count( $tags ); $i < $l; ++$i ) {
        $topicTag = new TopicTag();
        $topicTag->tag_id = $tags[ $i ];
        $topicTag->topic_id = $id;
        $topicTag->type = $params[ 'type' ];
        if( $topicTag->save() === false ) {
            $this->db->rollback();
            return false;
        }
    }

    var_dump( $this->db->isUnderTransaction() );

    $this->db->commit();
    return $id;
}

The fails with :

• If I don't set \PDO::ATTR_PERSISTENT => true, method "create" with return $id and var_dump( $this->db->isUnderTransaction() ) is TRUE but NO DATA been inserted into both table topic and table topic_tag

• If I set \PDO::ATTR_PERSISTENT => true, I will get Exception with : [Tue, 21 Jun 16 02:16:21 +0800][ERROR] PDOException: There is no active transaction And still failed to insert data into table topic, but new record appearing in table topic_tag.

• If I only keep one of the two parts, it will be working well.

How can I resolve this problem and is there a simple way to create Manual Transactions ?

Answer 1

I found out the solution for this question. The 'db' set in DI must be shared. So there need another parameter "TRUE" :

// database connection
$di->set( 'db', function() use( $conf ) {
    return new \Phalcon\Db\Adapter\Pdo\Mysql as DbAdapter( [
        'host' => $conf->db->host,
        'username' => $conf->db->username,
        'password' => $conf->db->password,
        'dbname' => $conf->db->dbname,
        'options' => [
            \PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION,
            \PDO::ATTR_PERSISTENT => true,
            \PDO::ATTR_AUTOCOMMIT => false
        ]
    ] ); 
}, true );