Bird
Raised Fist0
SQLquery~5 mins

BEGIN TRANSACTION syntax in SQL - Time & Space Complexity

Choose your learning style10 modes available
LearnWhyDeepVisualTryChallengeProjectRecallTime
Time Complexity: BEGIN TRANSACTION syntax
O(n)
Understanding Time Complexity

We want to understand how the time to run a transaction changes as the amount of work inside it grows.

How does the number of operations inside a transaction affect the total time?

Scenario Under Consideration

Analyze the time complexity of the following SQL transaction.

BEGIN TRANSACTION;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

This code moves money between two accounts inside a transaction to keep data safe.

Identify Repeating Operations

Look for repeated actions that take time.

  • Primary operation: Two UPDATE statements inside the transaction.
  • How many times: Each UPDATE runs once per transaction in this example.
How Execution Grows With Input

As the number of UPDATE statements inside the transaction grows, the total work grows too.

Input Size (number of UPDATEs)Approx. Operations
22 UPDATE operations
1010 UPDATE operations
100100 UPDATE operations

Pattern observation: The total time grows roughly in direct proportion to the number of statements inside the transaction.

Final Time Complexity

Time Complexity: O(n)

This means the time to complete the transaction grows linearly with the number of operations inside it.

Common Mistake

[X] Wrong: "A transaction always takes the same time no matter how many operations it has."

[OK] Correct: More operations inside a transaction mean more work, so the time grows with the number of operations.

Interview Connect

Understanding how transaction time grows helps you write efficient database code and explain your reasoning clearly in interviews.

Self-Check

"What if we added a loop that runs 100 UPDATE statements inside one transaction? How would the time complexity change?"

Practice

(1/5)
1. What is the main purpose of using BEGIN TRANSACTION in SQL?
easy
A. To permanently delete all data from a table
B. To start a group of database operations that can be saved or undone together
C. To create a new database
D. To display all records from a table

Solution

  1. Step 1: Understand what BEGIN TRANSACTION does

    This command starts a transaction, grouping multiple operations so they can be committed or rolled back as one.

  2. Step 2: Compare other options

    Deleting data, creating databases, or displaying records are unrelated to starting transactions.

  3. Final Answer:

    To start a group of database operations that can be saved or undone together -> Option B

  4. Quick Check:

    BEGIN TRANSACTION = start safe group [OK]
Hint: Think of transactions as a safety net for multiple actions [OK]
Common Mistakes:
  • Confusing transaction start with data deletion
  • Thinking it creates databases
  • Assuming it shows data
2. Which of the following is the correct syntax to start a transaction in SQL?
easy
A. BEGIN TRANSACTION;
B. START TRANSACTION;
C. BEGIN;
D. TRANSACTION START;

Solution

  1. Step 1: Recall standard SQL syntax for starting transactions

    The correct syntax to start a transaction is BEGIN TRANSACTION;.

  2. Step 2: Check other options for correctness

    START TRANSACTION; is valid in some systems but not standard SQL; BEGIN; alone is incomplete; TRANSACTION START; is invalid syntax.

  3. Final Answer:

    BEGIN TRANSACTION; -> Option A

  4. Quick Check:

    Correct syntax = BEGIN TRANSACTION; [OK]
Hint: Remember: 'BEGIN TRANSACTION;' starts the safe block [OK]
Common Mistakes:
  • Using incomplete 'BEGIN;' without TRANSACTION
  • Confusing order of words
  • Using non-standard phrases
3. Consider this SQL code:
BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

What is the effect of this transaction?
medium
A. The transaction will cause an error
B. Only the first update is saved
C. Both updates happen together or not at all
D. Updates happen immediately without grouping

Solution

  1. Step 1: Analyze the transaction block

    The two updates are inside a transaction started by BEGIN TRANSACTION; and ended by COMMIT;.

  2. Step 2: Understand transaction behavior

    Both updates will be saved together if no errors occur; if an error happens, none will be saved.

  3. Final Answer:

    Both updates happen together or not at all -> Option C

  4. Quick Check:

    Transaction = all or nothing [OK]
Hint: Transactions group changes to save or cancel together [OK]
Common Mistakes:
  • Thinking updates save separately
  • Assuming immediate save without commit
  • Believing transaction causes error here
4. Identify the error in this SQL transaction code:
BEGIN TRANSACTION
INSERT INTO users (name) VALUES ('Alice');
COMMIT;
medium
A. INSERT statement is invalid
B. COMMIT should come before INSERT
C. No error, code is correct
D. Missing semicolon after BEGIN TRANSACTION

Solution

  1. Step 1: Check syntax of each statement

    BEGIN TRANSACTION is missing a semicolon at the end, which is required to separate statements.

  2. Step 2: Verify order and validity of other statements

    COMMIT; correctly comes after the insert; the insert syntax is valid.

  3. Final Answer:

    Missing semicolon after BEGIN TRANSACTION -> Option D

  4. Quick Check:

    Semicolon needed after BEGIN TRANSACTION [OK]
Hint: Always end SQL statements with semicolon [OK]
Common Mistakes:
  • Forgetting semicolon after BEGIN TRANSACTION
  • Misordering COMMIT and INSERT
  • Assuming insert syntax is wrong
5. You want to transfer $200 from account A (id=1) to account B (id=2) safely. Which SQL transaction block correctly ensures the transfer is atomic and consistent?
hard
A. 
BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 200 WHERE id = 1;
UPDATE accounts SET balance = balance + 200 WHERE id = 2;
COMMIT;
B. 
UPDATE accounts SET balance = balance - 200 WHERE id = 1;
UPDATE accounts SET balance = balance + 200 WHERE id = 2;
C. 
BEGIN TRANSACTION;
UPDATE accounts SET balance = balance + 200 WHERE id = 2;
COMMIT;
D. 
BEGIN;
UPDATE accounts SET balance = balance - 200 WHERE id = 1;
ROLLBACK;

Solution

  1. Step 1: Understand the need for atomic transfer

    Both debit and credit updates must happen together or not at all to keep balances consistent.

  2. Step 2: Evaluate each option

    BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 200 WHERE id = 1;
UPDATE accounts SET balance = balance + 200 WHERE id = 2;
COMMIT;
    uses BEGIN TRANSACTION and COMMIT to group both updates safely. 
    UPDATE accounts SET balance = balance - 200 WHERE id = 1;
UPDATE accounts SET balance = balance + 200 WHERE id = 2;
    lacks transaction, risking partial update. 
    BEGIN TRANSACTION;
UPDATE accounts SET balance = balance + 200 WHERE id = 2;
COMMIT;
    only credits without debit. 
    BEGIN;
UPDATE accounts SET balance = balance - 200 WHERE id = 1;
ROLLBACK;
    rolls back, so no change occurs.

  3. Final Answer:

    BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 200 WHERE id = 1;
UPDATE accounts SET balance = balance + 200 WHERE id = 2;
COMMIT;
     -> Option A

  4. Quick Check:

    Atomic transfer needs BEGIN TRANSACTION + COMMIT [OK]
Hint: Use BEGIN TRANSACTION and COMMIT to group related updates [OK]
Common Mistakes:
  • Not grouping both updates in a transaction
  • Forgetting COMMIT to save changes
  • Rolling back changes unintentionally
Recommended

Ready to prove it?

Pass the test and this pattern moves unknown strong while earning +1 Grip toward your certificate.

10 questions~5 mineasy hard