Savepoints within transactions in SQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to start a transaction.

SQL

BEGIN [1];

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

AROLLBACK

BSAVEPOINT

CCOMMIT

DTRANSACTION

Attempts:

3 left

💡 Hint

Common Mistakes

Using SAVEPOINT instead of TRANSACTION to start a transaction.

Using COMMIT or ROLLBACK to start a transaction.

To start a transaction in SQL, you use BEGIN TRANSACTION;.

2fill in blank

medium

Complete the code to create a savepoint named 'sp1'.

SQL

SAVEPOINT [1];

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

Astart

Bsp1

Ccommit

Drollback

Attempts:

3 left

💡 Hint

Common Mistakes

Using reserved words like COMMIT or ROLLBACK as savepoint names.

Using 'start' which is not the intended savepoint name.

Savepoints are named markers within a transaction. Here, sp1 is the savepoint name.

3fill in blank

hard

Fix the error in the code to rollback to the savepoint 'sp1'.

SQL

ROLLBACK TO [1];

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

Asp1

Bcommit

Cstart

Dbegin

Attempts:

3 left

💡 Hint

Common Mistakes

Using reserved words like COMMIT or BEGIN instead of the savepoint name.

Using a different savepoint name than the one created.

To rollback to a savepoint, use ROLLBACK TO sp1; where sp1 is the savepoint name.

4fill in blank

hard

Fill both blanks to release the savepoint named 'sp1' and then commit the transaction.

SQL

RELEASE [1];
COMMIT [2];

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

Asp1

BTRANSACTION

CSAVEPOINT

Dsp2

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'SAVEPOINT' instead of 'TRANSACTION' after COMMIT.

Using a wrong savepoint name like 'sp2'.

To remove a savepoint, use RELEASE sp1;. To finish the transaction, use COMMIT TRANSACTION;.

5fill in blank

hard

Fill all three blanks to start a transaction, create a savepoint named 'sp1', and rollback to it.

SQL

BEGIN [1];
SAVEPOINT [2];
ROLLBACK TO [3];

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

ATRANSACTION

Bsp1

DSAVEPOINT

Attempts:

3 left

💡 Hint

Common Mistakes

Using SAVEPOINT instead of TRANSACTION to start the transaction.

Using different names for savepoint creation and rollback.

First, start the transaction with BEGIN TRANSACTION;. Then create a savepoint sp1. Finally, rollback to that savepoint with ROLLBACK TO sp1;.

Practice

1. What is the main purpose of using SAVEPOINT in a database transaction?

easy

A. To start a new transaction automatically

B. To permanently save changes to the database immediately

C. To mark a point in the transaction to which you can roll back without undoing the entire transaction

D. To lock a table for exclusive access

Solution

1. Step 1: Understand the role of SAVEPOINT

   SAVEPOINT creates a marker inside a transaction to allow partial rollback.

2. Step 2: Compare with other options

   It does not commit changes or lock tables; it only marks a rollback point.

3. Final Answer:

   To mark a point in the transaction to which you can roll back without undoing the entire transaction -> Option C

4. Quick Check:

   SAVEPOINT = partial rollback marker [OK]

Hint: SAVEPOINT marks rollback points inside transactions [OK]

Common Mistakes:

• Confusing SAVEPOINT with COMMIT
• Thinking SAVEPOINT locks tables
• Assuming SAVEPOINT starts a new transaction

2. Which of the following is the correct syntax to create a savepoint named sp1 inside a transaction?

easy

A. SET SAVEPOINT sp1;

B. SAVE sp1;

C. CREATE SAVEPOINT sp1;

D. SAVEPOINT sp1;

Solution

1. Step 1: Recall standard SQL syntax for savepoints

   The correct syntax is simply SAVEPOINT name; without extra keywords.

2. Step 2: Eliminate incorrect options

   Options A, B, and C use invalid or non-standard keywords.

3. Final Answer:

   SAVEPOINT sp1; -> Option D

4. Quick Check:

   Correct savepoint syntax = SAVEPOINT name [OK]

Hint: Use SAVEPOINT followed by name to create a savepoint [OK]

