# SQL statements Causing 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](/docs/server/reference/sql-statements/administrative-sql-statements/shutdown.md)) produce a 1400 error ([SQLSTATE](/docs/server/reference/sql-statements/programmatic-compound-statements/programmatic-compound-statements-diagnostics/sqlstate.md) 'XAE09') if a XA transaction is in effect.

Here is the list:

```sql
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](/docs/server/server-management/variables-and-modes/server-system-variables.md#in_transaction) server system variable.

Note that when a transaction starts (not in autocommit mode), all locks acquired with [LOCK TABLES](/docs/server/reference/sql-statements/transactions/lock-tables.md) 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()](/docs/server/reference/sql-functions/secondary-functions/miscellaneous-functions/get_lock.md) function can be used.

## Exceptions

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

* [CREATE TABLE](/docs/server/server-usage/tables/create-table.md) and [DROP TABLE](/docs/server/server-usage/tables/drop-table.md), when the `TEMPORARY` keyword is used.
  * However, [TRUNCATE TABLE](/docs/server/reference/sql-statements/table-statements/truncate-table.md) causes an implicit commit even when used on a temporary table.
* [CREATE FUNCTION](/docs/server/reference/sql-statements/data-definition/create/create-function.md) and [DROP FUNCTION](/docs/server/server-usage/stored-routines/stored-functions/drop-function.md), when used to create a UDF (instead of a stored function). However, [CREATE INDEX](/docs/server/reference/sql-statements/data-definition/create/create-index.md) and [DROP INDEX](/docs/server/reference/sql-statements/data-definition/drop/drop-index.md) cause commits even when used with temporary tables.
* [UNLOCK TABLES](/docs/server/reference/sql-statements/transactions/lock-tables.md) causes a commit only if a [LOCK TABLES](/docs/server/reference/sql-statements/transactions/lock-tables.md) was used on non-transactional tables.

<sub>*This page is licensed: CC BY-SA / Gnu FDL*</sub>

{% @marketo/form formId="4316" %}


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://mariadb.com/docs/server/reference/sql-statements/transactions/sql-statements-that-cause-an-implicit-commit.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.