What is Autocommit in MySQL: Explanation and Examples

In MySQL, autocommit is a mode that automatically saves every single SQL statement as soon as it finishes. When autocommit is enabled, you don't need to manually commit transactions because each statement is treated as a complete transaction.

How It Works

Imagine you are writing a letter and every sentence you write is immediately sent to the recipient without waiting for the whole letter to be finished. This is similar to how autocommit works in MySQL. When autocommit is ON, each SQL command you run is instantly saved to the database.

Normally, databases use transactions to group multiple commands together so they either all succeed or all fail. But with autocommit enabled, each command is its own transaction. This means changes are permanent right after the command runs, without needing a separate COMMIT command.

Example

This example shows how autocommit works by default in MySQL. Each INSERT is saved immediately without needing a COMMIT.

sql

SET autocommit = 1;

CREATE TABLE IF NOT EXISTS users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50)
);

INSERT INTO users (name) VALUES ('Alice');
INSERT INTO users (name) VALUES ('Bob');

SELECT * FROM users;

Output

id | name
---|-------
1  | Alice
2  | Bob

When to Use

Use autocommit when you want simple, quick changes without worrying about grouping multiple commands. It is great for small, independent operations like logging events or inserting single records.

However, if you need to make several changes that must all succeed or fail together (like transferring money between accounts), turn autocommit off and use explicit transactions with START TRANSACTION, COMMIT, and ROLLBACK.

Key Points

• Autocommit ON means every SQL statement is saved immediately.
• It simplifies operations but removes control over grouping multiple commands.
• You can toggle autocommit with SET autocommit = 0; or SET autocommit = 1;.
• Turning autocommit OFF lets you manually control transactions.

Key Takeaways

Autocommit mode saves each SQL statement immediately as a separate transaction.

It is enabled by default in MySQL for simple and fast operations.

Turn off autocommit to group multiple statements into one transaction.

Use explicit transactions when you need all-or-nothing changes.

You can toggle autocommit with the SET command.