Transaction?

A transaction is a way for an application to group several reads and writes(INSERT INTO, SELECT x FROM) together into 1 logical unit.
Its upto Application to decide whether it wants every read/write to be written directly into DB or group several statements as 1 transaction

Application --INSERT INTO--> |DB Manager| --> | DB |

Modes of Transactions

	AutoCommit (Default Mode)	Explicit Mode
What	Every INSERT is a seperate transaction Application does not call any dbmgr API, its default mode. Every read/write is a seperate transaction That means every insert is directly written into DB When you connect to the database and execute an INSERT command, the driver immediately sends a hidden BEGIN before the command and a hidden COMMIT immediately after it finishes. Every single command you run is its own, isolated, permanent transaction sequenceDiagram autonumber participant App as Application Code participant DBMgr as Database Manager participant DBStorage as Database Storage (Tables) App->>DBMgr: INSERT INTO Users (id=1) Note over DBMgr: DBMgr adds [BEGIN],[COMMIT] before and after INSERT statement DBMgr->>DBStorage: {Begin}Write id=1{End} App->>DBMgr: INSERT INTO Users (id=2) DBMgr->>DBStorage: {Begin}Write id=2{End} DBMgr-->>App: Success ACK	x number of operations are grouped as 1 transaction Application want dbmgr, to group x reads/writes into 1 transaction To group multiple operations(INSERT, DELETE etc) into one logical unit, the application must override the default behavior by sending specific SQL commands(`db.begin_transaction()`) before executing the data manipulation statements. Commit: The application sends multiple data packets (e.g., INSERT, UPDATE, DELETE statements). The DBMgr writes these changes to a Temporary log (Write-Ahead Log) rather than immediately writing to DB, once application sends Commit, these are written to DB. The application sends a final COMMIT to save everything DB Rollback(error occured): Application sends Rollback to discard all changes. Once the database Rollback, it deletes entry from Temporary(WAL log) sequenceDiagram autonumber participant App as Application Code participant DBMgr as Database Manager participant DBStorage as Database Storage (Tables) App->>DBMgr: db.begin_transaction() Note over DBMgr: START TRANSACTION App->>DBMgr: INSERT INTO Users (id=2) Note over DBMgr: Staged changes in Temporary Log App->>DBMgr: INSERT INTO Users (id=3) Note over DBMgr: Staged changes in Temporary Log App->>DBMgr: db.commit() DBMgr->>DBStorage: write id=2 and id=3 DBMgr-->>App: Success

Why Transaction is needed?

1. Atomicity:
- if Application is writing 20KB json document to DB. After 10KB node crashes
- User will see partially updated values which is Wrong. if write is done as part of transaction into Atomic DB, partial write is discarded and db moves to prev state
2. Safety Gurantees

	Dirty Read	Dirty Write
1. Read Committed	No	No

Terms

Read Related

Term	Description
Dirty Read	A transaction is writing some row to the database, but the transaction got aborted inbetween, ie data is not committed. if another transaction can see that uncommitted data, it is a dirty read To Prevent Dirty Read? Database remembers both the old committed value and the new value set by the transaction. Currently ongoing write transaction holds the write lock. While the transaction is ongoing, any other transactions that read the object are simply given the old value. Only when the new value is committed do transactions switch over to reading the new value.
Read Skew / Non repeatable read	Say Alice has $1,000 of savings at a bank, split across two accounts with $500 each. Now a transaction transfers $100 from one of her accounts to the other. If she looks at balance at same moment she might find 500$ in one, 400$ in another Solution: Snapshot Isolation

Term

Description

Dirty Read

A transaction is writing some row to the database, but the transaction got aborted inbetween, ie data is not committed.
if another transaction can see that uncommitted data, it is a dirty read
To Prevent Dirty Read? Database remembers both the old committed value and the new value set by the transaction. Currently ongoing write transaction holds the write lock. While the transaction is ongoing, any other transactions that read the object are simply given the old value. Only when the new value is committed do transactions switch over to reading the new value.

Read Skew / Non repeatable read

Say Alice has $1,000 of savings at a bank, split across two accounts with $500 each. Now a transaction transfers $100 from one of her accounts to the other. If she looks at balance at same moment she might find 500$ in one, 400$ in another
Solution: Snapshot Isolation

Write Related

Term Decription

Dirty Write Two transactions concurrently try to update the same row in a database. 1st write transaction was uncommitted, data in DB is in inconsistent state
On top of this incorrect data, 2nd transaction writes, this is Dirty write.
To prevent dirty write? Take lock over row then write a transaction, if transaction fails revert the change and then release the lock

Write Skew / Phantom

