autocommit

Overview

For an implicit transaction containing a SQL statement that modifies a table, the autocommit system variable specifies whether commit should be automatic upon successful statement execution.

USAGE

DETAILS

Explicit transactions (which start with BEGIN or START TRANSACTION) are not influenced by autocommit. autocommit influences implicit transactions. An implicit transaction is started when no transaction has been started and a SQL statement is issued which modifies a table.

  • When autocommit is enabled (the default), if the SQL statement is executed successfully, then the implicit transaction is committed automatically.

  • When autocommit is disabled, if the SQL statement is executed successfully, then the implicit transaction remains open until closed. Some ways the transaction can be closed are explicit COMMIT, explicit ROLLBACK, or the start of a new transaction.

  • If SQL statement execution fails, the SQL statement is rolled back.

SYNONYMS

SCHEMA

PARAMETERS

Command-line

--autocommit

Configuration file

Supported

Dynamic

Yes

Scope

Global, Session

Data Type

BOOLEAN (OFF, ON)

Product Default Value

ON

SKYSQL

Cloud

SkySQL Topology

ES Ver

Service Default

AWS

Multi-Node Analytics

10.6

ON

Single Node Analytics

10.6

ON

Replicated Transactions

10.4

ON

10.5

ON

10.6

ON

Single Node Transactions

10.4

ON

10.5

ON

10.6

ON

GCP

Multi-Node Analytics

10.6

ON

Single Node Analytics

10.6

ON

Replicated Transactions

10.4

ON

10.5

ON

10.6

ON

Single Node Transactions

10.4

ON

10.5

ON

10.6

ON

Cloud

SkySQL Topology

ES Ver

Service Default

AWS

ColumnStore Data Warehouse

10.6

ON (choices: OFF, ON)

Enterprise Server With Replica(s)

10.4

ON (choices: OFF, ON)

10.5

ON (choices: OFF, ON)

10.6

ON (choices: OFF, ON)

Enterprise Server Single Node

10.4

ON (choices: OFF, ON)

10.5

ON (choices: OFF, ON)

10.6

ON (choices: OFF, ON)

GCP

ColumnStore Data Warehouse

10.6

ON (choices: OFF, ON)

Enterprise Server With Replica(s)

10.4

ON (choices: OFF, ON)

10.5

ON (choices: OFF, ON)

10.6

ON (choices: OFF, ON)

Enterprise Server Single Node

10.4

ON (choices: OFF, ON)

10.5

ON (choices: OFF, ON)

10.6

ON (choices: OFF, ON)

PRIVILEGES

EXAMPLES

Configuration File Parameter

autocommit is enabled by default.

To disable autocommit:

[mariadbd]
autocommit=0

Session Setting

To disable autocommit:

SET autocommit=0;

To enable autocommit (when disabled):

SET autocommit=1;

Behavior When Enabled

The following behavior has been confirmed to occur, demonstrating behavior with autocommit enabled, when interacting with a table on the InnoDB storage engine or ColumnStore storage engine.

-- Enable autocommit (default).
SET autocommit=1;
-- In an implicit transaction, insert a row, triggering autocommit.
INSERT INTO autocommit_test values (1, 'autocommit=1, implicit transaction autocommit');
-- Begin an explicit transaction.
BEGIN;
-- In an explicit transaction, insert a row.
INSERT INTO autocommit_test values (2, 'autocommit=1, explicit transaction explicit commit');
-- Commit the transaction.
COMMIT;
-- Begin an explicit transaction.
BEGIN;
-- In an explicit transaction, insert a row.
INSERT INTO autocommit_test values (3, 'autocommit=1, explicit transaction explicit rollback');
-- Explicitly rollback the transaction.
-- Row with id=3 will not appear in final output.
ROLLBACK;
-- In an implicit transaction, insert a row, triggering autocommit.
INSERT INTO autocommit_test values (4, 'autocommit=1, implicit transaction autocommit');
-- In an implicit transaction, insert a row, triggering autocommit.
INSERT INTO autocommit_test values (5, 'autocommit=1, implicit transaction autocommit');
-- Perform rollback.
-- There is no effect since prior transactions were autocommitted.
ROLLBACK;
-- Display the resulting table.
SELECT * FROM autocommit_test;
+----+----------------------------------------------------+
| id | summary                                            |
+----+----------------------------------------------------+
|  1 | autocommit=1, implicit transaction autocommit      |
|  2 | autocommit=1, explicit transaction explicit commit |
|  4 | autocommit=1, implicit transaction autocommit      |
|  5 | autocommit=1, implicit transaction autocommit      |
+----+----------------------------------------------------+

