Best-Effort Locking Mechanism

Relevant source files

The following files were used as context for generating this wiki page:

• README.md
• src/meltano_state_backend_clickhouse/backend.py
• src/meltano_state_backend_clickhouse/init.py
• tests/test_backend.py
• pyproject.toml

The Best-Effort Locking Mechanism in the meltano-state-backend-clickhouse project provides advisory locking for pipeline state management. ClickHouse, being an OLAP (Online Analytical Processing) store, lacks traditional RDBMS primitives such as row-level updates, insert-conflict constraints (UPERT), or primary-key violations that other Meltano backends like Postgres or MSSQL rely on for locking. Consequently, this backend implements a specialized locking strategy to prevent concurrent processes from corrupting or overwriting pipeline state.

Sources: src/meltano_state_backend_clickhouse/backend.py:10-22, README.md:37-43

Architecture and Design Principles

The locking mechanism is designed around the limitations of ClickHouse's storage engine. It uses a "best-effort" approach characterized by an insert-then-verify pattern combined with stale-lock cleanup. Because ClickHouse does not enforce uniqueness, the mechanism allows multiple rows for the same state_id to exist in the lock table and uses a "settling" period to resolve ties between concurrent processes.

Sources: src/meltano_state_backend_clickhouse/backend.py:349-373, README.md:43

Lock Table Schema

The lock table uses the ReplacingMergeTree engine, which is keyed by state_id. It includes a Time-To-Live (TTL) as a secondary safety measure to ensure locks are eventually reaped by background merges if a process crashes.

Field	Type	Description
state_id	String	The unique identifier for the pipeline state being locked.
lock_id	String	A unique UUID generated by the process attempting to acquire the lock.
locked_at	DateTime	The timestamp when the lock row was inserted (defaults to now()).

Sources: src/meltano_state_backend_clickhouse/backend.py:255-267

The Settle-then-Decide Logic

To handle race conditions where two processes might check for an existing lock simultaneously, the backend implements a "settling" step. After a process inserts its candidate lock row, it waits for a predefined interval (LOCK_SETTLE_SECONDS) before querying the table to determine if it is the legitimate owner.

The process follows this flow:

1. Insert: The process inserts a unique lock_id for the target state_id.
2. Settle: The process sleeps for 1.0 second.
3. Verify: The process queries all candidate rows for the state_id without using the FINAL keyword.
4. Decide: If the process's lock_id is the lexicographically smallest (the min() value) among all candidates, it has acquired the lock.

This logic ensures that even if two processes insert rows nearly simultaneously, both will eventually see both rows after the settle period and agree on the same winner based on the lock_id tie-break.

Sources: src/meltano_state_backend_clickhouse/backend.py:349-373, src/meltano_state_backend_clickhouse/backend.py:61

Lock Acquisition Sequence

The following diagram illustrates the sequence of events during a successful lock acquisition:

Components and Configuration

The locking behavior is governed by several internal constants and user-defined settings.

Timing Constants

These constants define the boundaries of the best-effort system:

Constant	Value	Description
LOCK_TIMEOUT_SECONDS	30	Maximum duration to attempt acquisition before raising StateIDLockedError.
STALE_LOCK_SECONDS	300	Age at which a lock is considered stale and eligible for cleanup (5 minutes).
LOCK_SETTLE_SECONDS	1.0	The required wait time after insertion to ensure visibility to other nodes.

Sources: src/meltano_state_backend_clickhouse/backend.py:53-61

Key Functions

The logic is encapsulated within the ClickhouseStateStoreManager class:

• acquire_lock(state_id, retry_seconds): A context manager that implements the loop for inserting, settling, and verifying the lock. Sources: src/meltano_state_backend_clickhouse/backend.py:349
• _cleanup_stale_locks(): Executes a DELETE command against the lock table for rows older than STALE_LOCK_SECONDS. Sources: src/meltano_state_backend_clickhouse/backend.py:318
• _candidate_lock_ids(state_id): Retrieves all current rows for a state ID without collapsing them via FINAL, allowing the tie-break logic to see concurrent attempts. Sources: src/meltano_state_backend_clickhouse/backend.py:333

Reliability and Concurrency

The mechanism is tested to serialize concurrent processes effectively. A regression test in the project specifically addresses a "double-acquisition" bug that occurred before the LOCK_SETTLE_SECONDS delay was introduced. By using standard OS processes and a shared ClickHouse instance, the backend ensures that overlapping "critical sections" do not occur under high contention.

Sources: tests/test_backend.py:118-131, src/meltano_state_backend_clickhouse/backend.py:361-370

Summary

The Best-Effort Locking Mechanism provides a robust advisory locking system for ClickHouse by compensating for the database's lack of ACID-compliant row locking. By utilizing a combination of unique UUIDs, a mandatory settling period, and lexicographical tie-breaking, it ensures that Meltano state remains consistent even when multiple pipeline runners attempt to update the same state ID simultaneously.