How to cancel auto commit?

janwen's profile image janwen posted 12 years ago in General Permalink
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
jfalch's profile image jfalch posted 12 years ago Permalink
first,

SET @@session.autocommit = 0;
janwen's profile image janwen posted 12 years ago Permalink
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.
jfalch's profile image jfalch posted 12 years ago Permalink
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.
janwen's profile image janwen posted 12 years ago Permalink
server version:5.1.48-log
storage engine:InnoDB
kalvaro's profile image kalvaro posted 12 years ago Permalink
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;
jfalch's profile image jfalch posted 12 years ago Permalink
what is returned by
SELECT @@GLOBAL.tx_isolation, @@tx_isolation;
?
janwen's profile image janwen posted 12 years ago Permalink
@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.
janwen's profile image janwen posted 12 years ago Permalink
@jfalch Run
SELECT @@GLOBAL.tx_isolation, @@tx_isolation

get:
REPEATABLE-READ REPEATABLE-READ
kalvaro's profile image kalvaro posted 12 years ago Permalink
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!
janwen's profile image janwen posted 12 years ago Permalink
heidisql do not support start transaction manually? anyone else has suggestion?
ansgar's profile image ansgar posted 12 years ago Permalink
Please, check again to what value @@autocommit is set:
set @@autocommit:=0;
select @@autocommit;
janwen's profile image janwen posted 12 years ago Permalink
@anse
set @@autocommit:=0;
select @@autocommit;


get:
[b]0[/b]
janwen's profile image janwen posted 12 years ago Permalink
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.
ansgar's profile image ansgar posted 12 years ago Permalink
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.
[expired user #8738]'s profile image [expired user #8738] posted 9 years ago Permalink
Thanks ansgar!

Please, check again to what value @@autocommit is set:

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



worked perfectly.

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