Common Mistakes:

• Adding CREATE or SET keywords incorrectly
• Using SAVE instead of SAVEPOINT
• Missing semicolon at the end

3. Consider the following SQL transaction sequence:

BEGIN TRANSACTION;
INSERT INTO accounts VALUES (1, 100);
SAVEPOINT sp1;
UPDATE accounts SET balance = balance - 50 WHERE id = 1;
ROLLBACK TO SAVEPOINT sp1;
COMMIT;

What will be the final balance for account with id = 1?

medium

A. 50

B. 100

C. 0

D. Rollback error

Solution

1. Step 1: Analyze the transaction steps

   Insert sets balance to 100, then savepoint sp1 is created. The update subtracts 50, making balance 50.

2. Step 2: Apply ROLLBACK TO SAVEPOINT

   Rollback to sp1 undoes the update, restoring balance to 100 before commit.

3. Final Answer:

   100 -> Option B

4. Quick Check:

   Rollback to savepoint undoes changes after it [OK]

Hint: Rollback to savepoint undoes changes after it [OK]

Common Mistakes:

• Assuming rollback undoes entire transaction
• Ignoring rollback and expecting updated balance
• Confusing rollback with commit

4. Identify the error in the following transaction code:

BEGIN TRANSACTION;
SAVEPOINT sp1;
INSERT INTO orders VALUES (101, 'Book');
ROLLBACK TO sp2;
COMMIT;

medium

A. ROLLBACK TO references a non-existent savepoint

B. Missing COMMIT before SAVEPOINT

C. SAVEPOINT must come after INSERT

D. ROLLBACK TO cannot be used inside transactions

Solution

1. Step 1: Check savepoint names

   Only sp1 is created, but rollback tries to use sp2 which does not exist.

2. Step 2: Validate transaction flow

   Rollback to a non-existent savepoint causes an error; other statements are valid.

3. Final Answer:

   ROLLBACK TO references a non-existent savepoint -> Option A

4. Quick Check:

   Rollback must use existing savepoint name [OK]

Hint: Rollback must refer to an existing savepoint [OK]

Common Mistakes:

• Using wrong savepoint name in rollback
• Thinking savepoints must follow inserts
• Believing rollback can't be inside transactions

5. You have a transaction that inserts multiple rows into a table. You want to undo only the last two inserts if an error occurs, but keep the earlier inserts. Which sequence correctly uses savepoints to achieve this?

hard

A. BEGIN; INSERT row1; SAVEPOINT sp1; INSERT row2; SAVEPOINT sp2; INSERT row3; ROLLBACK TO sp1; COMMIT;

B. BEGIN; SAVEPOINT sp1; INSERT row1; INSERT row2; SAVEPOINT sp2; INSERT row3; ROLLBACK TO sp2; COMMIT;

C. BEGIN; INSERT row1; SAVEPOINT sp1; INSERT row2; SAVEPOINT sp2; INSERT row3; ROLLBACK TO sp2; COMMIT;

D. BEGIN; INSERT row1; INSERT row2; SAVEPOINT sp1; INSERT row3; ROLLBACK TO sp1; COMMIT;

Solution

1. Step 1: Understand the goal

   To keep row1 but undo row2 and row3, place a savepoint after row1 and rollback to it.

2. Step 2: Identify correct sequence

   BEGIN; INSERT row1; SAVEPOINT sp1; INSERT row2; SAVEPOINT sp2; INSERT row3; ROLLBACK TO sp1; COMMIT; rolls back changes after sp1, undoing row2 and row3 while keeping row1.

3. Final Answer:

   BEGIN; INSERT row1; SAVEPOINT sp1; INSERT row2; SAVEPOINT sp2; INSERT row3; ROLLBACK TO sp1; COMMIT; -> Option A

4. Quick Check:

   Rollback to savepoint before last two inserts undoes them [OK]

Hint: Rollback to savepoint before last inserts to undo them [OK]

Common Mistakes:

• Rolling back to wrong savepoint
• Placing savepoints after all inserts
• Assuming rollback undoes all inserts