Dynamic Consistency Boundary (conditional put options)

readme.md

Conditional Put: Three Approaches Compared

1. Our Approach (Serializable Snapshot Isolation)

Repo.transaction(
  fn ->
    if has_events_since?(filters, max_position) do
      Repo.rollback(:inconsistent)
    else
      {:ok, schema} = put(message)
      schema
    end
  end,
  isolation: :serializable
)

• Uses PostgreSQL's Serializable Snapshot Isolation (SSI)
• Check and insert are separate queries within the transaction
• PostgreSQL detects conflicts at commit time via predicate locks
• Aborted transactions raise serialization_failure error

2. Python eventsourcing Approach (Exclusive Table Lock)

Source: postgres_tt.py

-- Inside a stored procedure:
LOCK TABLE events IN EXCLUSIVE MODE;  -- Block ALL other writers
-- Check for conflicts
SELECT EXISTS (SELECT 1 FROM filtered_ids) INTO conflict_exists;
-- Insert if no conflicts
IF NOT conflict_exists THEN
    INSERT INTO events ...
END IF;

• Uses explicit table-wide lock (EXCLUSIVE MODE)
• Blocks ALL writers while allowing readers
• Check and insert happen while holding the lock
• No conflict possible because all writers are serialized

3. Orisun Approach (Advisory Locks per Criteria)

Source: db_scripts_1.sql

-- Extract criteria tags and lock each one
SELECT ARRAY_AGG(DISTINCT criterion::text) INTO criteria_tags
FROM jsonb_array_elements(criteria) AS criterion;

-- Sort alphabetically for deadlock prevention
criteria_tags := ARRAY(SELECT DISTINCT unnest(criteria_tags) ORDER BY 1);

-- Acquire advisory lock for each criterion
FOREACH key_record IN ARRAY criteria_tags LOOP
    PERFORM pg_advisory_xact_lock(hashtext(key_record));
END LOOP;

-- Check for conflicts (only within locked criteria)
SELECT transaction_id, global_id FROM events
WHERE data @> ANY(criteria)
ORDER BY transaction_id DESC, global_id DESC
LIMIT 1 INTO latest_tx_id, latest_gid;

-- Version check
IF latest_tx_id <> expected_tx_id OR latest_gid <> expected_gid THEN
    RAISE EXCEPTION 'OptimisticConcurrencyException:StreamVersionConflict';
END IF;

-- Insert events
INSERT INTO events ...

• Uses advisory locks (pg_advisory_xact_lock) per criteria/tag-set
• Only blocks writers with overlapping criteria - fine-grained
• Locks acquired in alphabetical order to prevent deadlocks
• Writers with different tag-sets can proceed in parallel

Comparison Table

Aspect	SSI (Ours)	Exclusive Lock (Python)	Advisory Locks (Orisun)
Lock granularity	None (optimistic)	Table-wide	Per criteria/tag-set
Writer parallelism	Full (until commit)	None (serialized)	Partial (non-overlapping OK)
Reader impact	Never blocked	Never blocked	Never blocked
Conflict detection	At commit time	Before insert	Before insert
Wasted work	Yes (on conflict)	No	No
Deadlock risk	None	None	Possible (mitigated by ordering)
Code complexity	Simple	Medium (stored proc)	High (stored proc + lock mgmt)
Lock wait time	None	All writers wait	Only overlapping writers wait

Tradeoffs Summary

SSI (Our Approach)

Pros:

• Simplest code - no stored procedures or lock management
• Full parallelism for non-conflicting writes
• No lock contention or waiting
• Relies on PostgreSQL's well-tested SSI implementation

Cons:

• Wasted work when conflicts occur (full transaction executes before abort)
• Higher retry rates under contention
• SSI predicate lock tracking overhead
• May not detect all conflicts with complex join queries

Exclusive Table Lock (Python)

Pros:

• No wasted work - conflict detected before insert
• Guaranteed success once lock acquired
• Predictable behavior
• Simple mental model

Cons:

• Serializes ALL writers regardless of whether they conflict
• Poor throughput when many concurrent writers
• Lock wait time adds latency
• Doesn't scale horizontally

Advisory Locks per Criteria (Orisun)

Pros:

• Best of both worlds - parallel for non-overlapping, serialized for overlapping
• No wasted work
• Scales better than table lock
• Fine-grained control

Cons:

• Most complex implementation
• Requires stored procedure
• Deadlock risk (mitigated by lock ordering)
• Lock management overhead
• Hash collisions possible (unlikely)

Visual Comparison

Scenario: 3 concurrent writers
  Writer A: tags [booking:123]
  Writer B: tags [booking:456]  
  Writer C: tags [booking:123]  (conflicts with A)

SSI (Ours):
  A ─────────────────────> COMMIT ✓
  B ─────────────────────> COMMIT ✓
  C ─────────────────────> COMMIT ✗ (serialization_failure, retry)
  
  All run in parallel, C fails at commit time.

Exclusive Lock (Python):
  A ─────────────────────> COMMIT ✓
       B (waiting)────────────────> COMMIT ✓
            C (waiting)────────────────> COMMIT ✓ (sees A's event, may decide differently)
  
  All serialized, no wasted work, but slow.

Advisory Locks (Orisun):
  A ─────────────────────> COMMIT ✓
  B ─────────────────────> COMMIT ✓  (parallel with A - different tags)
       C (waiting on A)───────────> COMMIT ✓ (sees A's event)
  
  A and B parallel, C waits only for A.

Recommendation

Our SSI approach is appropriate for typical DCB use cases where:

• Conflicts are rare (different bookings, different entities)
• Simplicity is valued over optimizing edge cases
• Caller already handles {:error, :inconsistent} for business logic

Consider advisory locks if we observe:

• High retry rates on specific high-contention tag-sets
• Need for better throughput under concurrent writes to same tags
• Acceptable complexity tradeoff

Consider exclusive lock only if:

• Write volume is low enough that serialization is acceptable
• Simplicity of "one writer at a time" is preferred
• All writes go through a single service instance anyway