Term	Decription
Dirty Write	Two transactions concurrently try to update the same row in a database. 1st write transaction was uncommitted, data in DB is in inconsistent state On top of this incorrect data, 2nd transaction writes, this is Dirty write. To prevent dirty write? Take lock over row then write a transaction, if transaction fails revert the change and then release the lock
Write Skew / Phantom	Race condition caused when 2 transactions update different rows in same table after reading common data from table Example: There is a hospital where atleast 1 doctor is required to be on shift(always) in hospital. Doctors can giveup their shifts if they are Sick, provided atleast 1 of their collegue stays in hospital. Alice & Bob are 2 doctors(on particular shift) feeling unwell and decide to request leave. Both click "Apply leave" button at same time. `Table doctors name on_call Alice true Bob true Carol false Alice doctors select * from doctors where on_call = true Bob <--------- select * from doctors --------> where on_call = true if(on_call>2) update doctors set on_call=false where name=Alice --txn1--> if(on_call>2) commit txn1 update doctors set on_call=false <-txn2-- where name=Alice commit txn2 Table doctors name on_call Alice false Bob false Carol false` Lock cannot solve the issue, Since both transactions are updating DIFFERENT ROWS in DB. Solution: - if both transactions should have run one after other, txn2 will get (on_call<2) and write would have been prevented.

Race condition caused when 2 transactions update different rows in same table after reading common data from table
Example:
There is a hospital where atleast 1 doctor is required to be on shift(always) in hospital.
Doctors can giveup their shifts if they are Sick, provided atleast 1 of their collegue stays in hospital.
Alice & Bob are 2 doctors(on particular shift) feeling unwell and decide to request leave.
Both click "Apply leave" button at same time.


Table doctors
name    on_call
Alice   true
Bob     true
Carol   false

    Alice                 doctors
  select * from doctors
  where on_call = true                Bob
                <---------         select * from doctors
                            --------> where on_call = true
   if(on_call>2)
   update doctors
   set on_call=false
   where name=Alice --txn1-->
                                    if(on_call>2)
                        commit txn1   update doctors
                                      set on_call=false
                           <-txn2--  where name=Alice
                        commit txn2
Table doctors
name    on_call
Alice   false
Bob     false
Carol   false

Lock cannot solve the issue, Since both transactions are updating DIFFERENT ROWS in DB.
Solution: - if both transactions should have run one after other, txn2 will get (on_call<2) and write would have been prevented.

Distributed Transaction

What: A database operation that updates data across 2 or more network seperated databases
Features of Distributed Transaction
1. All Commit or All Abort: Either all nodes commit or all nodes abort.
2. ACID: Distributed transaction should follow ACID

Methods to Achieve Distributed Transactions

1. Two Phase Commit (2PC) (Also called XA Transaction)

External transaction coordinator (e.g., Zookeeper) manages the commit process across multiple databases.
This is 1 of method of Consensus

Consider a Example of a pizza ordering system:
Where the order service acts as the coordinator, which generates a globally unique transaction ID
kitchen service and transaction service are participants.

sequenceDiagram
    actor Client
    participant OrderSvc@{ "type": "control" } as Order Service
acts as COORDINATER
    participant KitchenSvc@{ "type": "control" } as KitchenSvc
    participant TxSvc@{ "type": "control" } as TransactionSvc

    box Shards
        participant KitchenDB@{ "type": "database" } as KitchenDB
        participant TxDB@{ "type": "database" } as TransactionDB
    end

    Client->>OrderSvc: --Pizza 249-->

    Note over OrderSvc, TxDB: PHASE-1 (Coordinator asks to Lock DB)
    Note over OrderSvc: Generate globally
unique transactionId=1
    OrderSvc->>KitchenSvc: Prepare(trxnid=1)
    KitchenSvc->>KitchenDB: Check ingredients
    KitchenDB-->>KitchenSvc: available
    KitchenSvc->>KitchenDB: Lock DB
    Note over KitchenSvc: DB Locked
    KitchenSvc-->>OrderSvc: Ready

    OrderSvc->>TxSvc: Prepare(trxnid=1)
    TxSvc->>TxDB: Validate payment method
    TxDB-->>TxSvc: available
    TxSvc->>TxDB: Lock DB
    Note over TxSvc: DB Locked
    TxSvc-->>OrderSvc: Ready

    Note over OrderSvc, TxDB: PHASE-2 (Coordinator confirms to Commit)

    OrderSvc->>KitchenSvc: Commit(trxnid=1)
    KitchenSvc->>KitchenDB: Write into DB
    Note over KitchenSvc: Lock Released
    KitchenSvc-->>OrderSvc: Done

    OrderSvc->>TxSvc: Commit(trxnid=1)
    TxSvc->>TxDB: Write into DB
    Note over TxSvc: Lock Released
    TxSvc-->>OrderSvc: Done

