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 SEQUENCE 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 SEQUENCE CREATE SERVER CREATE TABLE CREATE TRIGGER CREATE USER CREATE VIEW DROP DATABASE DROP EVENT DROP FUNCTION DROP INDEX DROP PROCEDURE DROP ROLE DROP SEQUENCE 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.
Exceptions
These statements do not cause an implicit commit in the following cases:
- CREATE TABLE and DROP TABLE, when the
TEMPORARY
keyword is used.- However, TRUNCATE TABLE causes an implicit commit even when used on a temporary table.
- 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.
- UNLOCK TABLES causes a commit only if a LOCK TABLES was used on non-transactional tables.
- START SLAVE, STOP SLAVE, RESET SLAVE and CHANGE MASTER TO did not cause implicit commits prior to MariaDB 10.0.