Kafka · Redis
· Oracle SQL Infrastructure depth that separates leads

• ZooKeeper/KRaft detects broker failure (via heartbeat timeout)
• Controller broker elects a new leader from the ISR list for each affected partition
• Producers and consumers update their metadata and reconnect to new leader — transparent with retries
• If ISR has only the dead leader — partition becomes unavailable (acks=all) OR an out-of-sync replica can be elected if unclean.leader.election.enable=true (risk: data loss)
• For payments: always keep unclean.leader.election.enable=false — prefer unavailability over data loss

Kafka ordering is per-partition, not per-topic. Guaranteed only within a single partition.

• Use message key: set key=accountId or key=paymentId → same key always routes to same partition via hashCode() % numPartitions
• Ordering breaks with retries: if producer sends msg1, msg2 and msg1 fails → retry → msg2, msg1 order. Fix with max.in.flight.requests.per.connection=1 (but kills throughput)
• Better fix — idempotent producer: enable.idempotence=true assigns sequence numbers. Broker deduplicates + reorders. Allows max.in.flight=5 safely
• Partition count change: adding partitions breaks key→partition mapping for existing keys — all historical ordering breaks. Plan partition count upfront.

• Try-catch in consumer: catch deserialisation/processing errors, don't throw — prevents endless retry loop
• Dead Letter Topic (DLT): on failure after N retries, produce the bad message to payments.DLT — preserve it for analysis without blocking main topic
• Spring Kafka @RetryableTopic: handles retry + DLT automatically with exponential backoff. Retries go to payments-retry-0, payments-retry-1… final fail → DLT
• Alert on DLT: monitor DLT size — any message in DLT = P2 alert in payments
• Never commit a failed offset: that permanently skips the message

• Probabilistic early refresh: at 80% of TTL, randomly start refreshing — spreads load, no hard expiry cliff
• Distributed mutex (SETNX): on cache miss, first thread acquires lock, loads DB, populates cache. Other threads wait briefly then read from cache. Prevents N threads hitting DB simultaneously.
• Stale-while-revalidate: serve stale cached value while async refresh runs in background — zero latency impact to user
• Jitter on TTL: instead of TTL=300 for all balance keys, use TTL = 300 + random(0, 30)

Single-node problem: if Redis master dies after granting lock but before replicating to replica, replica promotes → two processes think they hold the lock.

Redlock: acquire lock on N independent Redis nodes (typically 5). Lock is valid only if acquired on majority (≥3) within TTL. No single node failure breaks the guarantee.

• Try to acquire on all 5 nodes with same key + UUID + TTL
• Count successes. If ≥3 acquired within TTL window → lock held
• Effective TTL = original TTL minus time taken to acquire
• On failure or timeout → release all acquired locks

Practical: Use Redisson library — implements Redlock correctly with watchdog (auto-extends TTL while lock holder is alive).

Composite index is used left-to-right. It stops at the first missing or range column.

Oracle 9i+ supports Index Skip Scan — can use composite index even if leading column skipped, but only if leading column has low cardinality. Verify with EXPLAIN PLAN.

• Step 1 — Capture current plan: EXPLAIN PLAN FOR <query> + SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY). Look for FULL TABLE SCAN on large tables.
• Step 2 — Check statistics: stale optimizer stats cause bad plans. SELECT last_analyzed FROM user_tables WHERE table_name='PAYMENTS'. If old → EXEC DBMS_STATS.GATHER_TABLE_STATS('SCHEMA','PAYMENTS')
• Step 3 — Implicit type conversion: WHERE account_id = 12345 on VARCHAR2 column → full scan. Must match data types.
• Step 4 — Index usage: query using UPPER(col), TRUNC(date_col), or leading wildcard LIKE '%abc'? These skip B-tree indexes.
• Step 5 — Index fragmentation: high delete volume → index fragmentation. ALTER INDEX idx_name REBUILD ONLINE
• Step 6 — Lock contention: SELECT * FROM V$LOCK l JOIN V$SESSION s ON l.SID=s.SID
• Step 7 — Bind variable peeking: Oracle optimises plan for first value seen — skewed data can cause bad plan for other values. Use CURSOR_SHARING=FORCE or hints.

Classic lost update / race condition. Three approaches, choose based on contention:

