I have 3 queries regarding PDO and Transactions. I'm pretty new at transactions, so please understand my misunderstandings!
First, i'll explain the situation: I have created a PDO wrapper in a singleton pattern, which accesses a MySQL database with innoDB tables. The reason I've created a wrapper is to provide extra functionality and improve portability.
Each time an object needs access to the database, it retrieves the same connection.
My questions are as follow:
1) I understand if I begin a transaction with PDO::beginTransaction()
, then that sets MySQLs autocommit mode to off. However does this affect just the current users connection to the database, or every visitors connection, since its the database which contains the autocommit value, not the PHP script?
2) I have a need to perform MySQL queries which are not relevant to the actual transaction. These queries are performed in unrelated objects, however still use the same MySQL connection as the transaction. I've just found out that the unrelated (to the transaction) queries are still being included in the transaction, which is causing undesired effects. Is there any way around this? Or is my PHP design incorrect for this use of transactions?
3) I'm using PHP 5.1. It supports the following functions: PDO::beginTransaction()
, PDO::commit()
and PDO::rollBack()
.
I understand that if I call PDO::rollBack()
, when a transaction is not in effect, a PDOException is thrown. The solution to this is to use PDO::inTransaction()
to check for transactions before using rollBack(), however this isn't added to PHP until 5.3.
At the moment, when my wrapper starts a transaction, it updates a static variable to true, and when its rolled back, or committed, it sets the variable back to false. Is this a good solution? Or is there a pre-existing solution I'm unaware of?
Many thanks for your help