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

How to cancel auto commit?

janwen posted 2 years ago in General
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 posted 2 years ago
first,

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

SELECT @@GLOBAL.tx_isolation, @@tx_isolation


get:

REPEATABLE-READ REPEATABLE-READ

kalvaro posted 2 years ago
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 posted 2 years ago
heidisql do not support start transaction manually? anyone else has suggestion?
ansgar posted 2 years ago
Please, check again to what value @@autocommit is set:

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

janwen posted 2 years ago
@anse

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



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