• Pessimistic Locking: SELECT balance FROM accounts WHERE id=? FOR UPDATE — acquires row lock. Second transaction waits. Prevents any interleaving. Best for high-contention accounts.
• Optimistic Locking: add version column. UPDATE accounts SET balance=?, version=version+1 WHERE id=? AND version=? — if 0 rows updated, someone else changed it → retry. Best for low-contention.
• SERIALIZABLE isolation: DB detects write conflict, rolls back one transaction automatically. Higher overhead but fully automatic.

In production payments: use pessimistic locking (SELECT FOR UPDATE) for balance debit operations. Speed matters less than correctness for money movement.

Queue: message consumed by one consumer → deleted. Kafka topic: message retained on disk for configurable period. Multiple independent consumer groups can read the same message. Kafka is a durable, replayable log — fundamental architectural difference. Enables event sourcing, audit replay, new consumer groups processing historical data.

• Partition count: 100+ partitions (each partition ~10K msg/s throughput). Can scale consumers up to 100.
• Replication factor: 3 — survive 2 broker failures
• Batch producer: linger.ms=5, batch.size=65536 — batch messages before send
• Compression: compression.type=lz4 — reduces network I/O dramatically
• Consumer tuning: fetch.min.bytes, fetch.max.wait.ms — batched polling
• Separate broker disks: dedicated SSDs per Kafka broker for log segments

Log compaction retains only the latest value per key — older records with the same key are deleted during compaction. Useful for payment status topic: key=paymentId, value=latest status. Any consumer joining later gets current state without replaying full history. Used for account balance snapshots, configuration change topics.

Consumer lag = (latest partition offset) - (consumer's committed offset) = number of unprocessed messages. Monitored via: kafka-consumer-groups.sh --describe, Kafka JMX metrics, Prometheus + kafka-exporter, Grafana dashboard. Alert when lag exceeds threshold (e.g. >10,000 for payments). Growing lag = consumer bottleneck → scale consumers up to partition count, or optimise processing, or batch DB writes.

CDC (Debezium + Kafka Connect) reads DB transaction log and publishes every change event. No code changes needed — captures ALL DB changes including those from batch jobs, legacy apps, DBA scripts. Use CDC when: can't modify existing apps, need dual-write consistency, or migrating from monolith. Use producer when you control the write path and want rich event payloads.

Redis 6+ uses I/O threads for network I/O but single thread for command execution — eliminates lock overhead entirely. Speed comes from: all data in RAM (microsecond access), simple data structures, epoll/kqueue async I/O, no serialisation overhead for most operations. 100,000+ ops/sec on commodity hardware.

• RDB (Snapshot): point-in-time dump to disk periodically. Fast restore. Data loss between snapshots on crash.
• AOF (Append-Only File): logs every write command. Can sync every second or every write. Near-zero data loss.
• For payments: AOF with fsync=everysec — max 1 second data loss acceptable for cache. For distributed lock keys: must use TTL as safety net anyway.
• Best: both RDB + AOF — RDB for fast restart, AOF for durability. Redis Enterprise/Sentinel for HA.

• Fixed window: INCR rate:userId:minute → check <= 1000 → EXPIRE 60s. Simple but burst at window boundary.
• Sliding window: Sorted Set. Key=userId, score=timestamp, value=requestId. ZADD new request, ZREMRANGEBYSCORE old entries, ZCARD to count. More accurate, slightly more overhead.
• Token bucket: Store tokens in Redis. Replenish at fixed rate. Consume on each request. Smoothest limiting.

Loading N accounts and for each lazily loading payments: 1 query for accounts + N queries for payments = N+1 queries. Detected via SQL logging or Hibernate statistics. Fix: @Query("SELECT a FROM Account a JOIN FETCH a.payments WHERE...") — single JOIN query. Or @EntityGraph. Or @BatchSize(size=50) for secondary fetch. Spring JPA + lazy loading is the #1 cause of slow payment reports.

All pool connections in use — new threads wait. Symptoms: HikariPool-1 - Connection is not available, request timed out after 30000ms in logs. Causes: long-running transactions, missing connection release (forgotten close), too many threads. Diagnose: SELECT * FROM V$SESSION WHERE WAIT_CLASS='User I/O'. Fix: tune maximum-pool-size, reduce transaction scope, add @Transactional only where needed, alert on pool saturation metric.

Oracle doesn't use read locks. Instead it maintains multiple versions of data via undo segments. A reading transaction sees a consistent snapshot as-of its query start time — even if another transaction is updating the same rows. This is why Oracle's READ_COMMITTED doesn't block readers — writers never block readers, readers never block writers. Undo segments store old versions. Long-running queries can get ORA-01555 (snapshot too old) if undo is recycled before query completes.