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

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 arou开发者_Go百科nd that though.

Is there a better way to handle transactions?

---

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.

---

There's nothing intrinsically wrong with a single connection. If you have multiple connections, then each runs an independent transaction. You basically have two options.

• Maintain the current single connection object for each of the three services
• Maintain separate connections (with related overheads) for each service, and commit/rollback each individual connection separately (not particularly safe, because you can't guarantee the ACID consistency then)

As a way round the two separate database instances that you're connecting to: use db links so that you only connect to a single database