Suggestion, php mysql transaction for insertion to multiple tables in a block

Question

I need to use transaction for a block of code, it consists of multiple insertions. Is it a good practice to have whole block of code within a try catch block, start transaction before the try..catch block. Then for any exception caught rollback the transactions else commit.

Basic question:

• is it bad practice to have whole block of code within a single transaction cycle?
• If it is a bad practice what would be a good way to handle this and why?

Here's a block of code:

    $con = Propel::getConnection(SomeTablePeer::DATABASE_NAME);        
    $con->beginTransaction();        
    try {
        $currentRevision = $budgetPeriod->getRevision();
        $newRevision = $currentRevision->copy();
        $newRevision->setIdclient($client->getIdclient());            
        $newRevision->setIsLocked(0);
        $newRevision->save();
        $currentRevision->setEffectiveTo($currentDate);
        $currentRevision->save();

        $currentRevisionHasCorporateEntities = $currentRevision->getCorporateEntitys();
        $newOldCorporateEntitiesRelations = array(); 

        foreach ($currentRevisionHasCorporateEntities as $currentRevisionHasCorporateEntity) {

            $newRevisionHasCorporateEntity = $currentRevisionHasCorporateEntity->copy();                
            $newRevisionHasCorporateEntity->save();
        }

     // this continues for a while there are a whole list of insertions based on previous insertion and on and on.
    }catch (Exception $exc) {
        $con->rollback();            
        $this->getUser()->setFlashError('Error occured! Transaction Failed');
    }

Answer 1

Actually, We should focus on smaller transaction boundaries so that we can avoid any locks if occurs in Db but sometimes we need whole block of code to either executed or not, so in that case we hardly have any chances left with us, you need to modularize your code as much as possible.