Difference between set autocommit=0 and set session transaction isolation level REPEATABLE READ;start transaction;?

Question

It is mentioned here that default isolation level for any transaction in innodb is repeatable read. In the same link above it is also mention that if I do set autocommit=0 then session always have transaction open. Now My undersatnding is if I do

set session transaction isolation level REPEATABLE READ;

start transaction;

DML(insert, update, delete) queries

commit;

and

 set autocommit=0;

    DML(insert, update, delete) queries

    commit;

Both should do the same, should run in repeatable read isolation level. I need to do commit or rollback in the end.

But as I use them for lot of queries the first one take lot of time and second one takes very little time. So clearly they aren't same. What I am missing? what is the default isolation level of transaction while auto commit is set to 0? Thanks in advance.

EDIT:I got the answer of what is default isolation level if I do set autocommit=0;(Thanks to kostja) It can be seen from show variables like "%isolation"; But I am yet to find out why first one is slower.

Kostja Kostja · Accepted Answer · 2015-04-13T18:07:05

Setting autocommit in the session doesn't change it globally. Sure it's not the case?

Answering your question, transaction isolation level doesn't depend on autocommit.

Difference between set autocommit=0 and set session transaction isolation level REPEATABLE READ;start transaction;?

1 Answers