CODEWITHSUNDEEP
phpmysqlpdo
Nate
Nate

Reputation: 28434

Does PDO::beginTransaction() cause table or row locking until PDO::commit()?

I have a PHP script that does around 1,000 inserts and 1,000 updates each loop using prepared statements. It loops as long as work needs to be done, sometimes for hours, and is causing performance problems (including deadlock exceptions).

I am trying to improve performance and am looking into bulk inserts and updates (although I'm still trying to figure out how to even do bulk updates).

When PDO::beginTransaction() is called, does it simply tell the PDO class not to send queries excecuted with PDO::execute() to the database, or does it send the query and then lock the tables/rows used until PDO::commit() is called?

The reason I ask is that the PHP script takes up to 50 seconds each loop and it makes the insert and update queries over that time span, so I'm trying to figure out if I can simply add PDO::beginTransaction() at the beginning of the script and PDO::commit() at the end.

Upvotes: 3

Views: 2851

Answers (2)

Joe Kdw
Joe Kdw

Reputation: 2261

PDO::beginTransaction() basically is autocommit mode. When you start a transaction ( $mydb->beginTransaction(); ), it means that you turn off PDO AutoCommit.

And, that would never stop to go on the transaction until you stop by Commit or Roll it back. e.g $mydb->commit(); When you stop that, it would turn into autocommit again. Here, you can control the transaction with commit or rollback.

In related to the issue that you have huge queries to handle, I suggest you to do that step by step based on the priorities, as follows:

// start your transaction - means - stop auto commit.
If(query1 gets successful){
   If(query2 gets successful)
   {
      // Commit the transaction // stop the transaction
   }
   else
   {
      // Roll back the transaction 
   }
}
else{
   // Rollback the transaction
}

Be aware of using PDO transaction since the result cannot be undone. see this ref: http://php.net/manual/en/pdo.transactions.php

Upvotes: 1

dynamic
dynamic

Reputation: 48141

Actually PDO::beginTransaction() follows the same rules as a START TRANSACTION statement in MySQL, more info here.

This means that MySQL will not lock the table immedialy, but will follow the ACID rules.

Note that if you need to explicit lock some table(s) you can do:

$db->beginTransaction();
$db->exec('LOCK TABLES t1, t2, ...');

Upvotes: 1

Related Questions