mysqldmlautocommit

Why can I use INSERT without any START TRANSACTION nor COMMIT/ROLLBACK with autocommit = 0 in mysql?


So I am trying to understand more about the autocommit statement in MySQL and I am having some trouble understanding why, after I use SET autocommit = 0; and try to run an INSERT without any START TRANSACTION and no COMMIT/ROLLBACK at the end it still inserts it into the table?

For what I understood, if the autocommit is equal to 1, it will commit automatically therefore it would make sense, but I set my autocommit to zero, therefore if I dont use any START TRANSACTION with COMMIT or ROLLBACK block surronding the insert it shouldnt work right?

I know there are some DML that commit automatically and i know that DDL statements do it as well, but in this case INSERT seems not to be any of the DML statements that does that.

The code goes as follow:

SET autocommit = 0;
SELECT @@autocommit; --It returns 0

-- No START TRANCATION
INSERT INTO example(first_name, last_name) VALUES ('TEST', 'TSET');
-- No COMMIT nor ROLLBACK

Any ideas?

Thanks in advance!


Solution

  • You see these rows in current session - but you'll not see them from another session.

    Look:

    enter image description here

    Compare timestamps...