Auto-commit behavior in SQL

Introduction

Auto-commit makes sure each change you make to the database is saved right away without needing extra steps.

When you want every change to be saved immediately after you run a command.

When you are running simple queries and don't need to group multiple changes together.

When you want to avoid losing data if your program stops unexpectedly.

When you are testing small changes and want to see results right away.

When you do not need to undo changes after running commands.

Syntax

SQL

SET autocommit = {0 | 1};

Use 1 to turn auto-commit ON, so changes save automatically.

Use 0 to turn auto-commit OFF, so you must manually save changes with COMMIT.

Examples

Turn auto-commit ON so every change saves immediately.

SQL

SET autocommit = 1;

Turn auto-commit OFF to control when changes save.

SQL

SET autocommit = 0;

With auto-commit ON, this change saves right away.

SQL

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

With auto-commit OFF, you must run COMMIT to save changes.

SQL

UPDATE users SET name = 'Bob' WHERE id = 1; COMMIT;

Sample Program

This example shows that before COMMIT, the new user is visible in the current session but not saved permanently. After COMMIT, the change is saved.

SQL

SET autocommit = 0;
INSERT INTO users (name) VALUES ('Charlie');
SELECT * FROM users WHERE name = 'Charlie';
COMMIT;
SELECT * FROM users WHERE name = 'Charlie';

Important Notes

Auto-commit behavior can differ between database systems; always check your system's defaults.

Turning auto-commit OFF lets you group many changes and save them all at once, which can be safer.

Remember to COMMIT your changes when auto-commit is OFF, or your changes will be lost.

Summary

Auto-commit saves each change immediately without extra commands.

You can turn auto-commit ON or OFF using SET autocommit.

When OFF, use COMMIT to save your changes manually.