ASSIGNMENT 38

Simulate a situation where the same transfer operation is executed more than once, as might happen in a real system due to network retries or duplicate requests. Perform the same deduction and credit operations multiple times and observe how the account balances are affected. Analyze whether the system prevents duplicate processing or allows the same transaction to be applied repeatedly. Based on your observations, think about how real-world systems ensure that repeated requests do not lead to inconsistent or duplicated state changes.

Introduction

In real-world systems (like banking or payment apps), a transaction might be sent multiple times due to:

Network retries
Slow server responses
Duplicate API requests

If the system is not designed properly, this can lead to duplicate deductions or credits, causing incorrect balances.

Step 1: Create Accounts Table

Code

CREATE TABLE accounts (
id INT PRIMARY KEY,
name VARCHAR(100),
balance DECIMAL(10,2)
);

Explanation

Stores account details
balance represents available money

Step 2: Insert Sample Data

Code

INSERT INTO accounts VALUES (1, 'Alice', 1000);
INSERT INTO accounts VALUES (2, 'Bob', 500);

Explanation

Alice --> ₹1000
Bob --> ₹500

Step 3: Simulate a Transfer (Single Execution)

Code

UPDATE accounts SET balance = balance - 200 WHERE id = 1;
UPDATE accounts SET balance = balance + 200 WHERE id = 2;

Result

Alice --> ₹800
Bob --> ₹700

Step 4: Simulate Duplicate Execution (Retry Scenario)

Code

UPDATE accounts SET balance = balance - 200 WHERE id = 1;
UPDATE accounts SET balance = balance + 200 WHERE id = 2;

Result

Alice --> ₹600
Bob --> ₹900

Observation

The system allowed duplicate processing
Same transaction applied twice
No mechanism to detect duplicates

This leads to:

Incorrect balances
Financial inconsistency
Potential fraud or system failure

The database:

Executes each query independently
Has no memory of previous transactions
Cannot identify if the request is a duplicate

Real-World Solutions

1. Transaction ID (Idempotency Key)

Example

CREATE TABLE transactions (
txn_id VARCHAR(50) PRIMARY KEY,
from_account INT,
to_account INT,
amount DECIMAL(10,2)
);

Each request has a unique txn_id
Duplicate txn_id --> rejected automatically

1. Check Before Processing

Logic

IF NOT EXISTS (SELECT * FROM transactions WHERE txn_id = 'TXN123') THEN
-- perform transfer
END IF;

1. Use Database Transactions (ACID)

Code

START TRANSACTION;

UPDATE accounts SET balance = balance - 200 WHERE id = 1;
UPDATE accounts SET balance = balance + 200 WHERE id = 2;

COMMIT;

Ensures atomicity

1. Unique Constraint on Transactions

ALTER TABLE transactions
ADD CONSTRAINT unique_txn UNIQUE (txn_id);

1. Application-Level Idempotency

APIs store request IDs

If same request comes again → return previous result
Used by systems like:
Payment gateways
Banking apps

Final Conclusion

Key Findings
Basic SQL operations do NOT prevent duplicate execution
Same transaction can be applied multiple times
Leads to data inconsistency