Hello, I would like to ask your opinion on the understanding that I
have of PDO transactions.
I'll begin with some pseudo-php code to show you how I'm using PDO transactions.
<?php
try {
PDO::beginTransaction
// here is the bulk of the script
// which may contain many
// PDO::prepare
// PDOStatement->bindParam
// PDOStatement->execute
// sequences
PDO::commit
}
catch (PDOException) {
PDO::rollBack
}
catch (Exception) {
PDO::rollBack
}
?>
Here is the reasoning I'm making to justify this usage.
Having only one huge transaction that encompasses practically all the
script produces the following results:
- Avoids the overhead from multiple commits.
- If something goes wrong during the execution of the script I can be
practically sure that the database wasn't 'corrupted'.
>From a database point of view it should be like the script never started at all.
- Even if I don't commit after each execute I will still have a
consistent behaviour.
For example, after deleting a record I won't be able to retrieve it
with a select.
- Even if the script takes a long time to complete I'm not risking to
hit a timeout and a rollback.
I'm quite positive about the above points, the main concern that I'm
having is about the possible locking issues that I might have when
multiple instances of the script are running simultaneously.
Could it be possible that a running script instance blocks the
database until the commit or something like that?
I'm using MySQL with InnoDB tables and if I'm not mistaken they
provide row level locking, which should be quite good for performance
under load.
But I'm not totally sure what results I should expect from the
approach I'm using, from a performance point of view.
If this is of any help I'm using persistent connections, with
something like this:
new PDO('mysql:host=127.0.0.1;port=1234;dbname=db', 'db_user', 'pass',
array(PDO::ATTR_PERSISTENT=>true, PDO::ATTR_EMULATE_PREPARES=>true,
PDO::ATTR_ERRMODE=>PDO::ERRMODE_WARNING));
Any thoughts on the approach I'm using would be most welcome.
I would also be willing to switch to PostgreSQL if you think that the
MySQL/InnoDB solution isn't appropriate for this kind of usage.
Thanks for reading!
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
