Concurrency, Locking, and Transaction Isolation Model

1. Core Idea

Enterprise data model harus aman ketika banyak actor dan worker mengubah data yang sama pada waktu bersamaan.

Dalam CPQ / Quote / Order / Billing / Fulfillment / Inventory, concurrency terjadi ketika:

• dua user edit quote yang sama,
• sales rep submit quote saat pricing recalculation berjalan,
• quote accepted dua kali karena retry,
• order cancelled saat fulfillment completed event masuk,
• billing charge activated saat product termination sedang diproses,
• approval decision masuk saat quote revision dibuat,
• worker queue memproses outbox/inbox/task yang sama,
• rating workers memakai allowance bucket yang sama,
• backfill job update row yang juga sedang dipakai aplikasi,
• external event datang out-of-order.

Mental model:

Concurrency correctness is not only a database setting. It is a domain design: expected version, state guard, idempotency key, unique constraint, lock strategy, retry policy, and reconciliation.

2. Why Concurrency Modelling Matters

Tanpa concurrency model:

• update user hilang karena last-write-wins,
• duplicate order dibuat dari quote yang sama,
• discount approval lama menimpa quote revision baru,
• order status mundur dari COMPLETED ke IN_PROGRESS,
• product instance active dan terminated bersamaan,
• charge created dua kali,
• outbox event dipublish dua worker,
• allowance bucket double consumed,
• inventory resource double allocated,
• manual repair menimpa fulfillment update,
• support melihat state yang tidak mungkin terjadi.

Production incidents concurrency sering sulit direproduksi karena hanya terjadi pada timing tertentu. Karena itu invariants harus dilindungi dengan model, bukan asumsi.

3. Types of Concurrency Problems

Each problem requires different mitigation.

4. Aggregate Version

Aggregate version is foundational.

Example:

quote.version = 4

When updating:

Command expects version 4.
Database row is still version 4.
Update succeeds and version becomes 5.

If row is already version 5:

409 Conflict

This prevents lost update.

SQL pattern:

update quote
set status = 'SUBMITTED',
    version = version + 1,
    updated_at = now()
where id = :quote_id
  and version = :expected_version;

If affected rows = 0, command failed due to stale version or missing quote.

5. Optimistic Locking

Optimistic locking assumes conflicts are rare.

Useful for:

• quote draft editing,
• account/profile update,
• catalog draft update,
• approval request mutation,
• product configuration draft,
• UI form save.

Fields:

version
updated_at
updated_by

API support:

If-Match: "quote-v4"

or request body:

{
  "expectedVersion": 4,
  "change": {}
}

Return:

409 Conflict

when version mismatch.

Do not silently merge business-critical changes unless merge semantics are defined.

6. Pessimistic Locking

Pessimistic locking prevents concurrent update by locking row.

PostgreSQL pattern:

select *
from quote
where id = :quote_id
for update;

Useful for:

• critical lifecycle transition,
• resource allocation,
• billing charge creation,
• allowance bucket consumption,
• order state aggregation,
• one-at-a-time repair,
• queue worker claim.

Risks:

• lock wait,
• deadlock,
• lower throughput,
• user-facing latency,
• transaction too long.

Rule:

If you lock, keep transaction short and never wait on external systems while holding lock.

7. Compare-and-Set State Transition

State transitions should be atomic.

Example:

update product_order
set status = 'CANCELLED',
    version = version + 1
where id = :order_id
  and status in ('CAPTURED', 'SUBMITTED', 'IN_PROGRESS')
  and version = :expected_version;

This protects transition guard at database level.

If affected rows = 0:

• state changed,
• version stale,
• order not found,
• transition not allowed.

Application then returns conflict or reloads state.

8. State Regression Prevention

Event consumers can apply stale events.

Example:

OrderItemFulfilled event processed.
Order item status = FULFILLED.
Later old OrderItemInProgress event arrives.

Consumer must not regress state.

Strategies:

• aggregate version check,
• transition guard,
• event sequence,
• occurred_at only if reliable and monotonic per aggregate,
• state precedence,
• ignore stale event,
• record stale event in inbox/audit.