Behavior When Disabled

The following behavior has been confirmed to occur, demonstrating behavior with autocommit disabled, when interacting with a table on the InnoDB storage engine or ColumnStore storage engine.

-- Disable autocommit.
SET autocommit=0;
-- In an implicit transaction, insert a row.
INSERT INTO autocommit_test values (6, 'autocommit=0, implicit transaction commit by next transaction start');
-- Prior implicit transaction is committed when the next transaction is started.
BEGIN;
-- Perform rollback.
-- There is no effect since the prior transaction was committed.
ROLLBACK;
-- Begin a new explicit transaction.
BEGIN;
-- In an explicit transaction, insert a row.
INSERT INTO autocommit_test values (7, 'autocommit=0, explicit transaction explicit commit');
-- Explicitly commit the explicit transaction.
COMMIT;
-- Start an explicit transaction.
BEGIN;
-- In an explicit transaction, insert a row.
INSERT INTO autocommit_test values (8, 'autocommit=0, explicit transaction rolled back');
-- Perform explicit rollback of the explicit transaction.
-- Row with id=8 will not appear in output.
ROLLBACK;
-- In an implicit transaction, insert a row.
INSERT INTO autocommit_test values (9, 'autocommit=0, implicit transaction rolled back');
-- In the same implicit transaction, insert another row.
INSERT INTO autocommit_test values (10, 'autocommit=0, implicit transaction rolled back');
-- Perform explicit rollback of the implicit transaction.
-- Rows with id=9 and id=10 will not appear in output.
ROLLBACK;
-- Display the resulting table
SELECT * FROM autocommit_test WHERE id>5;
+----+---------------------------------------------------------------------+
| id | summary                                                             |
+----+---------------------------------------------------------------------+
|  6 | autocommit=0, implicit transaction commit by next transaction start |
|  7 | autocommit=0, explicit transaction explicit commit                  |
+----+---------------------------------------------------------------------+

ERROR HANDLING

FEATURE INTERACTION

Non-Transactional Storage Engines

While InnoDB and ColumnStore support transactions, some MariaDB Server storage engines do not. One example of a non-transactional storage engine is Aria.

When using non-transactional storage engines, autocommit behavior is not relevant.

With engine=Aria, executing the "Behavior When Enabled" results in:

+----+------------------------------------------------------+
| id | summary                                              |
+----+------------------------------------------------------+
|  1 | autocommit=1, implicit transaction autocommit        |
|  2 | autocommit=1, explicit transaction explicit commit   |
|  3 | autocommit=1, explicit transaction explicit rollback |
|  4 | autocommit=1, implicit transaction autocommit        |
|  5 | autocommit=1, implicit transaction autocommit        |
+----+------------------------------------------------------+

During execution, the attempt to ROLLBACK will generate a warning:

+---------+------+---------------------------------------------------------------+
| Level   | Code | Message                                                       |
+---------+------+---------------------------------------------------------------+
| Warning | 1196 | Some non-transactional changed tables couldn't be rolled back |
+---------+------+---------------------------------------------------------------+

Enterprise Cluster

With MariaDB Enterprise Cluster, powered by Galera, failed autocommit transactions are automatically retried when wsrep_retry_autocommit is enabled.

RESPONSES

DIAGNOSIS

ISO 9075:2016

CHANGE HISTORY

Release Series

History

11.4 Enterprise

  • Not present.

10.6 Enterprise

  • Present starting in MariaDB Enterprise Server 10.6.4-1.

10.6 Community

  • Present starting in MariaDB Community Server 10.6.0.

10.5 Enterprise

  • Present starting in MariaDB Enterprise Server 10.5.3-1.

10.5 Community

  • Present starting in MariaDB Community Server 10.5.0.

10.4 Enterprise

  • Present starting in MariaDB Enterprise Server 10.4.6-1.

10.4 Community

  • Present starting in MariaDB Community Server 10.4.0.

10.3 Enterprise

  • Present starting in MariaDB Enterprise Server 10.3.16-1.

10.3 Community

  • Present starting in MariaDB Community Server 10.3.0.

10.2 Enterprise

  • Present starting in MariaDB Enterprise Server 10.2.25-1.

10.2 Community

  • Present starting in MariaDB Community Server 10.2.0.

Release Series

History

11.4 Enterprise

  • Not present.

10.6 Enterprise

  • Present starting in MariaDB Enterprise Server 10.6.4-1.

10.5 Enterprise

  • Present starting in MariaDB Enterprise Server 10.5.3-1.

10.4 Enterprise

  • Present starting in MariaDB Enterprise Server 10.4.6-1.

EXTERNAL REFERENCES