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.