Example:

if incomingAggregateVersion <= currentProjectionVersion:
    skip as duplicate/stale

9. Idempotency and Concurrency

Idempotency protects retry and duplicate commands.

Example:

Convert quote v4 to order
idempotency key = quote_id + quote_version

Database unique key:

create unique index uq_order_from_quote_version
on product_order (source_quote_id, source_quote_version)
where source_quote_id is not null;

Even if two workers process same command, only one order can be created.

Idempotency should have:

idempotency_key
request_hash
status
result_reference
created_at
expires_at

If same key with different payload appears, return conflict.

10. Worker Queue Concurrency

Outbox/task workers need claim pattern.

PostgreSQL:

select id
from outbox_event
where status = 'PENDING'
  and (next_retry_at is null or next_retry_at <= now())
order by created_at
limit 100
for update skip locked;

Then update selected rows to PUBLISHING.

This prevents multiple workers claiming same rows.

Important:

• use bounded batch,
• avoid long transaction,
• handle worker crash,
• reset stale PUBLISHING rows after timeout,
• monitor oldest pending row.

11. Lease-Based Claim

For tasks that can outlive transaction, use lease.

Fields:

locked_by
locked_at
lock_expires_at
status
attempt_no

Worker claim:

update fulfillment_task
set locked_by = :worker_id,
    locked_at = now(),
    lock_expires_at = now() + interval '5 minutes',
    status = 'PROCESSING'
where id = :task_id
  and status = 'READY'
  and (lock_expires_at is null or lock_expires_at < now());

Lease must expire if worker dies.

But side effects must remain idempotent because task may be retried after lease expiry.

12. Deadlock Avoidance

Deadlocks occur when transactions lock resources in different order.

Example:

T1 locks quote then approval.
T2 locks approval then quote.

Prevention:

• define lock order,
• keep transactions short,
• avoid external calls inside transaction,
• index FK columns,
• update rows in deterministic order,
• avoid broad update scans,
• set lock timeout for migrations/jobs,
• retry deadlock-safe transaction.

Lock order example:

Customer -> Account -> Quote -> Quote Item -> Approval
Order -> Order Item -> Fulfillment Task
Product Instance -> Charge

Define where needed.

13. Isolation Levels

Common PostgreSQL isolation levels:

Most OLTP apps use read committed with explicit locks/version checks.

Do not assume isolation level alone protects business invariants.

Example write skew can still happen under some isolation strategies unless guarded by constraints/locks.

14. Write Skew Example

Scenario:

Approval requires two distinct approvers.
Two transactions simultaneously check only one approval exists.
Both insert one approval by same or conflicting actor path.
Together rule violated.

Mitigation:

• unique constraints,
• aggregate-level lock on approval request,
• explicit approval decision service,
• serializable transaction if justified,
• post-write invariant check with lock.

Business invariant should be enforced around aggregate, not by scattered inserts.

15. Resource Allocation Concurrency

Resource allocation must avoid double assignment.

Example:

Assign same port/IP/device to two services.

Strategies:

• allocate through single resource service,
• unique active assignment constraint,
• select for update on resource row,
• reservation table with uniqueness,
• status compare-and-set,
• capacity check with lock,
• reconciliation.

Example:

create unique index uq_active_dedicated_resource_assignment
on service_resource_assignment (resource_instance_id)
where effective_to is null
  and assignment_type = 'DEDICATED';

16. Allowance Consumption Concurrency

Usage rating can consume allowance concurrently.

Example:

Allowance = 100 GB
Two workers each see 80 GB remaining
Both consume 50 GB

Mitigation:

• aggregate usage before rating,
• lock allowance bucket row,
• deterministic single writer per bucket/period,
• optimistic update with remaining quantity condition,
• partition by allowance key,
• reconcile allowance consumption.

Compare-and-set:

update allowance_bucket
set consumed_quantity = consumed_quantity + :qty,
    remaining_quantity = remaining_quantity - :qty
where id = :bucket_id
  and remaining_quantity >= :qty;

Check affected rows.

17. Billing Charge Concurrency

Charge creation must prevent duplicates.

Example one-time charge:

