The Ultimate Guide to High Availability with MariaDB

SQL statements that cause an implicit commit

You are viewing an old version of this article. View the current version here.

Some SQL statements cause an implicit commit. As a rule of thumb, such statements are DDL statements. 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 SERVER
CREATE TABLE
CREATE TRIGGER
CREATE USER
CREATE VIEW
DROP DATABASE
DROP EVENT
DROP FUNCTION
DROP INDEX
DROP PROCEDURE
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
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.

Exceptions

These statements do not cause an implicit commit in the following cases:

  • CREATE TABLE, ALTER TABLE and DROP TABLE, when the TEMPORARY keyword is used.
  • 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, CHANGE MASTER TO only cause explicit commit since MariaDB 10.

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.