Transfer of money securely using transactions and durability in PostgreSQL.

 I am building a system(like Phonepay/Gpay/Paytm).In that, users can store money,tranfer money,and view transactions where the system should not loose the money,should duplicate transactions and incorrect balances.

CREATING ACCOUNTS TABLE:


 CREATE TABLE accounts (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    balance INT NOT NULL CHECK (balance >= 0),
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERTING DUMMY DATA:

INSERT INTO accounts (name, balance)
VALUES
('Alice', 1000),
('Bob', 500);

Now want to transfer between accounts(here,alice and bob) and commit the transaction,ensuring that the changes are made in databases.



In above statements,I have updated balance for alice to 800(1000-200) and bob to 700(500+200) then used commit command.After I restarted my system(assuming as a crash occured unexpectedly) then query to show my balances it shows the last updated value.


What happens here is the commited changes persist even after the restart.

CRASH HAPPENS BEFORE COMMIT:

When we begin to query and updates are done then suddenly unexpected crashes comes means the transaction is not saved,no money is transfered.Now alice will have balance=1000 and bob will have 500.Database will rollback automatically.This happen because we didn't use commit.

CRASH HAPPENS AFTER COMMIT:

When we begin to query,updates are done and we put the query commit,then suddenly unexpected crashes comes means the transaction is saved,money is transfered.Now alice will have balance=800 and bob will have 700.Database will be updated.This happen because we use commit.

HOW DATABASE ENSURES DURABILITY:

PostgreSQL uses WAL(Write Ahead Logging).It writes the changes that has been made to log file before saving the actual data.If crash happens,database reads the log and the data which is commited is restored successfully.

SUMMARY:

  • COMMIT makes changes permanent
  • After restart, committed data remains without changing
  • If crash happens before commit,it will rollback
  • If crash happens after commit ,it will persist the data

  • Durability is ensured using WAL (the log system)

    • Comments

    Post a Comment

    Popular posts from this blog

    How I Sorted an Array of 0s, 1s, and 2s in Python Without Using sort()

     Introduction: While practicing Python problems, I came across an interesting question where I had to sort an array that contains only 0s, 1s, and 2s . The twist was that I was not allowed to use the built-in sort() function . At first, I didn’t know how to approach it, but then I learned a simple method using counting. In this blog, I’ll explain how I solved it step by step. Problem Statement: We are given an array like this: 2 0 1 2 0 1 0 We need to sort it in ascending order: 0 0 0 1 1 2 2 My Idea: Instead of sorting directly, I thought: Why not count how many 0s, 1s, and 2s are there ? Then I can just rebuild the array using those counts. Code I Used: arr = list(map(int, input("Enter elements (0s,1s,2s): ").split())) count0 = count1 = count2 = 0 for num in arr:     if num == 0:         count0 += 1     elif num == 1:         count1 += 1     ...
    Read more

    How I Sorted Squares of a Sorted Array in Python

    Introduction: While practicing Python problems, I came across a question where I had to square each element of a sorted array and return the result in sorted order. At first, it looked easy, but negative numbers made it a bit tricky. In this blog, I will explain how I solved it step by step. Problem Statement: We are given an array sorted in non-decreasing order. We need to return a new array containing the squares of each number, also sorted in non-decreasing order. Example: Input: nums = [-4, -1, 0, 3, 10] Output: [0, 1, 9, 16, 100] Input: nums = [-7, -3, 2, 3, 11] Output: [4, 9, 9, 49, 121] My Idea: If I directly square the numbers, negative values become positive, which may disturb the order. So I used a two-pointer approach. I compare the absolute values from both ends and place the larger square at the end of the result array. Code: class Solution: def sortedSquares(self, nums): n = len(nums) result = [0] * n left = 0 right = n - 1 pos...
    Read more