Steps
1. Coordinator generates globally unique transactionId and sends to microservices. Now-on-wards microservices will use same id for communication.
2. Coordinator sends Prepare message(with transactionId) asking microservices whether they can commit or not?
3. When microservices recieves prepare, it makes sure that it can commit to DB at any cost.
- Even if microservices crashes After sending Yes. Then after recovery it has to commit the transaction.
- There is no turning Back.
5. Coordinator recieves response from all nodes. This is called COMMIT POINT
6. Coordinator writes its decision to Transaction log. so that if any microervice responds with No, whole commit can be moved to prev state.
7. Phase-2: Coordinator Send Message to commit. microervice Commit ie write to DB else //if any one says no Phase-2: Send Abort Message to all microervice via coordinator

DOUBT/UNCETAIN STATE:
- Coordinator sends prepare, microervice voted Yes, and Coordinator crashes.
Now microervices are in Doubt state. microervices will wait forever for Coordinator to send Phase-2 message(Commit or Abort).

HEURISTICS DECISIONS:
Microervices can communicate & decide amongst themseleves once did not hear from Coordinator for particular time.

Problems with 2PC

1. Not supported by modern technologies Eg: NoSQL DB does not support. Apache Kafka, Cassandra, RabbitMQ does not support.
2. 2PC is form of Synchronous RPC: Kitchen microservice is waiting, holding locks If coordinator crashes → Kitchen blocks indefinitely and even cannot write other transactions
Solution: Saga Design Pattern

2. Saga Design Pattern

Dictionary Meaning of Saga:
A long, epic story or narrative detailing a series of adventures, events, and twists over time. Just like an epic story has multiple chapters that unfold sequentially—where a plot twist or failure in chapter three might force the characters to backtrack or undo what they did in chapter one
Saga design pattern is carried over multiple microservices, where each microservice does its own local DB transaction, listen on message & also compensate or revert the DB write if something goes wrong.

Where Saga can be used?
1. Distributed database updates(ie Distributed Transactions)
2. Anywhere in long-running business process or workflow that spans multiple isolated systems that cannot use standard ACID transactions.
Examples:
E-Commerce Checkout & Fulfillment: Many microsservices, many databases
Travel Booking: Flight, Hotel, Car Rental all are separate systems
Loan Application: Credit Check, Risk Assessment, Document Verification. All are separate systems

Concept of Saga
- A Saga is an architectural pattern for managing distributed transactions across multiple independent microservices without locking data globally.
- Microservice 1 performs a DB/Shard write to its database and publishes an event to Kafka, Microservice 2 consume this event to execute the next local write(on its DB).
- If any step down the line fails, the saga sequentially triggers compensating transactions (undo actions) via event messages to maintain eventual consistency across the system.

Twitter System Example

sequenceDiagram
autonumber

    actor Client
    participant GLB@{ "type": "boundary", "alias": "Global LB<br/>Regional LB" }
    box Pink Datacenter
    participant LB@{ "type": "boundary", "alias": "K8s Ingress" }
    participant Pod@{ "type": "control", "alias": "Recvr Pod" }
    participant Shard_01@{ "type": "database", "alias": "Shard1" }
    participant Shard_1@{ "type": "database", "alias": "Shard2" }
    participant Kafka@{ "type": "queue", "alias": "Kafka Queue" }
    participant Worker@{ "type": "control", "alias": "Consumer Pod" }
    participant Shard_02@{ "type": "database", "alias": "Downstream<br/>Timeline<br/>Shard" }
    end

    Client->>GLB: HTTP POST /tweet
(userId: 9999, content: "Hello")
    GLB->>LB: POST Tweet
    LB->>Pod: POST Tweet
    
    Note over Pod: In Memory Calculation
Same for every Pod

shardId = hash(userId)%num of Shards
    
    Pod->>Shard_01: Write Tweet to Shard_01
    Shard_01-->>Pod: Acknowledge Write
    
    Pod->>Kafka: Topic:TweetCreated
Message(userId: 9999)
    Pod-->>Client: 200 OK (Response sent to user)

    Note over Kafka, Shard_02: Asynchronous Saga / Fan-out Phase
    Kafka->>Worker: Topic:TweetCreated
Message(userId: 9999)
    Note over Worker: In Memory Calculation
Same for every Pod

shardId = hash(userId)%num of Shards
OR
Read from event metadata
    Worker->>Shard_02: Write to User Timeline Shard
    Shard_02-->>Worker: Acknowledge

(Steps 1–4)
Rather than consulting a central coordinator, the Recvr Pod independently computes the destination shard using local CPU memory (hash(userId) % num of Shards).
This eliminates network bottlenecks and allows any stateless pod to process the incoming request identically without external lookups.
(Steps 5–6)
The pod directly writes the tweet to Shard1 (Shard_01) and receives an acknowledgment
(Steps 7–8)
Instead of Pod publishes a TweetCreated event to Kafka and immediately returns a 200 OK response to the client.
(Steps 9–11)
Consumer Pod picks up the event from Kafka to execute the next phase of the workflow. It determines the target shard (either via local hash calculation or by reading metadata directly from the event payload) and writes the data to the Downstream Timeline Shard (Shard_02).