Distributed transactions and 2PC in DBMS Theory - Step-by-Step Execution

Concept Flow - Distributed transactions and 2PC

Client starts transaction

↓

Coordinator sends PREPARE to all participants

↓

Participants vote: YES or NO

↓

Coordinator receives all YES

↓

Coordinator sends COMMIT

↓

Participants commit

↓

Transaction ends

The coordinator asks all participants if they can commit. If all agree, it tells them to commit; if any say no, it tells them to abort.

Execution Sample

DBMS Theory

Coordinator: send PREPARE
Participants: vote YES or NO
Coordinator: if all YES send COMMIT else ABORT
Participants: commit or abort

This shows the basic message flow of the two-phase commit protocol in a distributed transaction.

Analysis Table

Step	Action	Coordinator State	Participants State	Message Sent	Result
1	Client starts transaction	Waiting	Idle	None	Transaction begins
2	Coordinator sends PREPARE	Waiting for votes	Ready to vote	PREPARE	Participants prepare to vote
3	Participants vote YES	Collecting votes	Vote YES	YES	Vote recorded
4	Participants vote YES	Collecting votes	Vote YES	YES	Vote recorded
5	Coordinator receives all YES	All votes YES	Waiting for commit/abort	None	Ready to commit
6	Coordinator sends COMMIT	Sent COMMIT	Received COMMIT	COMMIT	Participants prepare to commit
7	Participants commit	Waiting for ack	Committed	ACK	Commit done
8	Coordinator receives ACKs	Transaction complete	Idle	None	Transaction ends

💡 All participants voted YES, so coordinator sent COMMIT and transaction completed successfully.

State Tracker

Variable	Start	After Step 2	After Step 5	After Step 7	Final
Coordinator State	Waiting	Waiting for votes	All votes YES	Sent COMMIT	Transaction complete
Participants State	Idle	Ready to vote	Vote YES	Committed	Idle
Messages Sent	None	PREPARE	YES votes	COMMIT	ACK

Key Insights - 3 Insights

Why does the coordinator wait for all participants to vote before deciding?

Because the coordinator must ensure all participants agree to commit; if any say NO, the transaction must abort. This is shown in execution_table rows 3-5.

What happens if any participant votes NO?

The coordinator sends ABORT to all participants to cancel the transaction. This is not shown in the current table but would happen instead of sending COMMIT at step 6.

Why do participants send ACK after committing?

To inform the coordinator that they have completed the commit, so the coordinator knows the transaction is fully done. See execution_table rows 7-8.

Visual Quiz - 3 Questions

Look at the execution_table at step 5, what is the coordinator's state?

A All votes YES

B Waiting for votes

C Sent COMMIT

D Transaction complete

💡 Hint

Check the 'Coordinator State' column at step 5 in the execution_table.

At which step do participants commit the transaction?

A Step 4

B Step 6

C Step 7

D Step 8

💡 Hint

Look for 'Participants commit' in the 'Action' column of the execution_table.

If one participant votes NO at step 3, what message will the coordinator send next?

A PREPARE

B ABORT

C COMMIT

D ACK

💡 Hint

Recall the coordinator sends ABORT if any participant votes NO, as explained in key_moments.

Concept Snapshot

Distributed transactions involve multiple databases working together.
Two-Phase Commit (2PC) ensures all agree to commit or abort.
Phase 1: Coordinator asks participants to prepare and vote.
Phase 2: Coordinator sends COMMIT if all YES, else ABORT.
Participants commit or abort accordingly.
This prevents partial updates and keeps data consistent.

Full Transcript

Distributed transactions involve multiple databases or systems working together to complete a single transaction. The Two-Phase Commit protocol (2PC) is a method to ensure all parts agree to either commit or abort the transaction, keeping data consistent. First, the coordinator sends a PREPARE message to all participants asking if they can commit. Each participant votes YES or NO. If all vote YES, the coordinator sends a COMMIT message, and participants commit the transaction. If any vote NO, the coordinator sends ABORT, and participants cancel the transaction. Participants send acknowledgments after committing or aborting. This process prevents partial updates and ensures all systems stay in sync.

Practice

1. What is the main purpose of the Two-Phase Commit (2PC) protocol in distributed transactions?

easy

A. To ensure all participating systems agree to commit or abort a transaction

B. To speed up transaction processing by skipping checks

C. To allow partial commits in case of failures

D. To encrypt data during transaction processing

Solution

