Building a Real-time Distributed Locking System

Introduction

In collaborative SaaS applications, preventing data conflicts is a major challenge. When multiple users or background processes attempt to modify the same record simultaneously, race conditions can lead to data loss or corruption. I recently designed and implemented an Elastic Table Locking System to solve this problem elegantly at the database layer.

The Problem: Concurrency in Collaborative Apps

I needed a system that could:

1. Prevent Overwrites: Stop User A from saving changes if User B is currently editing the same record.
2. Handle System Locks: Allow background jobs (like webhooks) to lock records during processing.
3. Provide Real-time Feedback: Instantly notify the UI when a record is locked or unlocked.
4. Enforce Security: Ensure users can only lock records within their own organization.

The Architecture

I chose a “Database-First” approach, leveraging the power of PostgreSQL to handle the heavy lifting of state management and notification.

graph TB
    subgraph "Client Layer"
        UI[Web UI]
        Subscription[GraphQL Subscription]
    end
    
    subgraph "API Layer"
        GQL[GraphQL Server]
        Mutations[Lock Mutations]
        Queries[Lock Queries]
    end
    
    subgraph "Database Layer"
        Locks[(Locks Table)]
        RLS[Row Level Security]
        Triggers[DB Triggers]
        Notify[LISTEN/NOTIFY]
    end
    
    subgraph "Background Services"
        Cleanup[Cleanup Worker]
    end
    
    UI -->|acquireLock| Mutations
    UI -->|releaseLock| Mutations
    UI -->|lockStatus| Queries
    UI -->|Subscribe| Subscription
    
    Mutations --> Locks
    Queries --> Locks
    Locks -->|Enforce Multi-tenancy| RLS
    Locks -->|Auto-expire Check| Triggers
    Triggers -->|Emit Events| Notify
    Notify -->|Real-time Updates| Subscription
    Subscription --> UI
    
    Cleanup -->|Remove Stale Locks| Locks

1. The Database Layer (PostgreSQL)

The core is a dedicated locks table with strict constraints. I used Row Level Security (RLS) to enforce multi-tenancy at the lowest level, ensuring that a user from Organization X can never touch a lock belonging to Organization Y.

Key features implemented in SQL:

• Automatic Expiration: Locks have a expires_at timestamp. A background cleanup process removes stale locks, preventing “zombie” locks from blocking access indefinitely.
• Smart Constraints: Unique constraints ensure only one active lock exists per record entity.

2. The API Layer (GraphQL)

I expose the locking logic via a GraphQL API. This provides a strongly-typed interface for the frontend.

• Mutations: acquireLock, releaseLock, and extendLock operations.
• Queries: lockStatus to check if a record is editable.
• Subscriptions: This is the “magic” part. Using PostgreSQL’s LISTEN/NOTIFY channels, the GraphQL server pushes real-time updates to the client.

subscription OnLockChange($recordId: ID!) {
  lockChanged(recordId: $recordId) {
    status
    lockedBy {
      username
    }
    expiresAt
  }
}

Solving the “Zombie Lock” Problem

One common issue with locking systems is that if a user’s browser crashes or they lose internet connection, the lock might remain, blocking everyone else.

To solve this, I implemented a Heartbeat Mechanism:

1. The frontend acquires a lock with a short TTL (e.g., 30 seconds).
2. While the user is active, the frontend periodically calls extendLock to reset the timer.
3. If the user disconnects, the heartbeat stops, and the lock automatically expires after 30 seconds.

Conclusion

By pushing the concurrency logic down to the database and leveraging GraphQL subscriptions for real-time state syncing, I built a system that is both robust and highly responsive. It eliminates data conflicts and provides a seamless collaborative experience for users.