PDO Multiple queries: commit and rollback transaction

Question

I need to fire 2 queries. currently I'm doing it like this:

// Begin Transaction
$this->db->beginTransaction();

// Fire Queries
if($query_one->execute()){
    if($query_two->execute()){

        // Commit only when both queries executed successfully
        $this->db->commit();

   }else{
        $this->db->rollback();
   }
}else{
    $this->db->rollback();
}

Is this the correct approach? I'm not using any try..catch in my code will it make my code inappropriate or vulnerable for any situation?

Answer 1

Yes, your approach is correct. Using Try...catch may lead into cleaner and more readable code in some cases but your overall approach is fine.

If your code fragment is from a function that handles DB queries and not much else, I'd probably switch the approach around:

// Begin Transaction
$this->db->beginTransaction();

// Fire Queries
if(!$query_one->execute()){
    $this->db->rollback();
    // other clean-up goes here
    return;
}

if(!$query_two->execute()){
    $this->db->rollback();
    // other clean-up goes here
    return; 
}

$this->db->commit();

Of course, if you require lots of clean-up to be done before you can return, then your original approach is better. Especially in these cases I'd look into using PDO::ERRMODE_EXCEPTION. This has some additional benefits, like exceptions automatically rolling back the transaction unless they are caught.