Problem with autocommit

In following example:

DROP TABLE test;
CREATE TABLE test (
id VARCHAR(10) PRIMARY KEY
) ENGINE=InnoDB;

SET autocommit=0;
START TRANSACTION;
insert test values ('test1');
COMMIT;
SET autocommit=0;
START TRANSACTION;
insert test values ('test2');
insert test values ('test1');
ROLLBACK;

insert with value test2 is committed to database, is it correct behavior?

This behavior cases to me a lot of problems, mainly I can't run anything in transaction because if there is an error part of transaction before error is committed.

I have did some more test and I have noticed that transaction is still open and I had to run ROLLBACK as separate query to finish it, this is really strange.

You are opening the transaction twice (both commands are roughly equivalent). MySQL does not support nested transactions and does the worse thing (as usual):

Beginning a transaction causes any pending transaction to be committed. See Section 13.3.3, “Statements That Cause an Implicit Commit”, for more information.

where I open transaction twice?

#1

SET autocommit=0;

#2

START TRANSACTION;

Ok I have updated my example, issue is still the same, please test this example uder HeidiSQL

CREATE TABLE test (
id VARCHAR(10) PRIMARY KEY
) ENGINE=InnoDB;

START TRANSACTION;
insert test values ('test1');
COMMIT;
START TRANSACTION;
insert test values ('test2');
insert test values ('test1');
ROLLBACK;

Then after error appear please run:

SELECT * FROM test

Select will return 2 rows:

test1
test2

I get this when I run your code:

[Window Title]
Error
[Content]
SQL Error (1062): Duplicate entry 'test1' for key 'PRIMARY'
[OK]
[Footer]
Find some help on this error

Ok and now just after this error please run:

SELECT * FROM test

you will notice that value test2 has been inserted, and transaction still holds lock

Please read carefully what i am writing.. otherwise this will be pointless..