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

Problem with autocommit

aniolekx posted 3 years 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 3 years 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 3 years 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 3 years ago
where I open transaction twice?
kalvaro posted 3 years ago
#1

SET autocommit=0;


#2

START TRANSACTION;


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

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