Auto-commit behavior in SQL - Step-by-Step Execution

Concept Flow - Auto-commit behavior

Start Transaction

↓

Execute SQL Statement

↓

Auto-commit ON?

No→Wait for COMMIT or ROLLBACK

Yes↓

Automatically COMMIT changes

↓

End Transaction

This flow shows how SQL statements are automatically committed if auto-commit is ON, otherwise manual commit or rollback is needed.

Execution Sample

SQL

INSERT INTO users (id, name) VALUES (1, 'Alice');
SELECT * FROM users;
-- Auto-commit ON by default

This example inserts a row and immediately commits it because auto-commit is ON.

Execution Table

Step	Action	Auto-commit Status	Transaction State	Result
1	Start session	ON	No active transaction	Ready for statements
2	Execute INSERT	ON	Implicit transaction started	Row inserted
3	Auto-commit triggers	ON	Transaction committed	Changes saved
4	Execute SELECT	ON	No active transaction	Returns inserted row
5	End session	ON	No active transaction	Session closed

💡 Session ends with all changes committed automatically due to auto-commit ON

Variable Tracker

Variable	Start	After Step 2	After Step 3	After Step 4	Final
Transaction State	No active transaction	Implicit transaction started	Transaction committed	No active transaction	No active transaction
Data in users table	Empty	Row with id=1, name='Alice' inserted	Committed row present	Row present	Row present

Key Moments - 2 Insights

Why does the INSERT statement commit immediately without an explicit COMMIT?

Because auto-commit is ON (see execution_table step 3), each statement is committed automatically after execution.

What happens if auto-commit is OFF after executing an INSERT?

The transaction remains open until a COMMIT or ROLLBACK is issued, so changes are not saved immediately (not shown in this trace).

Visual Quiz - 3 Questions

Look at the execution table, at which step does the transaction commit?

A Step 2

B Step 3

C Step 4

D Step 5

💡 Hint

Check the 'Transaction State' column in execution_table row for Step 3

According to variable_tracker, what is the state of the transaction after Step 4?

A Implicit transaction started

B Transaction committed

C No active transaction

D Transaction rolled back

💡 Hint

Look at the 'Transaction State' row in variable_tracker after Step 4

If auto-commit was OFF, what would change in the execution table?

A Step 3 would show 'Waiting for COMMIT or ROLLBACK'

B Step 3 would show 'Transaction committed' as usual

C Step 2 would not execute the INSERT

D Step 4 would fail

💡 Hint

Refer to the concept_flow where auto-commit OFF leads to waiting for manual commit

Concept Snapshot

Auto-commit behavior in SQL means each statement is committed immediately after execution if auto-commit is ON.
If auto-commit is OFF, changes stay uncommitted until COMMIT or ROLLBACK.
Most SQL clients have auto-commit ON by default.
Use transactions explicitly to group multiple statements.
Auto-commit ensures data changes are saved without manual commit.

Full Transcript

Auto-commit behavior in SQL means that when auto-commit is ON, each SQL statement runs in its own transaction and commits automatically after execution. This means changes like INSERT, UPDATE, or DELETE are saved immediately without needing a separate COMMIT command. The execution trace shows starting a session with auto-commit ON, executing an INSERT which triggers an implicit transaction, then auto-commit commits the transaction automatically. A subsequent SELECT shows the inserted row. The transaction state changes from no active transaction to implicit transaction started, then to committed, and back to no active transaction. If auto-commit were OFF, the transaction would remain open after the INSERT until a manual COMMIT or ROLLBACK is issued. This behavior is important to understand for controlling when data changes become permanent.

Practice

1. What does auto-commit mode do in a SQL database?

easy

A. Prevents any changes from being saved to the database

B. Requires manual saving of changes after multiple statements

C. Automatically saves each SQL statement immediately after execution

D. Only saves changes when the database server restarts

Solution

1. Step 1: Understand auto-commit behavior

   Auto-commit means every SQL statement is saved immediately without needing extra commands.

2. Step 2: Compare options with definition

   Automatically saves each SQL statement immediately after execution matches this behavior exactly, others describe manual or no saving.

3. Final Answer:

   Automatically saves each SQL statement immediately after execution -> Option C

4. Quick Check:

   Auto-commit = immediate save [OK]

Hint: Auto-commit means no manual save needed after each statement [OK]