order_item_id + charge_type + trigger_event

Unique key:

create unique index uq_one_time_charge_trigger
on charge (order_item_id, charge_type, trigger_event)
where charge_type = 'ONE_TIME';

Recurring charge current uniqueness:

create unique index uq_active_recurring_charge
on recurring_charge (subscription_item_id, charge_type)
where status = 'ACTIVE'
  and effective_to is null;

Actual key depends business rules.

18. Approval Decision Concurrency

Approval decisions may arrive concurrently.

Issues:

• two approvers decide same step,
• rejection and approval race,
• quote revised while approval pending,
• delegation expires during decision,
• maker-checker rule changes.

Mitigation:

• lock approval request/step during decision,
• expected target version,
• step status compare-and-set,
• append decision row,
• update request status atomically,
• check target material change.

Example:

update approval_step
set status = 'APPROVED',
    completed_at = now()
where id = :step_id
  and status = 'PENDING';

If affected rows = 0, decision is stale/duplicate/conflicting.

19. Quote Revision Concurrency

Quote revision must avoid:

• two current revisions,
• accepted revision modified,
• approval attached to old revision,
• conversion from non-current accepted revision unless allowed.

Constraints:

one current draft revision per quote family
accepted revision immutable
approval target_version required
conversion uses accepted version

Partial unique index example:

create unique index uq_current_quote_revision
on quote_revision (quote_family_id)
where is_current = true;

20. Order Cancel vs Fulfillment Complete Race

Common race:

User cancels order.
Fulfillment completed event arrives around same time.

Need state guard matrix:

Event handler must check current state.

Do not blindly set status to fulfilled on completion event.

21. Product Termination vs Billing Activation Race

Race:

Product activated event triggers billing.
Cancellation/termination effective same day.

Need:

• effective date semantics,
• product status guard,
• charge idempotency,
• billing readiness check,
• termination-aware charge activation,
• reconciliation.

Guard:

Activate recurring charge only if product is ACTIVE at billing effective date and no termination effective before billing start.

22. PostgreSQL Physical Design

Versioned aggregate:

alter table quote
add column version integer not null default 0;

create index idx_quote_id_version
on quote (id, version);

Idempotency table:

create table idempotency_record (
  id uuid primary key,
  idempotency_key text not null,
  scope text not null,
  request_hash text,
  status text not null,
  result_entity_type text,
  result_entity_id uuid,
  response_reference text,
  expires_at timestamptz,
  created_at timestamptz not null,
  updated_at timestamptz not null,
  unique (scope, idempotency_key)
);

Lease table pattern for tasks:

create table work_item (
  id uuid primary key,
  work_type text not null,
  status text not null,
  priority integer not null default 0,
  locked_by text,
  locked_at timestamptz,
  lock_expires_at timestamptz,
  retry_count integer not null default 0,
  next_retry_at timestamptz,
  payload jsonb,
  created_at timestamptz not null,
  updated_at timestamptz not null
);

Index:

create index idx_work_item_ready
on work_item (priority desc, next_retry_at, created_at)
where status = 'READY';

23. Java/JAX-RS Backend Implications

Command service should handle concurrency explicitly.

Example:

public Quote submitQuote(SubmitQuoteCommand command) {
    Quote quote = quoteRepository.findForUpdate(command.quoteId());
    quote.assertVersion(command.expectedVersion());
    quote.submit(command.actor(), command.reason());
    quoteRepository.save(quote);
    outbox.append(QuoteSubmittedEvent.from(quote));
    audit.append(...);
    return quote;
}

For optimistic update:

int updated = quoteRepository.updateStatusIfVersion(
    quoteId,
    expectedVersion,
    SUBMITTED
);

if (updated == 0) {
    throw new ConflictException("QUOTE_VERSION_CONFLICT");
}

Return stable conflict error.

24. API Error Model for Concurrency

Conflict response:

{
  "errorCode": "VERSION_CONFLICT",
  "message": "The quote was modified by another process. Reload and retry.",
  "details": {
    "expectedVersion": 4,
    "currentVersion": 5
  },
  "correlationId": "corr-123"
}

