MySQL innoDB transactions

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Skittlewidth
Forum Contributor
Posts: 389
Joined: Wed Nov 06, 2002 9:18 am
Location: Kent, UK

MySQL innoDB transactions

Post 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]
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

auto-commit is enabled.
User avatar
Skittlewidth
Forum Contributor
Posts: 389
Joined: Wed Nov 06, 2002 9:18 am
Location: Kent, UK

Post by Skittlewidth »

I see. How can I disable it?

edit: don't worry I got it: SET AUTOCOMMIT = 0;
waskelton4
Forum Contributor
Posts: 132
Joined: Mon Sep 09, 2002 6:42 pm

Post 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
Last edited by waskelton4 on Tue Oct 25, 2005 9:08 am, edited 1 time in total.
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Post 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.
waskelton4
Forum Contributor
Posts: 132
Joined: Mon Sep 09, 2002 6:42 pm

Post 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..
Post Reply