CA 34 - Atomicity - Design a Reliable Wallet Transfer System with ACID Guarantees

 Building a (similar to PhonePe / GPay / Paytm).


Users can,
  • Store money in their wallet
  • Transfer money to other users
  • View transaction history
This system must strictly follow , because even a small inconsistency can lead to,
  • Money loss
  • Duplicate transactions
  • Incorrect balances
TABLE :

    Accounts table: 

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


    The table is created having id as the Primary Key which is serial, thus the id will be in a sequence. The name and the balance is set to NOT nULL, thus they cannot have null values.
    Also a check condition is given for the balance that the balance column cannot have negative values.
And a default is used for the last_updated column to set the current date.

Entering a dummy value : 

    

In order to show the Atomicity of postgres we will open a transcation and explain what Atomicity is. Atomicity is nothing but a property of a DBMS where the trascation is either successful or completly failed and rolledback. 

    Thus this either successful transcation or Rollback of complete transcation comes in handly for our App which handles online transcation. In case if our user 'Alice' sends money to 'Bob' , the amount is debited from Alice account but before crediting into Bob account there were some interruptions, this causes partial transcation, thus to avoid this the Atomicity features comes into the play. So the transcation is rolledback.

    We will demonstrate it here.


    Here we have started a transcation and have debited amount from the alice account, let us make some interruptions like syntax error in next step


    After the interruption, error occurs saying that the transcation is aborted and command is ignored. Let's check if the changes made to alice account is there.


    As we can see that the changes made could not be commited rather they are rolledback. And the changes made to the Alice account is reverted.

        Thus no partial transcation can be taken place, this is the Atomicity feature.

Another Example:


Here a transcation was begun and there a money transfere between Alice account and bOb account which was successful. So the changes made can be seen in the transcation, but now there was a check constraint error for the Alice account due to the transfer of 1000 from here acutal balance 500. Thus this will aslo be considered as partial transcation and the changes made will be reverted


So now we want the first successful transfer to be saved, thus we make use of Savepoint, even if there are some interruption we can roll back to the previous save and completed the transcation .


    Here as we can see the savepoint is used after the first debit and credit process thus even if there are any errors after this savepoint and then commit. Thus the transfer between the Alice and Bob account has been successfully completed. This can also be used to rollback and redo if any syntax errors are made. 



Comments

Post a Comment

Popular posts from this blog

CA 04 - Two Sum & Sorted Two Sum

Image
  MY CODE: In brute force method the intuitive way of solving this problem with two for loop. Using nested loop to traverse through the list and try different combination of two sum to check if it matches with the target.    Time complexity: O(n^2)       OPTIMIZED:          In the optimized method of approaching the Two Sum problem. The given list is already sorted in ascending order. This gives us the leverage of using Two pointer technique to solve this problem. Thus using single loop to approach this problem. If current sum is less than target move the left pointer, if it is greater move the right pointer else return the current index (equal to target).  Time complexity: O(n) MY CODE:
Read more

CA 05 - Reverse the array

  Array Reverse Reverse an array  arr[] . Reversing an array means  rearranging  the elements such that the  first  element becomes the  last , the  second  element becomes  second last  and so on. Input: arr[] = [1, 4, 3, 2, 6, 5] Output: [5, 6, 2, 3, 4, 1] Explanation: The first element 1 moves to last position, the second element 4 moves to second-last and so on. Input: arr[] = [4, 5, 1, 2] Output: [2, 1, 5, 4] Explanation: The first element 4 moves to last position, the second element 5 moves to second last and so on. CODE:     BRUTE FORCE METHOD:          Here, in this method we will create a temporary array to store the reversed given array and then assign it to the given array class Solution :     def reverseArray ( arr ):         n = len (arr)         temp = [ 0 ] * n         for i in range (n- 1 ,- 1 ,- 1 ): ...
Read more

CA 21 - Basic Select SQL Queries

Image
 1.  Query all columns for a city in   CITY   with the   ID   1661 . The  CITY  table is described as follows: Here we make use of the where conditon cllause to retrieve the details of the Id with 1661, we select all the columns to be displayed and "*" is avoided for best practice. SOLUTION: SELECT ID,NAME,COUNTRYCODE,DISTRICT,POPULATION FROM CITY where ID = 1661 ; 2. Query all columns for all American cities in the   CITY   table with populations larger than   100000 . The   CountryCode   for America is   USA . The  CITY  table is described as follows: Here we make use of the where condtional clause, to obatin the population to be over thhe given number we make use of the' >' symbol to achieve this. Also the AND operator is used to state two where conditons where we want only the details of cities in USA so we use the countrycode check with 'USA' single quotes are used to check with the value, usage ...
Read more