Distributed transactions and 2PC in DBMS Theory - Mini Project: Build & Apply

Distributed Transactions and Two-Phase Commit (2PC)

📖 Scenario: You are managing a banking system where transactions must be consistent across two separate databases: one for customer accounts and one for transaction logs. To ensure data consistency, you will simulate a distributed transaction using the Two-Phase Commit (2PC) protocol.

🎯 Goal: Build a simple SQL script that demonstrates the setup and execution of a distributed transaction using the Two-Phase Commit protocol across two databases.

📋 What You'll Learn

Create two separate tables representing two databases: accounts and transaction_logs.

Set up a transaction coordinator variable to manage the transaction state.

Write SQL commands to begin the distributed transaction and prepare both databases.

Complete the transaction by committing or rolling back based on the prepare phase.

💡 Why This Matters

🌍 Real World

Distributed transactions are critical in banking, e-commerce, and other systems where data consistency across multiple databases is essential.

💼 Career

Understanding 2PC is important for database administrators, backend developers, and system architects working with distributed systems.

1

Create the initial tables for distributed transaction

Create two tables named accounts and transaction_logs. The accounts table should have columns account_id (integer) and balance (integer). The transaction_logs table should have columns log_id (integer) and description (text).

DBMS Theory

-- Create the accounts table
-- Create the transaction_logs table

Hint

Use CREATE TABLE statements with the specified columns and data types.

2

Set up a transaction coordinator variable

Declare a variable named transaction_id and assign it a unique string value 'txn_1001' to identify the distributed transaction.

DBMS Theory

CREATE TABLE accounts (
    account_id INT PRIMARY KEY,
    balance INT
);

CREATE TABLE transaction_logs (
    log_id INT PRIMARY KEY,
    description TEXT
);

-- Declare the transaction coordinator variable
-- Your code here

Hint

Use DECLARE to create a variable and assign the string 'txn_1001'.

3

Begin and prepare the distributed transaction

Write SQL commands to begin the distributed transaction using BEGIN TRANSACTION. Then prepare the transaction on both accounts and transaction_logs tables using PREPARE TRANSACTION with the transaction_id variable.

DBMS Theory

CREATE TABLE accounts (
    account_id INT PRIMARY KEY,
    balance INT
);

CREATE TABLE transaction_logs (
    log_id INT PRIMARY KEY,
    description TEXT
);

DECLARE transaction_id VARCHAR(20) = 'txn_1001';

-- Begin the distributed transaction
-- Prepare the transaction on both databases
-- Your code here

Hint

Use BEGIN TRANSACTION to start and PREPARE TRANSACTION transaction_id to prepare.

4

Commit the distributed transaction

Complete the distributed transaction by committing it using COMMIT PREPARED with the transaction_id variable.

DBMS Theory

CREATE TABLE accounts (
    account_id INT PRIMARY KEY,
    balance INT
);

CREATE TABLE transaction_logs (
    log_id INT PRIMARY KEY,
    description TEXT
);

DECLARE transaction_id VARCHAR(20) = 'txn_1001';

BEGIN TRANSACTION;
-- Example update on accounts
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
-- Example insert on transaction_logs
INSERT INTO transaction_logs (log_id, description) VALUES (1, 'Withdraw 100 from account 1');

PREPARE TRANSACTION transaction_id;

-- Commit the prepared transaction
-- Your code here

Hint

Use COMMIT PREPARED transaction_id to finish the distributed transaction.

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