# Ticket Craze
### Base: Basic Assumptions

Only frontend + Node.js + MySQL

User places order
→ MySQL deducts inventory (UPDATE skus SET stock = stock - 1)
→ If successful → redirect to payment
→ Payment success → write order + order_items
→ Payment failure → MySQL adds inventory back

### Problem 1: Why Use Redis

10,000 people simultaneously grab **the same inventory row**:
`UPDATE skus SET stock = stock - 1 WHERE id=1 AND stock>0;`
MySQL will:
- Lock that product's inventory row (row lock)
- Wait to execute inventory deduction operation
- Release lock after execution completes

When many users simultaneously grab the same SKU, everyone is accessing the same row:

User1 updates inventory (locks row)
User2 waits
User3 waits
...
User1000 all waiting

→ Row lock queue blocks → MySQL CPU spikes → Request timeout

| Phenomenon | Reason |
| ----------------- | ------------------- |
| Large number of requests queuing | Only one thread can update the same sku |
| MySQL CPU spikes, latency increases | Lock contention is very intense |
| Even overselling | Concurrent reads + delayed writes cause inventory inconsistency |
| System directly freezes | Request chain blocking |
| MySQL crashes | Connection pool explodes under high concurrency, database latency jitters |

Overselling will occur in: check first → deduct inventory, operations that don't satisfy atomicity.

SELECT stock FROM skus WHERE id=1;   -- Read inventory
UPDATE skus SET stock = stock - 1 WHERE id=1 AND stock > 0;  -- Deduct inventory

The following guarantees atomicity, but is not completely safe, because high concurrency leads to large lock waits + complex business logic (payment, rollback failures, etc.) may ultimately still cause inventory inconsistency.

UPDATE skus SET stock = stock - 1 WHERE id = 1 AND stock > 0;

Reasons for introducing Redis:

| Redis Feature | Benefits |
| ---------------------------- | ------------------------------------------ |
| **Memory-based operations** | Access speed is orders of magnitude faster than MySQL (microsecond-level response), can handle 100,000+ QPS. |
| **Single-threaded model + atomic commands (e.g., DECR)** | Naturally guarantees atomic operations, no need for explicit locking, avoids concurrency competition. |
| **Lightweight data structures (String/Hash/List)** | Suitable for storing hot inventory, flash sale queues and other lightweight high-frequency data. |
| **Can set expiration time, rate limiting strategies** | Prevents malicious requests or abnormal traffic impact. |

Flow after introducing Redis

User places order → Redis checks inventory → Deduct inventory (DECR)
    ↓
If deduction succeeds:
    → Generate order request (asynchronously write to MySQL)
If deduction fails (returns negative):
    → Show sold out

### Problem 2: Why Introduce Kafka (Instead of Just Redis + MySQL + Node.js)

Even with Redis, new problems arise:

- Redis operations are fast, but **Redis → MySQL writes** are still synchronous;
- If thousands of orders need to be persisted after payment success, MySQL write pressure is still high;
- Once write exceptions occur (network jitter, downtime, timeout), inventory and order data will be inconsistent.

### Kafka Can Solve:

| Problem | Kafka's Solution |
| ------------------ | -------------------------------------- |
| **High concurrency MySQL write pressure** | Kafka acts as a "buffer layer", first asynchronously writes to message queue, consumer side then slowly writes to MySQL. |
| **High system coupling** | Kafka decouples order logic, inventory logic, payment logic, allowing each module to scale independently. |
| **Message loss risk** | Kafka has persistence, acknowledgment mechanisms, ensuring messages are reliable and not lost. |
| **Traffic shaping** | Accumulate messages during peak hours, then smoothly write to database afterward. |
| **Supports async processing** | User order can immediately return "queued", subsequent processing by consumer async persistence. |

**Kafka Keywords: Decoupling, Async, Traffic Shaping, Scalability.**

#### Summary: Why Introduce Redis + Kafka

