ISOLATION

Let me explain concurrent transactions using accounts table

First, we have accounts table

Alice = 1000
Bob = 500

since, now we try to do multiple transactions at same time from same account.

Now we open 2 sessions (2 query windows).

In Session 1:

BEGIN;

UPDATE accounts
SET balance = balance - 800
WHERE name = 'Alice';

 not committing

since, we deducted 800 from Alice.. but not committed yet.. so change is not final.

Now in Session 2:

BEGIN;

SELECT * FROM accounts WHERE name = 'Alice';

since, we are checking balance in another session.

Result:

Alice = 1000

since, Session 2 cannot see uncommitted changes from Session 1.. so it still shows old value.

Now in Session 2 we try to deduct again:

UPDATE accounts
SET balance = balance - 500
WHERE name = 'Alice';

since, system will try to update same row.

Result:

it will wait (lock)

since, Session 1 is already using that row.. so Session 2 cannot update until Session 1 completes.

Now go back to Session 1 and do:

COMMIT;

since, now changes are saved.

Now Session 2 continues

and then updates based on latest value.

Final result:

Alice = 200 (after first) = then second update happens

since, database handles it properly using locking.

Now about isolation levels

Default (READ COMMITTED):

since, one transaction cannot see uncommitted data of another

so no dirty read happens

REPEATABLE READ:

since, once data is read.. it will not change inside that transaction

so no non-repeatable read

SERIALIZABLE:

since, transactions behave like one after another

so no conflicts and full safety

THE THING IS:

since, database uses isolation to prevent problems like

dirty read = reading uncommitted data
non-repeatable read = data changing inside same transaction
lost update = two updates overwriting each other

so,

Session 2 cannot see uncommitted data
and cannot update same row immediately

because of this,

balance will not become wrong
no duplicate deduction
system stays consistent

this is how payment apps handle multiple users at same time safely.

Comments

Post a Comment

Popular posts from this blog

THE DELIVERY MAN

  The Delivery Man – Python List Operations  Introduction Lists in Python are used to store multiple items in a single variable. They are flexible, allowing us to add, remove, update, and manipulate elements easily. In this task, different list operations are performed to simulate a delivery man's workflow. 1. Creating a list and accessing an item items = ["Notebook", "Pencil", "Eraser", "Ruler", "Marker"] print(items[2]) Approach: Indexing Why: Lists use zero-based indexing, so the third item is accessed using index 2. 2. Adding a new item items.append("Glue Stick") print(items) Approach: append() method Why: It adds an item at the end of the list, which is ideal for adding new deliveries. 3. Inserting an item items.insert(2, "Highlighter") print(items) Approach: insert() method Why: Used when we need to add an item at a specific position instead of the end. 4. Removing an item items.remove("Ruler...
Read more

EC2 LAUNCHING

  EC2 Step 1: Launch EC2 Instance Go to: AWS Console --> EC2 --> Launch Instance Configure: Name : my-web-server AMI : Amazon Linux 2 (easy for beginners) Instance Type : t2.micro (free tier) Step 2: Key Pair Create or select a key pair. Step 3: Network Settings  Allow these: SSH (port 22) --> for login  HTTP (port 80) --> for web access Step 4: Launch Instance Click Launch Instance Step 5: Connect to Instance Use: EC2 --> Connect --> EC2 Instance Connect (easiest) OR via terminal: with this command. for this you have to download the pem files while selecting the key pair.  ssh -i your-key.pem ec2-user@<public-ip> Step 6: Install Web Server For Amazon Linux: sudo yum update -y sudo yum install httpd -y Step 7: Start Web Server sudo systemctl start httpd sudo systemctl enable httpd Step 8: Add a Web Page echo "Hello from EC2" | sudo tee /var/www/html/index.html Step 9: Get Public IP Go to EC2 d...
Read more

SORT A LINKED LIST USING MERGE SORT

  Sort a Linked List using Merge Sort Introduction Sorting a linked list efficiently is a common problem in data structures. Unlike arrays, linked lists do not allow direct access to elements, so algorithms like quicksort are not ideal. Merge Sort is the best choice for linked lists because: It does not require random access It works efficiently with node-based structures Problem Statement Given the head of a linked list: Sort the list in ascending order Return the sorted linked list Example Input: 10 -> 30 -> 20 -> 40 -> 60 -> 50 Output: 10 -> 20 -> 30 -> 40 -> 50 -> 60 Solution Code class Solution: def mergeSort(self, head): if not head or not head.next: return head mid = self.getMiddle(head) next_to_mid = mid.next mid.next = None left = self.mergeSort(head) right = self.mergeSort(next_to_mid) return self.merge(left, right) def getMiddle(self,...
Read more