How to Set Autocommit in MySQL: Syntax and Examples

In MySQL, you can set autocommit mode using the SET autocommit = 0 to disable or SET autocommit = 1 to enable it. Autocommit mode controls whether each SQL statement is committed automatically or requires an explicit commit.

Syntax

The SET autocommit statement controls the autocommit mode in MySQL. Use SET autocommit = 1 to enable autocommit, which means every SQL statement is committed immediately. Use SET autocommit = 0 to disable autocommit, so you must manually commit transactions.

sql

SET autocommit = 0;  -- Disable autocommit
SET autocommit = 1;  -- Enable autocommit

Example

This example shows disabling autocommit, running multiple statements, and then committing manually. It demonstrates how changes are not saved until COMMIT is called.

sql

SET autocommit = 0;

INSERT INTO employees (name, position) VALUES ('Alice', 'Developer');
INSERT INTO employees (name, position) VALUES ('Bob', 'Designer');

-- Changes are not saved yet
COMMIT;  -- Save changes

-- Now enable autocommit again
SET autocommit = 1;

INSERT INTO employees (name, position) VALUES ('Charlie', 'Manager');

Output

Query OK, 0 rows affected (0.00 sec)
Query OK, 1 row affected (0.01 sec)
Query OK, 1 row affected (0.01 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 1 row affected (0.01 sec)

Common Pitfalls

One common mistake is forgetting to commit changes when autocommit is disabled, which causes data not to be saved. Another is assuming autocommit is off by default; in MySQL, autocommit is enabled by default. Also, changing autocommit inside a transaction has no effect until the transaction ends.

sql

/* Wrong: Disable autocommit but forget to commit */
SET autocommit = 0;
INSERT INTO employees (name, position) VALUES ('Diana', 'Analyst');
-- No COMMIT here, so changes are lost when session ends

/* Right: Commit changes explicitly */
COMMIT;

Quick Reference

Command	Description
SET autocommit = 1;	Enable autocommit mode (default)
SET autocommit = 0;	Disable autocommit mode
COMMIT;	Save all changes made in the current transaction
ROLLBACK;	Undo all changes made in the current transaction

Key Takeaways

Use SET autocommit = 1 to enable automatic commits after each statement.

Use SET autocommit = 0 to disable autocommit and control transactions manually.

Always use COMMIT to save changes when autocommit is disabled.

MySQL autocommit is enabled by default unless changed.

Changing autocommit inside a transaction takes effect only after the transaction ends.