Transaction handling in Node.js - Step-by-Step Execution

Concept Flow - Transaction handling

Start Transaction

↓

Execute Queries

↓

Any Error?

Yes→Rollback Transaction

↓

Transaction Aborted

↓

Commit Transaction

↓

End

This flow shows starting a transaction, running queries, checking for errors, then either committing or rolling back.

Execution Sample

Node.js

async function runTransaction(db) {
  await db.beginTransaction();
  try {
    await db.query('INSERT INTO users ...');
    await db.commit();
  } catch (e) {
    await db.rollback();
  }
}

This code starts a transaction, runs a query, commits if successful, or rolls back on error.

Execution Table

Step	Action	Result	State Change	Next Step
1	Begin transaction	Transaction started	Transaction open	Execute queries
2	Run INSERT query	Query successful	Data staged	Check for errors
3	Check for errors	No error	Continue	Commit transaction
4	Commit transaction	Transaction committed	Data saved permanently	End
5	End	Transaction complete	Transaction closed	Stop

💡 Transaction ends after commit; all changes saved permanently.

Variable Tracker

Variable	Start	After Step 1	After Step 2	After Step 4	Final
transactionState	none	open	open	committed	closed
dataChanges	none	none	staged	saved	saved

Key Moments - 2 Insights

Why do we need to rollback the transaction on error?

Rollback undoes all changes made during the transaction to keep data consistent, as shown in step 4 of execution_table where commit only happens if no errors occur.

What happens if commit is called without starting a transaction?

Commit would fail or do nothing because there is no open transaction to finalize, as the transactionState variable shows 'none' before step 1.

Visual Quiz - 3 Questions

Look at the execution_table, what is the transactionState after step 2?

A closed

B committed

C open

D none

💡 Hint

Check variable_tracker row for transactionState after Step 2.

At which step does the transaction get permanently saved?

A Step 4

B Step 3

C Step 2

D Step 5

💡 Hint

Look at execution_table row where commit happens.

If an error occurs during the query, what should happen next?

A Commit transaction

B Rollback transaction

C Ignore error and continue

D End without commit or rollback

💡 Hint

Refer to concept_flow where error leads to rollback.

Concept Snapshot

Transaction handling in Node.js:
- Start with db.beginTransaction()
- Run queries inside try block
- On success, call db.commit() to save changes
- On error, call db.rollback() to undo changes
- Ensures data stays consistent and safe

Full Transcript

Transaction handling means grouping database operations so they all succeed or fail together. In Node.js, you start a transaction with beginTransaction. Then you run your queries. If all queries succeed, you commit to save changes. If any query fails, you rollback to undo all changes. This keeps your data safe and consistent. The flow is: start transaction, run queries, check errors, commit if no errors, rollback if errors, then end.

Practice

1. What is the main purpose of using transactions in Node.js database operations?

easy

A. To ensure multiple database actions succeed or fail together

B. To speed up database queries

C. To create multiple database connections

D. To log database errors automatically

Solution

1. Step 1: Understand the role of transactions

   Transactions group several database actions so they either all succeed or all fail, maintaining data integrity.

2. Step 2: Compare options

   Only To ensure multiple database actions succeed or fail together correctly describes this purpose. Other options describe unrelated features.

3. Final Answer:

   To ensure multiple database actions succeed or fail together -> Option A

4. Quick Check:

   Transaction purpose = group actions for success/failure [OK]

Hint: Transactions keep multiple DB actions atomic and consistent [OK]

Common Mistakes:

• Thinking transactions speed up queries
• Confusing transactions with connection pooling
• Assuming transactions auto-log errors

2. Which of the following is the correct way to start a transaction using a Node.js database client?

easy

A. client.query('START TRANSACTION')

B. client.query('OPEN TRANSACTION')

C. client.query('BEGIN')

D. client.query('INIT TRANSACTION')

Solution

