BEGIN TRANSACTION syntax in SQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does BEGIN TRANSACTION do in SQL?

BEGIN TRANSACTION starts a new transaction block. It tells the database to treat the following operations as a single unit that can be committed or rolled back together.

beginner

Why use BEGIN TRANSACTION before multiple SQL statements?

Using BEGIN TRANSACTION ensures that all statements inside the transaction either succeed together or fail together, keeping data consistent.

beginner

What commands usually follow BEGIN TRANSACTION?

After BEGIN TRANSACTION, you usually run your SQL statements, then use COMMIT to save changes or ROLLBACK to undo them.

intermediate

Is BEGIN TRANSACTION supported by all SQL databases?

Most SQL databases support BEGIN TRANSACTION or similar commands, but syntax can vary slightly. Always check your database documentation.

intermediate

What happens if you forget to COMMIT after BEGIN TRANSACTION?

If you forget to COMMIT, the changes made inside the transaction might not be saved and could be rolled back automatically when the session ends.

What is the purpose of BEGIN TRANSACTION in SQL?

A To delete data from a table

B To end a transaction

C To start a group of operations treated as one unit

D To create a new database

BEGIN TRANSACTION starts a transaction block where multiple operations are grouped together.

Which command is used to save changes after BEGIN TRANSACTION?

A ROLLBACK

B SAVE

C END

D COMMIT

COMMIT saves all changes made in the transaction.

What does ROLLBACK do in a transaction?

A Undoes all changes made in the transaction

B Starts a new transaction

C Saves changes permanently

D Deletes the transaction log

ROLLBACK undoes all changes made since BEGIN TRANSACTION.

If you do not use COMMIT after BEGIN TRANSACTION, what happens?

A Changes are saved automatically

B Changes might be lost or rolled back

C Transaction ends successfully

D Database crashes

Without COMMIT, changes may not be saved and could be rolled back.

Which of these is NOT a typical step in a transaction?

A CREATE TABLE

B BEGIN TRANSACTION

C ROLLBACK

D COMMIT

CREATE TABLE is a schema operation, not part of transaction control.

Explain what BEGIN TRANSACTION does and why it is important in SQL.

Think about how to keep data changes safe and consistent.

Describe the sequence of commands you use when working with transactions in SQL.

What do you do before and after your SQL commands to control changes?

Practice

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