CODEWITHSUNDEEP
phpmysqltransactionspdo
40pro
40pro

Reputation: 1281

Is the benefit significant enough to use transaction if I'm only performing one query?

I'm wondering, since transaction is useful when you're performing a set of queries. However, if I'm only performing one query should I bother to use it?

I currently have

$res = $pdo->query("SELECT id FROM products LIMIT 5");
$row = $res->fetchAll();

And all it does is that. Is the following code more efficient? (I'm thinking that its not since its only doing one thing, but I'm not sure)

$pdo->beginTransaction();
$res = $pdo->exec("SELECT id FROM products LIMIT 5");
$pdo->commit();

If its not more efficient, is there an alternative method of doing this that may be more efficient?

Thank you.

Upvotes: 1

Views: 314

Answers (2)

yankee
yankee

Reputation: 40850

Transactions are not about efficiency

Transactions allow you to group several statements to one atomic statement, meaning that it will complete completely or not at all, but it must not complete partially. E.g. if you remove money from one bank account and then add it to another bank account then this action should either happen completely (account a as $x less and account b has $x more) or not at all. What MUST NOT happen is that you remove money from account a, then the system crashes and money is lost in the digital void.

You can't have no transactions

When you are using PDO and you don't explicitly start a transaction then you are in auto-commit mode. That means that every time you start a query, a transaction is started, the query is executed and the transaction is committed (means marked as complete) immediately.

Still thinking about performance?

There are some rare cases where thinking about performance and transactions may be justified. E.g. if you are manipulation large amounts of data it can be (whether it really is depends on your DB) faster to do all your statements in one large transactions. On the other hand in this case it is probably logical to do these manipulations in a transaction anyway, because if your program crashes, you want to rerun the program and it should start of from the beginning, not somewhere where it left of the last time.

Upvotes: 2

John Conde
John Conde

Reputation: 219894

If there are any actions performed after the query that are important to the function that script is performing (let's say an e-commerce transaction) and leaving the query in place would be problematic (i.e. something related to payment failed) then you will want to use transactions even though only one query will be performed. Otherwise you will have purchases without payment.

If that transaction is the only thing occurring in that script then transactions are unnecessary and can/should be avoided.

Here is a simplistic example:

try 
{
    $pdo->beginTransaction();
    $res = $pdo->exec("INSERT INTO orders ....");
    // Try to make payment
    $payment->process();
    $pdo->commit();
}
catch (Exception $e)
{
    // Payment failed unexpectedly! Rollback the transaction.
    $pdo->rollback();
}

Naturally you will want to do a better job of handling failures like this but this should give you a decent demonstration of when transactions are appropriate.

Upvotes: 3

Related Questions