Page 1 of 1

MySQL innoDB transactions

Posted: Fri Aug 12, 2005 8:14 am
by Skittlewidth
I thought I'd learn about MySQL's transaction handling capabilities so I searched for a tutorial and began to work through this one:

http://www.databasejournal.com/features ... hp/3382171

I typed in the following example:

Code: Select all

Let's try looking from a different connection. For this exercise, open two connections to the database. 

Connection 1: 
mysql> CREATE TABLE t (f INT) TYPE=InnoDB;

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO t (f) VALUES (1);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM t;
+------+
| f    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)


Connection 2: 

mysql> SELECT * FROM t;
Empty set (0.02 sec) 
The important point is that running the same query from different connections (one within the middle of a transaction, the other from without) produces different results. Now, commit the transaction from the first connection, and run the query again from connection 2.
But both my connections return the same data, without having told MySQL to COMMIT. I've checked that my connection IDs were different when I was testing on my local mysql installation, and also tried it from separate machines on a server installation, but the second connection always returns the inserted data. Is there something else I need to get MySQL tranactions to work? I'm using 4.1.11-nt on my local machine and my server tests have run on 4.0.24.

[edit] Do I need to install something extra to make InnoDB work? [/edit]

Posted: Fri Aug 12, 2005 8:20 am
by feyd
auto-commit is enabled.

Posted: Fri Aug 12, 2005 8:21 am
by Skittlewidth
I see. How can I disable it?

edit: don't worry I got it: SET AUTOCOMMIT = 0;

Posted: Mon Oct 24, 2005 1:48 pm
by waskelton4
another question regarding this..

I've been trying to update a table of users that i have .. updating some of them and some inserting new ones..

I'm trying to update it using transactions through php..

here is a very simplified chunk of what i'm trying to do..

Code: Select all

mysql_connect(....);
mysql_query("Start Transaction;");

while($i=0;$i<10;$i++) {
   if($i > 5) {
        $sql = "UPDATE  tblUsers SET userLevel = '1';"; 
   }
   else {
       $sql = "INSERT INTO tblUsers (name, userLevel) SET ('$name', '2');";
   }

  if(mysql_query($sql)) {
      $commit = 1;
   }
   else {
      $commit = 0;
   }

}

$commit = 0;
if($commit == 1) {
	print mysql_query("commit;") ? "Transaction Committed.<br>" : "Commit for Transaction Failed";
}
else {
	print mysql_query("rollback;") ? "Transaction Rolledback.<br>" : "Rollback for Transaction Failed";
}
Thats pretty rough.. but it gets mypoint accross.. i hope

anyway.. as you can see.. i've set $commit equal to zero right before the code that determines whether to rollback or commit so it rollsback everytime.
It is printing that it is rolledback everytime however the table tells differently.. it seems that it's all being comitted..

Is this because autocommit is set to 1?

and... if so.. if i change it to 0 to make it work.. will all my other sql statements break and not be committed?

help is greatly appreciated..

Thanks
Will

Posted: Tue Oct 25, 2005 3:45 am
by Weirdan

Posted: Tue Oct 25, 2005 5:00 am
by onion2k
I was playing with InnoDB transactions the other day ..

Code: Select all

define("DATABASE_HOST","localhost");
	define("DATABASE_USER","root");
	define("DATABASE_PASSWORD","password");
	define("PRIMARY_DATABASE_NAME","innodbtest");
	define("TABLE_PREFIX","inno_");

	$databaseLink = mysql_connect(DATABASE_HOST,DATABASE_USER,DATABASE_PASSWORD);
	mysql_select_db(PRIMARY_DATABASE_NAME, $databaseLink);

	$sql = "SET AUTO_COMMIT = 0;";
	mysql_query($sql,$databaseLink);

	mysql_query("START TRANSACTION;",$databaseLink);
	$sql = "insert into ".TABLE_PREFIX."table (first_name,last_name,email,created) values ('Chris','Nea'le','chris.neale@highford.com',NOW());";
	if (mysql_query($sql,$databaseLink)) {
		mysql_query("COMMIT;",$databaseLink);
	} else {
		mysql_query("ROLLBACK;",$databaseLink);
	}

	mysql_query("START TRANSACTION;",$databaseLink);
	$sql = "insert into ".TABLE_PREFIX."table (first_name,last_name,email,created) values ('Dan','Robinson','dan.robinson@highford.com',NOW());";
	if (mysql_query($sql,$databaseLink)) {
		mysql_query("COMMIT;",$databaseLink);
	} else {
		mysql_query("ROLLBACK;",$databaseLink);
	}

	$sql = "select * from ".TABLE_PREFIX."table;";
	$result = mysql_query($sql,$databaseLink);
	while ($record = mysql_fetch_object($result)) {
		echo "- ".$record->first_name." - ".$record->last_name." - ".$record->email." - ".$record->created."<br>";
	}
As you can see, in the first insert statement there's a mistake in the data (an unescaped quote). That transaction gets rolled back. The second one is fine is it gets commited. Pretty basic example really .. but it works.

Posted: Tue Oct 25, 2005 9:16 am
by waskelton4
from that link..
If the connection has autocommit enabled, the user can still perform a multiple-statement transaction by starting it with an explicit START TRANSACTION or BEGIN statement and ending it with COMMIT or ROLLBACK.

This is my case.. and with my two statements at the bottom determining by the $commit flag whether or not to commit or rollback it tells me that the statement is being rolled back each time i run it since i have explicitly set $commit = 1; right before that logic.

The problem is that it is in fact NOT being rolled back and it is being comitted everytime.

Did i miss something in the documentation or did I read it wrong?(both quite possible) I think my code is setup correctly but It isn't acting like I think it is supposed to :)


also.. fwiw..

webserver:
Windows 2003 Server
IIS 6
php ver = 4.3.7
from phpinfo()
MySQL
Active Persistent Links 0
Active Links 0
Client API version 3.23.49


DB Server:
mysql ver= 4.1.10a nt

on seperate boxes..