Savepoints within transactions in SQL - Time & Space Complexity

Time Complexity: Savepoints within transactions

O(n)

Understanding Time Complexity

When using savepoints inside transactions, it's important to understand how the number of operations grows as the database processes commands.

We want to know how the work done changes when we add more savepoints or commands inside a transaction.

Scenario Under Consideration

Analyze the time complexity of the following SQL transaction using savepoints.

BEGIN TRANSACTION;
INSERT INTO orders VALUES (1, 'item1');
SAVEPOINT sp1;
UPDATE inventory SET stock = stock - 1 WHERE item = 'item1';
SAVEPOINT sp2;
DELETE FROM cart WHERE item = 'item1';
COMMIT;
    

This code starts a transaction, inserts an order, sets savepoints, updates inventory, deletes from cart, and commits.

Identify Repeating Operations

Look for repeated actions or loops that affect performance.

• Primary operation: Executing SQL commands inside the transaction.
• How many times: Each command runs once; savepoints mark positions but do not repeat commands.

How Execution Grows With Input

Adding more commands or savepoints increases the work linearly.

Input Size (n)	Approx. Operations
10 commands	About 10 operations
100 commands	About 100 operations
1000 commands	About 1000 operations

Pattern observation: The total work grows directly with the number of commands and savepoints.

Final Time Complexity

Time Complexity: O(n)

This means the time to complete the transaction grows in a straight line as you add more commands or savepoints.

Common Mistake

[X] Wrong: "Savepoints cause the database to repeat all previous commands, making the transaction slower exponentially."

[OK] Correct: Savepoints only mark positions to roll back to; they do not re-execute commands, so they add only a small, constant overhead each time.

Interview Connect

Understanding how savepoints affect transaction time helps you explain database behavior clearly and shows you grasp how commands scale in real applications.

Self-Check

"What if we nested savepoints inside loops that run many times? How would the time complexity change?"

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