How to cancel auto commit?

User, date Message
Written by janwen
10 months ago
Category: General
10 posts since Wed, 19 Oct 11 		I　want to commit manually.How can i do that not auto commit the transaction.I want to test the mysql transaction and isolation like:
begin;
select count(*) from beiker_region_property
where id=10100100 for update;
commit;

thanks
Written by jfalch
10 months ago
241 posts since Sat, 17 Oct 09 		first,

SET @@session.autocommit = 0;
Written by janwen
10 months ago
10 posts since Wed, 19 Oct 11 		thanks jfalch for your replay,but not work for me:
I try your solution:
set @@session.autocommit=0;
begin;
select count(*) from beiker_region_property
where id=10100100 for update;
commit;

not working,just get the executed result without executing the commit.
Written by jfalch
10 months ago
241 posts since Sat, 17 Oct 09 		1) try set @@autocommit = 0;

2) what server version are you using ?

3) you know that autocommit has an effect only if the storage engine used for your table(s) supports transactions ? InnoDB does (as well as ndbcluster, PBXT, SolidDB); MyISAM does NOT, so setting autocommit (or anything else) will not have an effect with MyISAM.
Written by janwen
10 months ago
10 posts since Wed, 19 Oct 11 		server version:5.1.48-log
storage engine:InnoDB
Written by kalvaro
10 months ago
457 posts since Thu, 29 Nov 07 		HeidiSQL does not have a GUI for it but you can always run the appropriate SQL commands:

start transaction;
select count(*) from beiker_region_property
where id=10100100 for update;



... and finally:

commit;



... or:

rollback;
Written by jfalch
10 months ago
241 posts since Sat, 17 Oct 09 		what is returned by
SELECT @@GLOBAL.tx_isolation, @@tx_isolation;
?
Written by janwen
10 months ago
10 posts since Wed, 19 Oct 11 		@kalvaro i tried your solution,not work.
1.

start transaction;
select count(*) from beiker_region_property
where id=10100100 for update;


and then run above in another query tab
2.

start transaction;
select count(*) from beiker_region_property
where id=10100100 for update;



In the second query,i get the query result immediately,but i did not run commit command.
Written by janwen
10 months ago
10 posts since Wed, 19 Oct 11 		@jfalch Run

SELECT @@GLOBAL.tx_isolation, @@tx_isolation


get:

REPEATABLE-READ REPEATABLE-READ
Written by kalvaro
10 months ago
457 posts since Thu, 29 Nov 07 		Er, you cannot nest transactions in MySQL. If you attempt to start a new one, MySQL will make an implicit commit. Whatever, the

SET @@session.autocommit = 0;

tecnique jfalch explained should do the trick, if you are using InnoDB tables. There's no way to get transactions with MyISAM!
Written by janwen
10 months ago
10 posts since Wed, 19 Oct 11 		heidisql do not support start transaction manually? anyone else has suggestion?
Written by ansgar
10 months ago
4027 posts since Fri, 07 Apr 06 		Please, check again to what value @@autocommit is set:

set @@autocommit:=0;
select @@autocommit;
Written by janwen
10 months ago
10 posts since Wed, 19 Oct 11 		@anse

set @@autocommit:=0;
select @@autocommit;



get:
[b]0[/b]
Written by janwen
10 months ago
10 posts since Wed, 19 Oct 11 		Where can i set the autocommit to false in the heidisql? It seemed user can set autocommit to false in the heidisql in the previous version.But i can not find the settings anywhere now.
Written by ansgar
10 months ago
4027 posts since Fri, 07 Apr 06 		You just did it, by firing "set @@autocommit:=0", successfully, as your reply reveals. That should do it, on InnoDB tables, not on MyISAM tables, as said above.
 

Please login to leave a reply, or register at first.