0

In PHP you can start transactions when you are performing queries. But when should you use it? Is it bad to use this feature with every query? Or should you only use this query when you are adding/removing/updating large quantities of data?

Example of a transaction:

try {
    $db->beginTransaction();

    $db->exec("some query");

    $stmt = $db->prepare("another query");
    $stmt->execute(array($value));

    $stmt = $db->prepare("another query again??");
    $stmt->execute(array($value2, $value3));

    $db->commit();
} catch(PDOException $ex) {
    //Something went wrong rollback!
    $db->rollBack();
    echo $ex->getMessage();
}

My guess would be that it would heavily bring down performance if you do this with every query in your code

tl;dr Can you use transactions with every query? Or should you only use them when performing large manipulations to the database?

Dacaspex
  • 669
  • 7
  • 15

1 Answers1

2

You definitely need to use transactions if you want to make all changes or none. For example if you transferring money from one person to another you should increase one's balance and decrease another's.

It's good to use transactions if you have many simple queries and you need performance. It depends on database but often databases try make all changes in memory during transaction and only then flush to disk. Memory is much faster than disks.

But it only matters if you have highload site or you need to insert 1000s of records and each 100ms is important. If you have less than 100 queries, they are simple and there is nothing wrong if you execute half of them and then script breaks (server restart, PHP timeout, ...) and your data is still consistent - you may not use transactions.

If you think that you don't use transactions if you don't start them explicitly you may be wrong. It depends on the database but it may autostart transaction with each query.
https://stackoverflow.com/a/2950764/437763

Community
  • 1
  • 1
luchaninov
  • 6,792
  • 6
  • 60
  • 75