Two quick question on transaction behaviour

Question

I have code that looks like this:

function foobar(array& $objects, $con = null)
{
   if (is_null($con))
      $con = DbSingleton::getConnectio();

   $con->beginTransaction();  // <- question 1

   try
   {
     foreach($objects as $object)
     {
        // allocate memory for new object 
        $new_obj = new MyShiningNewObject();

        // do something to the new object ...
        $new_obj->setParentId($object->getId());
        $new_obj->save($con);

        // mark for garbage collection
        unset($new_obj);  // <- question 2
      }

     $con->commit();
  }
  catch(Exception $e){ $con->rollBack(); }      
}

My questions are:

1. I am begining a transaction, this could well be a nested transaction. In the case of a nested transaction, if an exception is thrown and I rollback, how far back does the rollback go (to the outermost transaction) - common sense suggest that this should be the case, but one never knows.

2. I am freeing memory (ok, marking as 'freeable' by the Zend GC). Since I am commiting the transaction AFTER the loop, (the variable is marked as frreable IN the loop), is this safe - I mean will the data be safely stored in the db even though I have unset the variable that the value came from?

Answer 1

To answer question 2 :

• once the query has been sent to the database, what is done on PHP variables doesn't matter anymore
• the only thing you'll do that will have an impact is when you'll commit or rollback.
• which means that unsetting the PHP variable will have no impact on the transactions/data on the DB side.

About question 1 :

• If your database system supports nested transactions, commiting/rollbacking on an "inner" transaction should, logically, only act on that "inner" transaction.
• But note that nested transactions are not supported by all DBMS -- MySQL, for instance, doesn't support them, as far as I remember.
• For reference, here are three questions about nested reference (there are probably many more) :
  • Nested transactions in postgresql 8.2?
  • Nested transactions in Sql Server
  • Are nested transactions allowed in MySQL?

Answer 2

Since only the results of the outermost transaction as visible from outside, nesting transactions have little sense as such, and of the major systems only SQL Server supports them.

In SQL Server, an intermediate rollback rolls back to the beginning of the intermediate transaction, an intermediate commit does nothing.

The outermost rollback rolls back the whole outermost transaction (even if there were commits in between), the outermost commit commits the whole outermost transaction.

The other systems have only one transaction level with possible SAVEPOINTS in between. You can rollback to each of the previous savepoints (by providing their name), but issuing a COMMIT or ROLLBACK without the savename point always commits or rolls back the outermost transaction.