How to structure complex SQL Transactions in PHP

Question

I am totally confused about how we should write SQL transactions in PHP.
We have a invoice payment section, so we have to do

Make the DB changes in the invoice tables as per the payment details updateInvoice()
Do data insertions in the Journal as per the payment amount addJournals()
Update/Insert the payment details in the reports for reporting section setUpReport()

so we included all the three actions into a single transaction

try {
    $this->conn->beginTransaction();
    updateInvoice();
    addJournals();
    setUpReport();
    $this->conn->commit();
} catch (Exception $ex) {
    $this->conn->rollback();
}

There are around 8-10 tables involved in this transactions and it seems the transactions are locking all these tables.

Also we have noticed this process is taking too much time and there are occasional deadlocks happening during this process. On doing some research I understood we need to make the above transaction atomic and simple. And most of the suggestion points towards splitting the transaction into multiple transactions. So I was planning to make separate transaction for each function like

try {
    $this->conn->beginTransaction();
    updateInvoice();
    $this->conn->commit();
} catch (Exception $ex) {
    $this->conn->rollback();
}
try {
    $this->conn->beginTransaction();
    addJournals();
    $this->conn->commit();
} catch (Exception $ex) {
    $this->conn->rollback();
}
try {
    $this->conn->beginTransaction();
    setUpReport();
    $this->conn->commit();
} catch (Exception $ex) {
    $this->conn->rollback();
}

If I restructure the code like this, if an error happens on setUpReport() it will be difficult to revert the actions in the above 2 transactions. So I am really confused how we need to structure the transaction.

user_13817418 · Answer

I had the same problem, I changed max_execution_time = 60

If you can't change value, make reconnect

EXAMPLE:

  echo "Other queries in your system / framework.....";
               $this->sendQuery("SQL: SELECT * from session_tab...");
               $this->sendQuery("SQL: SELECT * from privileges... ");
               $this->sendQuery("SQL: .....");          
               $this->sendQuery("SQL: ......");          
               $this->sendQuery("SQL: SELECT * from table..."); //    Waiting long time..
               
              echo "After that you want execute next queries.. " ;
              
              $this->db->disconnect();
              $this->db->connect();
               
             try {
                $this->conn->beginTransaction();
                setUpReport();
                $this->conn->commit();
            } catch (Exception $ex) {
                $this->conn->rollback();
            }

How to structure complex SQL Transactions in PHP

1 Answers