SQL statements that cause an implicit commit
Some 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.
Here is the list:
ALTER DATABASE ... UPGRADE DATA DIRECTORY NAME ALTER EVENT ALTER FUNCTION ALTER PROCEDURE ALTER SERVER ALTER TABLE ALTER VIEW ANALYZE TABLE BEGIN CACHE INDEX CHANGE MASTER TO CHECK TABLE CREATE DATABASE CREATE EVENT CREATE FUNCTION CREATE INDEX CREATE PROCEDURE CREATE ROLE CREATE SERVER CREATE TABLE CREATE TRIGGER CREATE USER CREATE VIEW DROP DATABASE DROP EVENT DROP FUNCTION DROP INDEX DROP PROCEDURE DROP ROLE DROP SERVER DROP TABLE DROP TRIGGER DROP USER DROP VIEW FLUSH GRANT LOAD INDEX INTO CACHE LOCK TABLES OPTIMIZE TABLE RENAME TABLE RENAME USER REPAIR TABLE RESET REVOKE SET PASSWORD SHUTDOWN START SLAVE START TRANSACTION STOP SLAVE TRUNCATE TABLE UNLOCK TABLES
SET autocommit = 1
causes an implicit commit if the value was 0.
All these statements cause an implicit commit before execution. Some of them, like CREATE TABLE ... SELECT
, also cause a commit immediatly after execution. Such statements couldn't be rollbacked in any case.
If you are not sure whether a statement has implicitly committed the current transaction, you can query the in_transaction server system variable.
Exceptions
These statements do not cause an implicit commit in the following cases:
CREATE TABLE
,ALTER TABLE
andDROP TABLE
, when theTEMPORARY
keyword is used.CREATE FUNCTION
andDROP FUNCTION
, when used to create a UDF (instead of a stored function). However,CREATE INDEX
andDROP INDEX
cause commits even when used with temporary tables.UNLOCK TABLES
causes a commit only if aLOCK TABLES
was used on non-transactional tables.START SLAVE
,STOP SLAVE
,RESET SLAVE
andCHANGE MASTER TO
only cause implicit commit since MariaDB 10.0.