Transaction & Data Analytics


Understanding Transactions: The Foundation {#understanding-transactions}

What Exactly IS a Transaction?

A transaction is a collection of database operations that must be treated as a single, indivisible unit of work. Think of it like ordering food at a restaurant:

  1. You order (START the transaction)

  2. Kitchen prepares food (Execute operations)

  3. You pay (More operations)

  4. You receive food (Final operations)

  5. Transaction complete (COMMIT)

If ANY step fails (kitchen burns food, payment declined, etc.), the ENTIRE order is cancelled - you don't get charged, kitchen doesn't waste ingredients, everyone goes back to the starting state.


2. The ACID Properties Explained {#acid-properties}

The ACID properties are the fundamental guarantees that make transactions reliable and trustworthy.


A - ATOMICITY: "All or Nothing" Principle

Definition: Either ALL operations in a transaction complete successfully, or NONE do.



C - CONSISTENCY: "Rules Must Always Be Obeyed"

Definition: Transactions bring the database from one valid state to another, never violating business rules or constraints.



I - ISOLATION: "Transactions Don't Step on Each Other"

Definition: Multiple transactions can run simultaneously without interfering with each other.


Different types of Isolation:

1. READ UNCOMMITTED - "I Can See Dirty Laundry"


When to Use READ UNCOMMITTED:

  • Quick reports where approximate data is acceptable

  • Performance-critical queries where precision isn't essential

  • Dashboard queries showing rough metrics


2. READ COMMITTED - "I Only See Clean Laundry" (PostgreSQL Default)

Characteristics of READ COMMITTED:

  • Prevents dirty reads

  • Allows non-repeatable reads

  • Allows phantom reads

  • Good balance of consistency and performance

  • Best for most business operations


3. REPEATABLE READ - "What I See Stays the Same"

When to Use REPEATABLE READ:

  • Financial calculations that must be internally consistent

  • Reports where data shouldn't change during generation

  • Batch processing where consistency across multiple queries is crucial


When to Use SERIALIZABLE:

  • Critical financial operations

  • Data integrity is more important than performance

  • Transactions that must appear to run sequentially


D - DURABILITY: "Once Done, Never Undone"

Definition: Once a transaction is committed, its changes are permanent and survive system failures.

2. Transaction Isolation Levels in Detail {#isolation-levels}


Read Phenomena Explained

1. Dirty Read

Problem: Reading uncommitted data that might be rolled back.


2. Non-Repeatable Read: Existing Rows Change

Key point: The row already existed - its values just changed. You're dealing with the same physical row but different data.


3. Phantom Read: New Rows Appear/Disappear

Key point: A new row appeared that wasn't there before. It's like a "phantom" or "ghost" row.


Think of it like this:

  • Non-Repeatable Read: "The book I was reading changed its content while I was reading it"

  • Phantom Read: "New books appeared on the shelf while I was counting them"


4. Concurrency Control Problems {#concurrency-problems}

The Problem: When multiple people (transactions) try to use the database at the same time, they can mess up each other's work and corrupt the data.

Think of it like this: Imagine you and your friend are both editing the same Google Doc simultaneously. Without proper controls, you might:

  • Overwrite each other's changes

  • See half-finished edits

  • End up with a completely messed up document

With Concurrency Control: The database ensures transactions don't interfere with each other by:

  • Locking resources (like "only one person can edit this row at a time")

  • Isolation levels (controlling what changes transactions can see)

  • Timestamps (ordering transactions properly)



1. Lost Update Problem

Scenario: Two cashiers updating the same product's stock

2. Uncommitted Data (Dirty Read)

Scenario: Online shopping - checking if you can afford something

What happened:

  • The system temporarily took money for the laptop

  • While processing, another part of the system checked your balance

  • It saw the reduced amount (dirty/uncommitted data)

  • Made a wrong decision based on data that was later cancelled

Real life equivalent: Like someone checking your wallet while you're temporarily holding money to pay for something, but then you put the money back because the item wasn't available.


3. Inconsistent Retrievals

Scenario: Reading data while it's being moved around

  • Lost Update: Changes get overwritten

  • Uncommitted Data: Reading changes that might get cancelled

  • Inconsistent Retrieval: Reading data while it's being modified


LOCKS:
Binary Lock (Simple Lock)

Two states only: LOCKED or UNLOCKED

Problem: Even if you just want to READ data, you have to wait. Very restrictive!

Shared/Exclusive Lock

Shared Lock (S-Lock) - "Reading Lock"

  • Multiple transactions can have shared locks on same data

  • Used for reading only

  • Compatible with other shared locks

Exclusive Lock (X-Lock) - "Writing Lock"

  • Only ONE transaction can have exclusive lock

  • Used for writing/updating

  • NOT compatible with any other locks