How to create a new transaction scope in propel

Question

We are using propel as orm on a mysql db. We are changing several tables and call external services during a transaction. In between we log these actions and the response in a logging table. If an error occurs, we revert the actions but want to keep the log messages. At the moment the log messages are using the same transaction scope and would be reverted with the transaction.

Do I get a new connection and transactionscope with

$con = Propel::getConnection(DATABASE_NAME);

or do I have to check if the same connection is returned

PSEUDO CODE

public function write_log()
{
  $con = Propel::getConnection(DATABASE_NAME);

  $log=new Log();
  $log->message('foo');
  $log->save($con);
}

public function change_data()
{
    write_log('start');

    $con = Propel::getConnection(DATABASE_NAME);
    $con->beginTransaction();
    try {
       //this message should stay in the database
       write_log('change_db_data:'.$new_db_value);
       //this should be reverted
       change_db_data($new_db_value); 

       write_log('call webservice_1');
       $response=call_webservice_1();
       write_log($response);
       if($response==null)
       { 
         $con->rollback();
       }

       write_log('call webservice_2');
       $response=call_webservice_2();
       write_log($response);
       if($response==null)
       { 
         $con->rollback();
       }


       $con->commit();
    }
    catch(Exception $e){
        $con->rollback();
        write_log('error')
    }
    write_log('end');
}

Answer 1

Good choice picking Propel. You have two choices, either encapsulate the logging in their own transactions, or use a nested transaction, uniquely supported by Propel.

The first requires only transactionality in the write_log function:

public function write_log()
{
  $con = Propel::getConnection(DATABASE_NAME);

  $con->beginTransaction();
  $log=new Log();
  $log->message('foo');
  $log->save($con);
  $con->commit();
}

The second is to start a nested transaction and ensure that only the inner transaction is rolled back:

public function write_log()
{
  $con = Propel::getConnection(DATABASE_NAME);

  $log=new Log();
  $log->message('foo');
  $log->save($con);
}

public function change_data()
{
    $con = Propel::getConnection(DATABASE_NAME);
    $con->beginTransaction();
    write_log('start');

    $con->beginTransaction();
    try {
       //this message should stay in the database
       write_log('change_db_data:'.$new_db_value);
       //this should be reverted
       change_db_data($new_db_value); 

       write_log('call webservice_1');
       $response=call_webservice_1();
       write_log($response);
       if($response==null)
       { 
         throw new \Exception('Null response.');
       }

       write_log('call webservice_2');
       $response=call_webservice_2();
       write_log($response);
       if($response==null)
       { 
         throw new \Exception('Null response.');
       }

       $con->commit();
    }
    catch(Exception $e){
        $con->rollback();
        write_log('error')
    }
    write_log('end');
    $con->commit();
}