note 21019 deleted from ref.mysql by googleguy
| From: | googleguy@php.net | Date: | Tue, 22 Oct 2013 09:01:48 +0000 |
| Subject: | note 21019 deleted from ref.mysql by googleguy | ||
| References: | 1 | Groups: | php.notes |
| Request: | Send a blank email to php-notes+get-196836@lists.php.net to get a copy of this message | ||
Note Submitter: jeyoung at priscimon dot com
----
[Ed. Note:
This may be due to the fact that subsequent calls to mysql_connect with the same parameters return
the same resource id for the connection, so in reality it is using the same connection. In order to
force a new link, you must specify the new_link parameter in mysql_connect.]
MySQL transactions
MySQL supports transactions on tables that are of type InnoDB. I have noticed a behaviour which is
puzzling me when using transactions.
If I establish two connections within the same PHP page, start a transaction in the first connection
and execute an INSERT query in the second one, and rollback the transaction in the first connection,
the INSERT query in the second connection is also rolled-back.
I am assuming that a MySQL transaction is not bound by the connection within which it is set up, but
rather by the PHP process that sets it up.
This is a very useful "mis-feature" (bug?) because it allows you to create something like
this:
class Transaction {
var $dbh;
function Transaction($host, $username, $password) {
$this->dbh = mysql_connect($host, $username, $password);
}
function _Transaction() {
mysql_disconnect($this->dbh);
}
function begin() {
mysql_query("BEGIN", $this->dbh);
}
function rollback() {
mysql_query("ROLLBACK", $this->dbh);
}
function commit() {
mysql_query("COMMIT", $this->dbh);
}
}
which you could use to wrap around transactional statements like this:
$tx =& new Transaction("localhost", "username", "password");
$tx->begin();
$dbh = mysql_connect("localhost", "username", "password");
$result = mysql_query("INSERT ...");
if (!$result) {
$tx->rollback();
} else {
$tx->commit();
}
mysql_disconnect($dbh);
unset($tx);
The benefit of such a Transaction class is that it is generic and can wrap around any of your MySQL
statements.