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
');
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.
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.
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.
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.