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

Problem with autocommit

aniolekx posted 6 months ago in General
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.
aniolekx posted 6 months ago
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.
kalvaro posted 6 months ago
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.
aniolekx posted 6 months ago
where I open transaction twice?
kalvaro posted 6 months ago
#1

SET autocommit=0;



#2

START TRANSACTION;



aniolekx posted 6 months ago
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



kalvaro posted 6 months ago
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

aniolekx posted 6 months ago
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
aniolekx posted 6 months ago
Please read carefully what i am writing.. otherwise this will be pointless..

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