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

Need a 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

Need a 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

Need a 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

Need a hint?

Use COMMIT PREPARED transaction_id to finish the distributed transaction.