CODEWITHSUNDEEP
phpmysqltransactionsadodbrollback
JVApen
JVApen

Reputation: 11317

How to Rollback transactions in PHP ADODB

I'm trying to use DB-transactions using ADODB in php (mysql-driver), though for some reason I cannot get the transactions working. Currently the code looks a bit like this (simplified in order to make things sequential)

// Start DB-connection
$db = NEWADOConnection('mysql');
$db->debug = false;
$db->disableBlobs = false;
$db->PConnect($host, $login, $password, $database); or die ("Connection failed");

// Start DB-transaction
$db->autoCommit = false; // According to doc, this should not be needed
$db->SetTransactionMode("SERIALIZABLE");
$db->StartTrans();

// Update some value
$statement = $db->Prepare("UPDATE tableName SET column=? WHERE id=$id");
$result = $db->Execute($statement, array($newValue));

// Do a rollback
$db->FailTrans(); // According to doc, indicates that CompleteTrans will rollback
$db->CompleteTrans();

// Close connection
$db->Close();

As far as I understood the documentation of ADODB, the code should work, though for some reason, the table is still updated after running this code.

Has anyone an idea which configuration I have forgotten to change?

Thanks in advance

JVApen

Some background info:

PHP-version: PHP 5.3.10-1ubuntu3.9
ADODB-version: V5.18
Documentation I've based my code on: http://phplens.com/lens/adodb/docs-adodb.htm

Before someone comments on a strange thing in the code: Yes, I'm using prepared statements and string-concat together for queries, though $id is something which I can control, while $newValue is some input-argument.

Upvotes: 4

Views: 2301

Answers (1)

JVApen
JVApen

Reputation: 11317

Found the problem, the issues does not appear to be in ADODB (Though it should have warned).

Using MySQL with MyISAM-tables instead of InnoDB-tables makes sure that transactions are not supported. If you would have a mix of both, transactions is only supported for the InnoDB-tables.

Transformed all tables to InnoDB, and the problem appears to be solved now.

Upvotes: 4

Related Questions