auto_commit is still enabled although I prepend "START TRANSACTION" in mysql-CodePudding

When I check the official documentation of MySQL or MariaDB,
they say that auto_commit is disabled when START TRANSACTION is used.
But In my code It seemed not properly working. Did I do something wrong? Any Help would be appreciated!


SELECT @@autocommit; -- 1 (enabled)
START TRANSACTION;
SELECT @@autocommit; -- 1 (I thought it should be zero.)
COMMIT;

CodePudding user response：

Yes, autocommit is disabled. But the option value is not altered.

thread 1                                 |  thread 2
=========================================|==============================================
SELECT @@autocommit; -- 1 (enabled)      |
START TRANSACTION;                       |
SELECT @@autocommit; -- 1                |
    -- but autocommit is disabled !!!    |
INSERT INTO test (id) SELECT 1;          |
                                         |  SELECT * FROM test;   -- empty output
COMMIT;                                  |
                                         |  SELECT * FROM test;   -- row id=1 returned

The setting defines does the autocommit is applied out of the transaction.