Is this a good way of using PDO transactions?

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.


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

Re: Is this a good way of using PDO transactions?

> - 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.

Not 100% completely. I'm not sure about innodb but in postgresql, a
"serial" datatype (works the same as an autoincrement field in mysql)
doesn't get rolled back if a transaction aborts. This is by design.

> 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?

If you're using innodb (or falcon) in mysql or postgresql, that's the
point of them being transactional database engines.

Each script instance will be completely independent of the others in
that they won't see anything that happens in each others transactions.

One big problem with having an extremely big transaction (depending on
what you're doing of course) is that it may take a while to commit or
rollback the whole thing.

If you're processing 10,000 records - should be no problem at all. If
you're processing a few million, it can become a problem.

Mysql/innodb will also issue commits implicitly for some actions (eg
'alter table' or 'drop table' type commands). Postgresql supports these
inside transactions. See
http://dev.mysql.com/doc/refman/5.0/en/implicit-commit.html

--
Postgresql & php tutorials
http://www.designmagick.com/

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php