Common Mistakes:

• Thinking auto-commit requires manual COMMIT
• Confusing auto-commit with rollback behavior
• Assuming changes save only on server restart

2. Which SQL command turns off auto-commit mode in most databases?

easy

A. COMMIT;

B. SAVEPOINT;

C. ROLLBACK;

D. SET AUTOCOMMIT = OFF;

Solution

1. Step 1: Identify command to disable auto-commit

   Most databases use SET AUTOCOMMIT = OFF; to disable auto-commit mode.

2. Step 2: Understand other commands

   COMMIT saves changes, ROLLBACK undoes changes, SAVEPOINT marks a point in a transaction; none disable auto-commit.

3. Final Answer:

   SET AUTOCOMMIT = OFF; -> Option D

4. Quick Check:

   Disable auto-commit = SET AUTOCOMMIT = OFF [OK]

Hint: SET AUTOCOMMIT = OFF disables auto-commit [OK]

Common Mistakes:

• Using COMMIT or ROLLBACK to disable auto-commit
• Confusing SAVEPOINT with auto-commit control
• Omitting the SET keyword

3. Consider this sequence with auto-commit OFF:
INSERT INTO users (id, name) VALUES (1, 'Alice');
ROLLBACK;
SELECT * FROM users WHERE id = 1;
What will the SELECT query return?

medium

A. A row with id=1 and name='Alice'

B. No rows

C. Syntax error

D. All rows in users table

Solution

1. Step 1: Analyze the INSERT and ROLLBACK with auto-commit OFF

   With auto-commit OFF, the INSERT is not saved until COMMIT. ROLLBACK undoes the INSERT.

2. Step 2: Determine SELECT result after rollback

   Since the INSERT was undone, the SELECT finds no row with id=1.

3. Final Answer:

   No rows -> Option B

4. Quick Check:

   ROLLBACK undoes changes, so SELECT returns no rows [OK]

Hint: ROLLBACK undoes uncommitted changes, so no new rows appear [OK]

Common Mistakes:

• Assuming INSERT is saved without COMMIT
• Thinking ROLLBACK only affects future queries
• Expecting syntax error from ROLLBACK

4. You run these commands with auto-commit OFF:
UPDATE products SET price = price * 0.9 WHERE category = 'Books';
COMMIT;
ROLLBACK;
What is the state of the prices after these commands?

medium

A. Prices are updated with 10% discount

B. Prices remain unchanged

C. Syntax error due to ROLLBACK after COMMIT

D. Partial update applied

Solution

1. Step 1: Understand effect of UPDATE and COMMIT

   UPDATE changes prices, COMMIT saves these changes permanently.

2. Step 2: Analyze ROLLBACK after COMMIT

   ROLLBACK after COMMIT has no effect because changes are already saved.

3. Final Answer:

   Prices are updated with 10% discount -> Option A

4. Quick Check:

   COMMIT saves changes; ROLLBACK after does nothing [OK]

Hint: COMMIT saves changes; ROLLBACK after commit does nothing [OK]

Common Mistakes:

• Thinking ROLLBACK undoes committed changes
• Assuming partial updates without commit
• Confusing syntax error with command order

5. You want to insert multiple rows into a table but only save them if all inserts succeed. Which approach correctly uses auto-commit behavior?

hard

A. Turn auto-commit OFF, insert all rows, then COMMIT if no errors

B. Keep auto-commit ON and insert rows one by one without COMMIT

C. Use ROLLBACK before inserting rows to save them all

D. Insert rows and rely on auto-commit to undo on error automatically

Solution

1. Step 1: Understand transactional control with auto-commit OFF

   Turning auto-commit OFF lets you group multiple inserts into one transaction.

2. Step 2: Use COMMIT to save only if all inserts succeed

   After all inserts, COMMIT saves changes; if errors occur, ROLLBACK can undo all.

3. Final Answer:

   Turn auto-commit OFF, insert all rows, then COMMIT if no errors -> Option A

4. Quick Check:

   Group inserts with auto-commit OFF and COMMIT [OK]

Hint: Turn off auto-commit to group inserts, then COMMIT [OK]

Common Mistakes:

• Relying on auto-commit ON for grouped inserts
• Using ROLLBACK before inserts to save changes
• Expecting auto-commit to undo on error automatically