SciGuyMcQ SciGuyMcQ - 2 months ago 7
PHP Question

PHP PDO Transaction Management across multiple write methods on different classes

I need help architecting an appropriate approach to managing transaction across multiple writes over multiple data access methods on different classes.

Consider the following example code.

PDO Connection Class

class DBService {
private static $instance = null;
public static function connect() {
if(self::$instance === null) {
try {
self::$instance = new PDO("mysql:host=myservername;dbname=mydbname", 'myusername', 'mypassword');
} catch (\Exception $e) {
return null;
}
}
return self::$instance;
}

protected function __construct() {} // prevent creating new instance (faking a singleton)
}


Order Repository for Database Access

class OrderRepository {
public static function insert($data) {
$con = DBService::connect();
$stmt = $con->prepare('INSERT INTO Orders (customer_id, order_date) VALUES (:custId, NOW())');
$stmt->execute(array(':custId'=>$data['custId']));
return $con->lastInsertId();
}
}


OrderDetailRepository (lineitems) for an order's products

class OrderDetailRepository {
public static function insert($data) {
$con = DBService::connect();
$stmt = $con->prepare('INSERT INTO OrderDetails (order_id, product_id, quantity) VALUES (:orderId, :prodId, :qty)');
$stmt->execute(array(':orderId'=>$data['orderId'], ':prodId'=>$data['prodId'], ':qty'=>$data['qty']));
return $con->lastInsertId();
}
}


The service class to handle the logic of creating an order (ie Order entry + Order detail entry). This is where I need help understanding how to implement a transaction so that if there is an error in the 2nd insert (Order Details) I can rollback the work done inserting into the Order table.

class OrderService {
public static function createOrder($data) {
try {
$orderId = OrderRepository::insert($data);
$orderDetailId = OrderDetailRepository::insert($data);

// Commit transaction
} catch (\Exception $e) {
// Roll back transaction
return false;
}
return true;
}
}


I tried to make this question succinct so the code example is as minimalistic as I could make it. But still I apologize for the verboseness.

Thanks

Answer

Simply wrap the transaction around where you control the flow of activities, in this case in the OrderService->createOrder() method

class OrderService {
    public static function createOrder($data) {
       try {
           $con = DBService::connect();
           $con->beginTransaction();

           $orderId = OrderRepository::insert($data);
           $orderDetailId = OrderDetailRepository::insert($data);

           // Commit transaction
           $con->commit();
       } catch (\Exception $e) {
           // Roll back transaction
           $con->rollback();
           return false;
       }
       return true;
    }
}

Or you could move it back a level and do it around the call to OrderService->createOrder()

The method can then be simplified to

class OrderService {
    public static function createOrder($data) {
       $orderId = OrderRepository::insert($data);
       $orderDetailId = OrderDetailRepository::insert($data);
    }
}

Your main processing loop

try {
    $con = DBService::connect();
    $con->beginTransaction();

    $OrderService->createOrder($data);

    $con->commit();

} catch (\Exception $e) {
    // Roll back transaction
    $con->rollback();
}
Comments