1. Step 1: Recall standard SQL transaction commands

   The standard command to start a transaction is 'BEGIN'.

2. Step 2: Validate options

   Only client.query('BEGIN') uses the correct SQL command 'BEGIN'. Other options are invalid SQL commands.

3. Final Answer:

   client.query('BEGIN') -> Option C

4. Quick Check:

   Start transaction command = BEGIN [OK]

Hint: Use 'BEGIN' to start transactions in SQL [OK]

Common Mistakes:

• Using non-standard commands like 'START TRANSACTION'
• Confusing transaction start with connection open
• Using commands that don't exist in SQL

3. Consider this Node.js code snippet using a PostgreSQL client:

await client.query('BEGIN');
await client.query('INSERT INTO users(name) VALUES($1)', ['Alice']);
await client.query('COMMIT');
console.log('Transaction complete');

What will be printed if all queries succeed?

medium

A. Transaction complete

B. Error: Transaction failed

C. No output

D. Transaction rolled back

Solution

1. Step 1: Analyze the transaction flow

   The code starts a transaction, inserts a user, then commits the transaction.

2. Step 2: Check output after successful commit

   Since all queries succeed, the commit happens and the console logs 'Transaction complete'.

3. Final Answer:

   Transaction complete -> Option A

4. Quick Check:

   Successful commit logs message [OK]

Hint: Successful commit prints confirmation message [OK]

Common Mistakes:

• Assuming errors print without exceptions
• Confusing commit with rollback output
• Ignoring async/await behavior

4. Identify the error in this Node.js transaction code snippet:

try {
  await client.query('BEGIN');
  await client.query('INSERT INTO orders(id) VALUES(1)');
  await client.query('COMMIT');
} catch (e) {
  await client.query('ROLLBACK');
  console.error('Transaction failed');
}

medium

A. Missing await before ROLLBACK query

B. No error handling for ROLLBACK failure

C. BEGIN should be after INSERT query

D. COMMIT should be inside catch block

Solution

1. Step 1: Review transaction error handling

   The code correctly uses try-catch to rollback on error.

2. Step 2: Identify missing error handling for rollback

   If the ROLLBACK query itself fails, this code does not catch that error, which can cause unhandled exceptions.

3. Final Answer:

   No error handling for ROLLBACK failure -> Option B

4. Quick Check:

   Rollback errors need separate handling [OK]

Hint: Always handle errors from ROLLBACK queries too [OK]

Common Mistakes:

• Assuming ROLLBACK never fails
• Placing BEGIN after queries
• Putting COMMIT inside catch block

5. You want to perform two related database inserts in Node.js using transactions. Which pattern correctly ensures both inserts succeed or both fail, releasing the client connection properly?

const client = await pool.connect();
try {
  await client.query('BEGIN');
  await client.query('INSERT INTO accounts(id) VALUES(1)');
  await client.query('INSERT INTO profiles(account_id) VALUES(1)');
  await client.query('COMMIT');
} catch (e) {
  await client.query('ROLLBACK');
  throw e;
} finally {
  client.release();
}

hard

A. Throwing error in catch block breaks transaction

B. client.release() should be called before COMMIT

C. ROLLBACK should be inside finally block

D. This pattern is correct and safely handles transactions and connection release

Solution

1. Step 1: Check transaction flow and error handling

   The code starts a transaction, performs two inserts, commits if successful, rolls back on error, and rethrows the error.

2. Step 2: Verify connection release

   The client connection is released in the finally block, ensuring it always happens regardless of success or failure.

3. Final Answer:

   This pattern is correct and safely handles transactions and connection release -> Option D

4. Quick Check:

   Try-catch-finally with commit, rollback, release = safe pattern [OK]

Hint: Use try-catch-finally with commit, rollback, and release [OK]

Common Mistakes:

• Releasing connection too early
• Placing rollback in finally block
• Not rethrowing errors after rollback