[FIXED] What is the preferred way to get access a transaction to commit or rollback?

Issue

I understand how transactions work and everything functions as expected, but I do not like the way I access connections to commit or rollback transactions.

I have 3 service classes that can access the same singleton connection object. I want to wrap these three things in a single transaction, so I do this:

try {
  $service1 = new ServiceOne;
  $service2 = new ServiceTwo;
  $service3 = new ServiceThree;

  $service1->insertRec1($data);
  $service2->deleteRec2($data);
  $service3->updateRec3($data);

  $service1->getSingletonConnection()->commit();
}
catch(Exception $ex) {
  $service1->getSingletonConnection()->rollback();
}

The connection object returned by getSingletonConnection is just a wrapper around the oci8 connection, and committing is oci_commit; rollback is oci_rollback.

As I said, this works because they are all accessing the same connection, but it feels wrong to access the connection through any arbitrary service object. Also, there are two different databases used in my app so I need to be sure that I retrieve and commit the correct one... not sure if there is any way around that though.

Is there a better way to handle transactions?

Solution

it feels wrong to access the connection through any arbitrary service object.

I agree with you 100%.

It seems to me that if each service only makes up part of a database transaction, then the service cannot be directly responsible for determining the database session to use. You should select and manage the connection at the level of code that defines the transaction.

So your current code would be modified to something like:

try {
  $conn = getSingletonConnection();
  $service1 = new ServiceOne($conn);
  $service2 = new ServiceTwo($conn);
  $service3 = new ServiceThree($conn);

  $service1->insertRec1($data);
  $service2->deleteRec2($data);
  $service3->updateRec3($data);

  $conn->commit();
}
catch(Exception $ex) {
  $conn->rollback();
}

It seems like this would simplify dealing with your two-database issue, since there would only be one place to decide which connection to use, and you would hold a direct reference to that connection until you end the transaction.

If you wanted to expand from a singleton connection to a connection pool, this would be the only way I can think of to guarantee that all three service calls used the same connection.

Answered By - Dave Costa

Answer Checked By - Mary Flores (PHPFixing Volunteer)