The core path during flash sales is almost entirely completed in memory;
MySQL is protected at the Kafka consumer side, avoiding direct high concurrency impact;
The system has **high performance, high availability, scalability, eventual consistency** characteristics.

### Problem 3: Database Design (Schema)

![[Pasted image 20251106151613.png]] (https://dbdiagram.io/d)
#### 3-1: Core Data
- **orders (Order)**: Represents "what the user wants to buy" — order is business intent + total amount + status (waiting, confirmed, cancelled, expired, etc.).
- **order_items (Order Details)**: Represents specific product lines in the order (each line has one sku, quantity, price snapshot at that time). It ensures historical orders won't be wrong in the future due to product price or description changes.
- **payments (Payment/Transaction)**: Represents "money flow/status" — may be pending, success, failure, refund, etc., and is closely related to third-party payments (callbacks, retries, partial refunds, reconciliation).

#### 3-2: Why Can't They Be Combined Into One Table?
- **Responsibility confusion**: order simultaneously responsible for describing products + recording cash flow, will cause field bloat, logic entanglement (order placement, payment, refund, reconciliation mixed together).
- **Different lifecycles**: order status (PENDING → CONFIRMED) and payment status (PENDING → COMPLETED / FAILED / REFUNDED) have high asynchrony, putting them together causes update conflicts and complex lock contention.
- **Multiple payments situation**: One order may have **multiple payment attempts**, installments or partial refunds. If there's only one orders table, it's hard to represent "one order multiple transactions".
- **Historical correctness**: Product price/name changes after order placement. If price isn't snapshotted to order_items, historical order amounts may not be reproducible.
- **Performance and scaling**: Different read/write patterns (order placement writes order+items; payments frequently update payments and have third-party callbacks), separate tables can independently optimize indexes, sharding and expansion strategies.

#### 3-3: Relationships (ER, One-to-Many Specific Description)
- `orders (1) —— (N) order_items`
One order has multiple detail lines. `order_items.order_id` points to `orders.id` (your schema uses FK with ON DELETE CASCADE, very reasonable: delete order details when order is deleted).
- `orders (1) —— (N) payments` (usually 0..N)
One order can have no payment (PENDING), or multiple payment attempts or multiple transactions (e.g., first payment fails then retry payment, refund records, etc.). `payments.order_id` points to `orders.id`.
- `order_items.sku_id` points to `skus.id` (associates with product catalog, but price is snapshot stored in order_items.price)
- `inventory_audit.ref_id` can record `order id` or `reservation id`, as audit trail

#### 3-4: Typical Data and Field Design Considerations

- `order_items.price`: **Price snapshot**. Cannot be replaced with current product price, because historical orders need to be reproducible.
- `orders.total`: Convenient for reading (but can be calculated from order_items for validation); common practice is dual storage (redundancy) with validation mechanism.
- `payments.transaction_id`: Record third-party returned transaction ID, used for reconciliation and queries; set unique at DB level (or at least index).
- `orders.expires_at`: Supports "unpaid auto-cancel" mechanism (expired releases inventory).
- `inventory_audit`: append-only, used for inventory change tracking and backtracking (must have).

1. User clicks place order (Create Order)
   → Try DECR inventory in Redis (only continue if successful)
   → Write to orders (PENDING) and order_items (price snapshot)
   → Write inventory_audit (reserve / decrease)

2. Redirect to payment (or return to frontend waiting for payment)
   → Create payments (status = PENDING, amount = orders.total)

3. Third-party payment callback
   → Payment success: update payments.status = COMPLETED and record transaction_id
                update orders.status = CONFIRMED
   → Payment failure: update payments.status = FAILED (can retry)
                If order needs rollback: release Redis inventory / write inventory_audit increase inventory

4. Refund
   → Create new payment (or set REFUNDED on payments), generate reconciliation record, and write inventory_audit (if need to restore inventory)

### Next Chapter
Ticket-Craze: Redis- https://hackmd.io/@chaodotcom/H1pwATjkWe