Transactions combined with PDO php, several clients in the same time

I have a problem: I have a PHP application, whose users writes a form each 5 minutes. The form is saved in 4 to 5 data tables. In the moment to save the information, I have to save it in each data table in the same time in order to avoid duplicates, and to keep the structures. So I use a transactions to make it possible with each different insert SQL statement. Also, I use transactions in order to make rollbacks wether it was neccesary. Besides I use transactions combined with PDO from PHP in a dynamic function:

public static function ejecutarQueryTransactionPDO($vaStrSQL,$vaValues,$vaTypesValues){
$stmt = null;
$conn = null;

try {
  $conn = Datos2PDO::createConnectionPDO();

  $conn->setAttribute(PDO::ATTR_EMULATE_PREPARES , PDO::CASE_NATURAL);

  $stmt = null;

  $conn->beginTransaction();

  for ($j=0, $m = count($vaStrSQL); $j < $m; $j++) {
    $stmt = $conn->prepare($vaStrSQL[$j]);

    for ($i=0, $n = count($vaValues[$j]); $i < $n; $i++) {
      if ($vaTypesValues[$j][$i] != 'd') {
        $stmt->bindParam($i+1, $vaValues[$j][$i], Datos2PDO::$aTiposParametrosPDO[$vaTypesValues[$j][$i]]);
      } else {
        $stmt->bindParam($i+1, intval($vaValues[$j][$i]), Datos2PDO::$aTiposParametrosPDO[$vaTypesValues[$j][$i]]);
      }
    }

    $stmt->execute();
  }

  $conn->commit();
} catch (Exception $e) {
  file_put_contents("../../".DaoParameters::CARPETA_ERRORES."/notrs_logs_error.txt", date('Y-m-d H:i:s')." ".$_SESSION["NOTRS_userName"]." datos2PDO ejecutarQueryTransactionPDO ".$e->getMessage()."\n", FILE_APPEND | LOCK_EX);
  if (isset ($conn)) {
    $conn->rollback();
  }
  $stmt = null;
  $conn = null;
  return false;
}

//close connection
$stmt = null;
$conn = null;

return true;
}

So, my question is: is it better use several stored procedures instead of transaction? do the transactions affect the performance in a bad way? do the lock of tables in the transactions affect the performance in a bad way? can you give me advices about how to increase the performance and speed?

1 answer

  • answered 2018-07-11 05:19 zedfoxus

    Transaction is a good thing. Without transactions, there's a risk of data integrity loss. You don't want bad data in your databases. Transactions have a penalty associated with it also. However, the benefits of transactions (i.e. ensures data integrity) outweighs penalties.

    Ensure that your transactions aren't too long or long-lasting. If your query is always the same but parameters' values are different, just prepare the query once and then execute multiple times. That'll reduce the cost of preparing the query multiple times in a loop. What you are doing in your example appears just fine.