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

Question

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?

Edit: This was just a stupid typo. It should be "start transaction" or "begin". Not "begin transaction". Sorry to waste peoples time.

Answer 1

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.

Answer 2

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.