Auto-commit behavior in SQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to check if auto-commit is enabled.

SQL

SHOW VARIABLES LIKE '[1]';

Drag options to blanks, or click blank then click option'

Acommit_mode

Bauto_commit

Cautocommit

Dtransaction_mode

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'auto_commit' instead of 'autocommit'.

Trying to check 'commit_mode' which is not a variable.

The variable autocommit shows if auto-commit is enabled in the database.

2fill in blank

medium

Complete the SQL command to disable auto-commit for the current session.

SQL

SET SESSION [1] = 0;

Drag options to blanks, or click blank then click option'

Aautocommit

Bauto_commit

Ccommit_mode

Dtransaction_mode

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'auto_commit' instead of 'autocommit'.

Trying to set 'commit_mode' which is invalid.

To disable auto-commit, set autocommit to 0 for the session.

3fill in blank

hard

Fix the error in the SQL command to commit a transaction.

SQL

[1];

Drag options to blanks, or click blank then click option'

ACOMMIT TRANSACTION

BROLLBACK

CSAVEPOINT

DCOMMIT

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'COMMIT TRANSACTION' which is not standard SQL.

Confusing commit with rollback or savepoint.

The correct command to commit a transaction is simply COMMIT.

4fill in blank

hard

Fill both blanks to start a transaction and then disable auto-commit.

SQL

START [1]; SET [2] = 0;

Drag options to blanks, or click blank then click option'

ATRANSACTION

Bautocommit

Cauto_commit

DSESSION

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'auto_commit' instead of 'autocommit'.

Using 'SESSION' instead of 'autocommit' for the second blank.

Use START TRANSACTION to begin a transaction and set autocommit to 0 to disable auto-commit.

5fill in blank

hard

Fill all three blanks to create a transaction, insert a row, and commit the changes.

SQL

BEGIN [1]; INSERT INTO users (name) VALUES ('Alice'); [2]; [3];

Drag options to blanks, or click blank then click option'

ATRANSACTION

BCOMMIT

CROLLBACK

DSTART

Attempts:

3 left

💡 Hint

Common Mistakes

Using ROLLBACK instead of COMMIT to save changes.

Using START instead of BEGIN for the first blank.

Use BEGIN TRANSACTION or START TRANSACTION to begin, then COMMIT to save changes.

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