Duplicate idempotent request:

{
  "status": "COMPLETED",
  "resultReference": {
    "type": "ORDER",
    "id": "order-id"
  }
}

Same idempotency key with different payload:

{
  "errorCode": "IDEMPOTENCY_KEY_CONFLICT",
  "message": "Idempotency key was already used with a different request payload"
}

25. Event Consumer Concurrency

Consumer must handle:

• duplicate event,
• stale event,
• out-of-order event,
• concurrent processing by multiple instances,
• replay,
• poison message.

Use:

• inbox unique key,
• aggregate version,
• idempotent side effect,
• local transaction,
• retry/DLQ,
• projection version.

Pattern:

Begin transaction
  insert inbox(event_id, subscriber)
  load projection
  if event.version <= projection.version: mark skipped
  else apply update
  mark inbox processed
Commit

26. Cache Concurrency

Cache can worsen concurrency if stale.

Examples:

• permission cache stale after revoke,
• product status cache stale after termination,
• quote version cache stale during update,
• price rule cache stale after publish.

Do not perform critical state transitions based only on cache.

For commands:

load authoritative state from DB/source service

Cache is for read optimization, not invariant enforcement unless designed with strong guarantees.

27. Testing Concurrency

Test:

• duplicate requests,
• same idempotency key retry,
• same idempotency key different payload,
• concurrent accept quote,
• concurrent convert quote,
• cancel vs fulfillment complete,
• approval vs quote revision,
• concurrent allowance consumption,
• worker queue multiple workers,
• stale event after newer event,
• optimistic lock conflict.

Use integration tests with real database constraints where possible.

Unit tests alone rarely catch race conditions.

28. Observability

Monitor:

• version conflict rate,
• idempotency conflict rate,
• duplicate event skip count,
• stale event count,
• deadlock count,
• lock wait time,
• task lease expiry,
• worker retry count,
• unique constraint violation by command,
• order/product/charge duplicate attempts,
• state regression attempts.

Example query:

-- Work items stuck with expired lease
select id, work_type, locked_by, lock_expires_at
from work_item
where status = 'PROCESSING'
  and lock_expires_at < now();

-- Reused idempotency key conflict candidates
select scope, idempotency_key, count(*)
from idempotency_record
group by scope, idempotency_key
having count(*) > 1;

Second query should return none if unique constraint works.

29. Failure Modes

30. PR Review Checklist

When reviewing concurrent data changes, ask:

• What aggregate can be updated concurrently?
• Is expected version required?
• Is optimistic or pessimistic locking used?
• Is state transition atomic?
• Is idempotency required?
• What is the idempotency key?
• Is uniqueness enforced in DB?
• What happens on duplicate command?
• What happens on stale event?
• Are events versioned?
• Are worker tasks claimed safely?
• Could deadlock happen due to lock order?
• Are external calls outside DB transaction?
• Are cache reads used only for safe reads?
• Are concurrency conflicts observable?
• Are tests covering duplicate/race scenarios?

31. Internal Verification Checklist

Verify these in the internal CSG/team context:

• Standard optimistic locking/version field usage.
• Standard conflict error response.
• Idempotency key support for create/convert/billing commands.
• Unique constraints for order-from-quote and charge creation.
• Queue worker locking pattern.
• Outbox/inbox concurrency handling.
• Transaction isolation default.
• Use of select for update and lock timeout standards.
• Deadlock monitoring.
• Aggregate version in events/projections.
• Cache usage in command validation.
• Incidents involving duplicate order, duplicate charge, lost update, stale event, deadlock, or double resource allocation.

32. Summary

Concurrency correctness is a data modelling concern.

A strong model must define:

• aggregate version,
• optimistic locking,
• pessimistic locking,
• atomic state transition,
• idempotency record,
• unique business constraints,
• worker lease/claim,
• event version handling,
• stale event prevention,
• resource/allowance locking,
• deadlock avoidance,
• cache safety,
• conflict error model,
• observability.

The key principle:

In production, the same business action will be retried, duplicated, raced, delayed, and replayed. Your data model must make the correct outcome the easiest and safest outcome under concurrency.