Auto-commit behavior in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Effect of Auto-commit ON after INSERT

Consider a database session where auto-commit is enabled by default. You run the following SQL commands:

INSERT INTO employees (id, name) VALUES (101, 'Alice');
SELECT COUNT(*) FROM employees;

What will the SELECT query return immediately after the INSERT?

SQL

INSERT INTO employees (id, name) VALUES (101, 'Alice');
SELECT COUNT(*) FROM employees;

A The count will be zero because auto-commit rolls back changes automatically.

B The count does not include the new row until a COMMIT is issued.

C The SELECT query will cause an error because the transaction is not committed.

D The count includes the new row inserted (count increased by 1).

💡 Hint

Think about what auto-commit means for each statement.

With auto-commit ON, each statement is committed immediately after execution. So the INSERT is committed before the SELECT runs, making the new row visible.

❓ query_result

intermediate

Effect of Auto-commit OFF on UPDATE visibility

In a session where auto-commit is disabled, you execute:

UPDATE products SET price = price * 1.1 WHERE category = 'Books';
SELECT COUNT(*) FROM products WHERE price > 100;

Assuming no COMMIT or ROLLBACK has been issued yet, what will the SELECT query return?

SQL

UPDATE products SET price = price * 1.1 WHERE category = 'Books';
SELECT COUNT(*) FROM products WHERE price > 100;

A The SELECT reflects the updated prices because the UPDATE is visible in the session.

B The SELECT does not see the updated prices until COMMIT is issued.

C The SELECT query will fail due to uncommitted changes.

D The SELECT returns zero because updates are rolled back automatically.

💡 Hint

Consider transaction isolation and visibility within the same session.

Even with auto-commit OFF, uncommitted changes are visible within the same session. So the SELECT sees the updated prices.

📝 Syntax

advanced

Disabling Auto-commit in SQL Session

Which of the following SQL commands correctly disables auto-commit mode in a session for a standard SQL database?

A SET AUTOCOMMIT = OFF;

B BEGIN TRANSACTION;

C COMMIT AUTOCOMMIT OFF;

D DISABLE AUTOCOMMIT;

💡 Hint

Think about the standard command to toggle auto-commit mode.

Setting AUTOCOMMIT = OFF disables auto-commit mode in many SQL databases. Other options are either invalid or start a transaction but do not disable auto-commit globally.

🧠 Conceptual

advanced

Impact of Auto-commit on Transaction Rollback

In a database session with auto-commit enabled, what happens if you execute an UPDATE statement and then issue a ROLLBACK command?

A The ROLLBACK causes an error because no transaction is active.

B The ROLLBACK undoes the UPDATE because it is part of the current transaction.

C The ROLLBACK has no effect because the UPDATE was already committed automatically.

D The ROLLBACK commits the UPDATE instead of undoing it.

💡 Hint

Consider when auto-commit commits changes relative to ROLLBACK.

With auto-commit ON, each statement is committed immediately. So ROLLBACK cannot undo changes already committed.

🔧 Debug

expert

Diagnosing Unexpected Data Visibility with Auto-commit OFF

You have auto-commit disabled and run these commands in two separate sessions:

-- Session 1:
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- no COMMIT yet

-- Session 2:
SELECT balance FROM accounts WHERE id = 1;

Session 2 still shows the old balance. Why?

A Because auto-commit is disabled, changes are immediately visible to all sessions.

B Because uncommitted changes in Session 1 are not visible to other sessions.

C Because Session 2 is running in autocommit mode and ignores uncommitted changes.

D Because the UPDATE statement failed silently in Session 1.

💡 Hint

Think about transaction isolation and visibility across sessions.

Uncommitted changes are only visible within the same session. Other sessions see the old data until a COMMIT is issued.

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