SQL Expert Lab — Distributed Transactions and Sagas (Expert)

🖥️

SQL Terminal

📚

Reference

🗂️

Schema

⭐⭐⭐⭐⭐ EXPERT OBJECTIVES

○Design outbox table for reliable event publishing

○Create saga_instances table with state machine

○Implement compensating transactions for each step

○Build saga_orchestrator() stored procedure

○Simulate stale saga detection (timeout query)

○Implement idempotent retry logic (idempotency_key)

○Add saga timeout + dead letter handling

○Validate: 100 concurrent sagas, zero inconsistencies

0 / 8 complete

EXPERT SQL TERMINAL — DISTRIBUTED TRANSACTIONS AND SAGAS (EXPERT)

PostgreSQL 16 — Expert Lab Environment loaded ⭐ Distributed Transactions and Sagas (Expert) — Expert Tier ⚠ No guidance provided. Mastery is assessed on correctness AND trade-off analysis. ⚠ Use -- comments or REPORT to document every design decision. ──────────────────────────────────────────

pg=#

KEY COMMANDS

CREATE TABLE outbox (id UUID PRIMARY KEY DEFAULT gen_random_uuid(), aggregate_...
CREATE TABLE saga_instances (id UUID PRIMARY KEY, saga_type TEXT, current_step...
CREATE PROCEDURE compensate_payment(p_saga_id UUID) LANGUAGE plpgsql AS $$ BEG...
UPDATE saga_instances SET current_step='payment',updated_at=NOW() WHERE id=? A...
SELECT * FROM saga_instances WHERE status='running' AND updated_at < NOW()-INT...
INSERT INTO outbox(aggregate_type,event_type,payload) VALUES ('payment','Payme...

Expert tier: syntax starting points only. Your reasoning counts as much as the SQL.

SCHEMA BROWSER

Type \d or SHOW TABLES to list tables, SHOW SEARCH_PATH for active schema.

EXPERT BRIEFING

DISTRIBUTED TXN — APEX PAYMENTS MESH — EXPERT ⭐⭐⭐⭐⭐

⭐⭐⭐⭐⭐ Expert tier. No walkthrough. No syntax examples beyond the reference window. You are assessed on correctness, justification of trade-offs, and depth of reasoning.

SCENARIO

Apex Payments runs a microservices mesh where a single payment touches 5 databases across 3 services. A failed payment mid-flight leaves orders in limbo and inventory double-counted. You must implement the Saga pattern using the Outbox pattern for reliable messaging, design compensating transactions, build a saga orchestrator table, and handle partial failure recovery — including a network partition mid-saga scenario.

OBJECTIVES (8)

Design outbox table for reliable event publishing
Create saga_instances table with state machine
Implement compensating transactions for each step
Build saga_orchestrator() stored procedure
Simulate stale saga detection (timeout query)
Implement idempotent retry logic (idempotency_key)
Add saga timeout + dead letter handling
Validate: 100 concurrent sagas, zero inconsistencies

EXPERT RULES

Every DDL decision must be justified with a -- comment or REPORT statement
Trade-off analysis is required for all design choices
All 8 objectives must be completed before the report is accepted
Reference window provides syntax starting points only

Display Mode