Use SELECT to check a condition in a transaction before commiting

169 views Asked by At

In a quite big transaction that must roolback if exception occurs to avoid incoherencies in the DB, I'm doing a couple of operations including selects, updates and inserts.

In the simplified example below I would like to cancel some INSERTs on certain conditions in the "second operation" part while preserving the "first operation" part.

My problem is that DBAL as well as PostGreSQL do not support nested transactions and hence I cannot partially rollback the "second operation" alone.

I'm wondering how to deal with this problem. I was thinking of DELETE-ing the INSERTs when $condition is hit but it sounds like a nasty solution to me.

How can I handle this in a cleaner way?


$conn->beginTransaction();

try{

    // ==========================
    // Do a first operation in DB
    // ==========================
    $conn->beginTransaction();
    try{

      // Do an INSERT in DB

      $conn->commit();

    } 
    catch(Exception $e) 
    {
      $conn->rollBack();
      throw $e;
    }

    // ===========================
    // Do a second operation in DB
    // ===========================
    $conn->beginTransation();
    try{

      // Do multiple INSERTs in DB
      while( !$exit )
      {
          // Do one INSERT

          // Go to next occurrence OR exit    
      }

      if( $condition )
      {
          // Here I would like to cancel all the DB INSERTS. 
          // To ease the operation I would like to use the rollBack function.
          // But DBAL is not designed like this. Calling rollback here will  rollback 
          // the outer try also and hence all DB operations inluding the first operation.
          // In addition, PostGreSQL doesn't seem to support nested transactions.
          $conn->rollBack();
      }
      else
      {
          $conn->commit();
      }

    }
    catch(Exception $e) 
    {
      $conn->rollBack();
      throw $e;
    }

    $conn->commit();

} 
catch(Exception $e) 
{
  $conn->rollBack();
  throw $e;
}
0

There are 0 answers