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

Problem with autocommit

User, date Message
Written by aniolekx
4 months ago
Category: General
7 posts since Fri, 10 Aug 12
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.
Written by aniolekx
4 months ago
7 posts since Fri, 10 Aug 12
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.
Written by kalvaro
4 months ago
590 posts since Thu, 29 Nov 07
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.
Written by aniolekx
4 months ago
7 posts since Fri, 10 Aug 12
where I open transaction twice?
Written by kalvaro
4 months ago
590 posts since Thu, 29 Nov 07
#1

SET autocommit=0;



#2

START TRANSACTION;



Written by aniolekx
4 months ago
7 posts since Fri, 10 Aug 12
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



Written by kalvaro
4 months ago
590 posts since Thu, 29 Nov 07
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

Written by aniolekx
4 months ago
7 posts since Fri, 10 Aug 12
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
Written by aniolekx
4 months ago
7 posts since Fri, 10 Aug 12
Please read carefully what i am writing.. otherwise this will be pointless..
 

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