Description\n-----------\n\nSome SQL statements cause an implicit commit. As a rule of thumb, such statements are DDL statements. The same statements (except for SHUTDOWN) produce a 1400 error (SQLSTATE 'XAE09') if a XA transaction is in effect.\n\nHere is the list:\n\n``sql\nALTER DATABASE ... UPGRADE DATA DIRECTORY NAME\nALTER EVENT\nALTER FUNCTION\nALTER PROCEDURE\nALTER SEQUENCE\nALTER SERVER\nALTER TABLE\nALTER VIEW\nANALYZE TABLE\nBEGIN\nCACHE INDEX\nCHANGE MASTER TO\nCHECK TABLE\nCREATE DATABASE\nCREATE EVENT\nCREATE FUNCTION\nCREATE INDEX\nCREATE PROCEDURE\nCREATE ROLE\nCREATE SEQUENCE\nCREATE SERVER\nCREATE TABLE\nCREATE TRIGGER\nCREATE USER\nCREATE VIEW\nDROP DATABASE\nDROP EVENT\nDROP FUNCTION\nDROP INDEX\nDROP PROCEDURE\nDROP ROLE\nDROP SEQUENCE\nDROP SERVER\nDROP TABLE\nDROP TRIGGER\nDROP USER\nDROP VIEW\nFLUSH\nGRANT\nLOAD INDEX INTO CACHE\nLOCK TABLES\nOPTIMIZE TABLE\nRENAME TABLE\nRENAME USER\nREPAIR TABLE\nRESET\nREVOKE\nSET PASSWORD\nSHUTDOWN\nSTART SLAVE\nSTART TRANSACTION\nSTOP SLAVE\nTRUNCATE TABLE\n`\n\nSET autocommit = 1 causes an implicit commit if the value was 0.\n\nAll these statements cause an implicit commit before execution. This means that, even if the statement fails with an error, the transaction is committed. Some of them, like CREATE TABLE ... SELECT, also cause a commit immediatly after execution. Such statements couldn't be rollbacked in any case.\n\nIf you are not sure whether a statement has implicitly committed the current transaction, you can query the in_transaction server system variable.\n\nNote that when a transaction starts (not in autocommit mode), all locks acquired with LOCK TABLES are released. And acquiring such locks always commits the current transaction. To preserve the data integrity between transactional and non-transactional tables, the GET_LOCK() function can be used.\n\nExceptions\n\nThese statements do not cause an implicit commit in the following cases:\n\n CREATE TABLE and DROP TABLE, when the TEMPORARY` keyword is used.\n   However, TRUNCATE TABLE causes an implicit commit even when used on a temporary table.\n CREATE FUNCTION and DROP FUNCTION, when used to create a UDF (instead of a stored function). However, CREATE INDEX and DROP INDEX cause commits even when used with temporary tables.\n UNLOCK TABLES causes a commit only if a LOCK TABLES was used on non-transactional tables.\n\nURL: https://mariadb.com/docs/server/reference/sql-statements/transactions/sql-statements-that-cause-an-implicit-commit