CODEWITHSUNDEEP
phpmysqltransactions
rjmunro
rjmunro

Reputation: 28056

In PHP, can I get MySQL to rollback a transaction if I disconnect without committing, rather than commit it?

If I run the following PHP, I would expect no value to be inserted into the test table, because I have a transaction that I haven't committed:

$db = mysql_connect("localhost","test","test");
mysql_select_db("test");
mysql_query("begin transaction;");
mysql_query("insert into Test values (1);") or die("insert error: ". mysql_errror());
die('Data should not be commited\n');
mysql_query("commit;"); // never occurs because of the die()

But instead it seems to commit anyway. Is there a way to turn off this behaviour without turning off autocommit for the PHP that doesn't use transactions elsewhere on the site?

Upvotes: 1

Views: 2166

Answers (3)

rkcell
rkcell

Reputation: 468

By default, the transaction will not be rolled back. It is the responsibility of your application code to decide how to handle this error, whether that's trying again, or rolling back.

If you want automatic rollback, that is also explained in the manual:

The current transaction is not rolled back. To have the entire transaction roll back, start the server with the `--innodb_rollback_on_timeout` option.

Upvotes: 0

Álvaro González
Álvaro González

Reputation: 146430

The syntax to start a transaction is:

START TRANSACTION

The feature you are talking about is AUTOCOMMIT. If you don't want it, you'll have to disable it:

SET autocommit = 0

The reference can be found at http://dev.mysql.com/doc/refman/5.1/en/commit.html

I also recommend that you test the return value of all mysql_...() functions. You cannot assume that they'll always run successfully.

Upvotes: 1

mellowsoon
mellowsoon

Reputation: 23231

Use mysql_query('BEGIN'). The SQL "BEGIN TRANSACTION" is not valid (and in fact mysql_query is returning false on that query, which means there is an error). It's not working because you never start a transaction.

Upvotes: 4

Related Questions