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
SET autocommit = 1 causes an implicit commit if the value was 0.
All 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.
If you are not sure whether a statement has implicitly committed the current transaction, you can query the in_transaction server system variable.
Note 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.
These statements do not cause an implicit commit in the following cases:
DROP TABLE, when the
TEMPORARYkeyword is used.
- However, TRUNCATE TABLE causes an implicit commit even when used on a temporary table.
DROP FUNCTION, when used to create a UDF (instead of a stored function). However,
DROP INDEXcause commits even when used with temporary tables.
UNLOCK TABLEScauses a commit only if a
LOCK TABLESwas used on non-transactional tables.
CHANGE MASTER TOonly cause implicit commit since MariaDB 10.0.