1. Step 1: Understand the role of 2PC in distributed systems

   2PC coordinates multiple systems to either all commit or all abort a transaction, ensuring consistency.

2. Step 2: Analyze the options

   Only To ensure all participating systems agree to commit or abort a transaction correctly describes 2PC's goal of agreement before finalizing changes.

3. Final Answer:

   To ensure all participating systems agree to commit or abort a transaction -> Option A

4. Quick Check:

   2PC ensures agreement = To ensure all participating systems agree to commit or abort a transaction [OK]

Hint: 2PC means all or nothing commit agreement [OK]

Common Mistakes:

• Thinking 2PC speeds up transactions
• Believing partial commits are allowed
• Confusing 2PC with encryption

2. Which of the following is the correct sequence of phases in the Two-Phase Commit protocol?

easy

A. Commit phase followed by Prepare phase

B. Commit phase only

C. Abort phase followed by Prepare phase

D. Prepare phase followed by Commit phase

Solution

1. Step 1: Recall the 2PC phases

   The protocol first asks participants to prepare (vote), then commits if all agree.

2. Step 2: Match phases to options

   Prepare phase followed by Commit phase correctly lists Prepare phase first, then Commit phase.

3. Final Answer:

   Prepare phase followed by Commit phase -> Option D

4. Quick Check:

   2PC phases = Prepare then Commit [OK]

Hint: Prepare before commit in 2PC sequence [OK]

Common Mistakes:

• Reversing the order of phases
• Ignoring the Prepare phase
• Thinking Commit happens alone

3. Consider a distributed transaction using 2PC with three participants: P1, P2, and P3. If P1 and P2 vote to commit but P3 votes to abort during the Prepare phase, what will be the final outcome?

medium

A. Only P1 and P2 commit, P3 aborts

B. All participants abort the transaction

C. All participants commit the transaction

D. Transaction is left in uncertain state

Solution

1. Step 1: Understand voting in 2PC Prepare phase

   All participants must vote to commit for the transaction to proceed; any abort vote causes abort.

2. Step 2: Apply voting results

   Since P3 votes to abort, the coordinator instructs all to abort to keep data consistent.

3. Final Answer:

   All participants abort the transaction -> Option B

4. Quick Check:

   Any abort vote = all abort [OK]

Hint: One abort vote cancels entire transaction [OK]

Common Mistakes:

• Assuming partial commits are allowed
• Thinking transaction stays uncertain
• Ignoring abort votes

4. A distributed transaction using 2PC is stuck indefinitely in the Commit phase. What is the most likely cause of this problem?

medium

A. A participant failed to send its vote during the Prepare phase

B. All participants voted to abort during Prepare phase

C. The coordinator crashed after sending Commit messages but before receiving acknowledgments

D. The transaction was never started

Solution

1. Step 1: Identify causes of blocking in Commit phase

   If the coordinator crashes after sending Commit but before acknowledgments, participants wait indefinitely.

2. Step 2: Analyze options

   The coordinator crashed after sending Commit messages but before receiving acknowledgments matches this scenario; other options relate to earlier phases or no transaction.

3. Final Answer:

   The coordinator crashed after sending Commit messages but before receiving acknowledgments -> Option C

4. Quick Check:

   Coordinator crash during Commit causes blocking [OK]

Hint: Coordinator crash after commit message causes blocking [OK]

Common Mistakes:

• Confusing Prepare phase failures with Commit phase blocking
• Assuming abort votes cause commit blocking
• Ignoring coordinator role

5. In a distributed system using 2PC, how can the protocol be improved to avoid the blocking problem caused by coordinator failure during the Commit phase?

hard

A. Use a Three-Phase Commit protocol that adds a pre-commit phase

B. Skip the Prepare phase to speed up commits

C. Allow participants to commit independently without coordinator

D. Increase the timeout for participant responses

Solution

1. Step 1: Understand blocking in 2PC

   2PC can block if coordinator fails after sending commit but before acknowledgments.

2. Step 2: Identify protocol improvements

   Three-Phase Commit adds a pre-commit phase to reduce blocking by ensuring participants can safely decide without coordinator.

3. Final Answer:

   Use a Three-Phase Commit protocol that adds a pre-commit phase -> Option A

4. Quick Check:

   3PC adds pre-commit to avoid blocking [OK]

Hint: 3PC adds pre-commit phase to prevent blocking [OK]

Common Mistakes:

• Skipping Prepare phase breaks consistency
• Allowing independent commits causes inconsistency
• Just increasing timeout doesn't fix blocking