distal-attribute
distal-attribute
distal-attribute
distal-attribute

How to cancel auto commit?

User, date Message
Written by janwen
2 years 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 jfalchMoney, Euro
2 years ago
387 posts since Sat, 17 Oct 09
first,

SET @@session.autocommit = 0;
Written by janwen
2 years 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 jfalchMoney, Euro
2 years ago
387 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
2 years ago
10 posts since Wed, 19 Oct 11
server version:5.1.48-log
storage engine:InnoDB
Written by kalvaro
2 years ago
594 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 jfalchMoney, Euro
2 years ago
387 posts since Sat, 17 Oct 09
what is returned by
SELECT @@GLOBAL.tx_isolation, @@tx_isolation;
?
Written by janwen
2 years 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
2 years ago
10 posts since Wed, 19 Oct 11
@jfalch Run

SELECT @@GLOBAL.tx_isolation, @@tx_isolation


get:

REPEATABLE-READ REPEATABLE-READ

Written by kalvaro
2 years ago
594 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
2 years ago
10 posts since Wed, 19 Oct 11
heidisql do not support start transaction manually? anyone else has suggestion?
Written by ansgar
2 years ago
4987 posts since Fri, 07 Apr 06
Please, check again to what value @@autocommit is set:

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

Written by janwen
2 years ago
10 posts since Wed, 19 Oct 11
@anse

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



get:
[b]0[/b]
Written by janwen
2 years 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
2 years ago
4987 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.