Concurrency Control for Self-Hosted Supabase: Handle Race Conditions

When multiple users update the same data simultaneously in your self-hosted Supabase application, you're facing one of the oldest problems in database engineering: concurrency control. Without proper handling, two users editing the same record can overwrite each other's changes, corrupt data, or create inconsistent application state. This guide covers practical patterns for handling concurrent writes in your self-hosted Supabase deployment.

The Concurrent Write Problem

Imagine a collaborative document editor, an inventory management system, or an e-commerce checkout. Two users read the same data at nearly the same time, make their changes locally, and both attempt to save. Without concurrency control, the last write wins—and the first user's changes are silently lost.

This "lost update" problem manifests in several ways:

• Overwritten changes: User A's edits disappear when User B saves
• Inconsistent totals: Inventory counts go negative or become inaccurate
• Double bookings: Two customers reserve the same resource
• Corrupted state: Related records become out of sync

Self-hosted Supabase gives you full control over how PostgreSQL handles these scenarios, but you need to implement the patterns yourself.

Two Approaches to Concurrency

There are two fundamental strategies for handling concurrent modifications:

Pessimistic Locking

Lock the row when someone starts editing, preventing anyone else from modifying it until the lock is released. This guarantees no conflicts but can cause problems:

• Users may hold locks too long
• Deadlocks can occur if users lock multiple rows
• Poor user experience when waiting for locks
• Complex lock management and timeout handling

Optimistic Locking

Assume conflicts are rare. Let everyone read and edit freely, but check at save time whether the data changed since it was read. If it did, reject the update and let the application handle the conflict.

For most web applications, optimistic locking is the better choice. It's simpler to implement, scales better, and provides a better user experience when conflicts are infrequent—which they usually are.

Implementing Optimistic Locking in Supabase

The core pattern requires a version column that increments with each update. Here's how to set it up:

Step 1: Add a Version Column

-- Add version column to existing table
ALTER TABLE documents ADD COLUMN version integer NOT NULL DEFAULT 1;

-- Or include it in new table creation
CREATE TABLE documents (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  title text NOT NULL,
  content text,
  updated_at timestamptz DEFAULT now(),
  version integer NOT NULL DEFAULT 1
);

Step 2: Create an Auto-Increment Trigger

CREATE OR REPLACE FUNCTION increment_version()
RETURNS TRIGGER AS $$
BEGIN
  NEW.version = OLD.version + 1;
  NEW.updated_at = now();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER documents_version_trigger
  BEFORE UPDATE ON documents
  FOR EACH ROW
  EXECUTE FUNCTION increment_version();

Step 3: Conditional Updates in Your Application

When saving changes, include the version in your WHERE clause:

const { data, error } = await supabase
  .from('documents')
  .update({ 
    title: 'Updated Title',
    content: 'New content here'
  })
  .eq('id', documentId)
  .eq('version', currentVersion) // Only update if version matches
  .select()
  .single();

if (!data && !error) {
  // No rows updated = version mismatch = concurrent modification
  throw new Error('Document was modified by another user');
}

The key insight: if version changed since you read the document, the WHERE clause won't match any rows, and the update affects zero rows. Your application detects this and can respond appropriately.

Handling Conflicts Gracefully

When a conflict occurs, you have several options depending on your application's needs:

Option 1: Notify and Refresh

The simplest approach—tell the user and show them the current data:

async function saveDocument(doc: Document) {
  const { data, error } = await supabase
    .from('documents')
    .update({ title: doc.title, content: doc.content })
    .eq('id', doc.id)
    .eq('version', doc.version)
    .select()
    .single();

  if (!data) {
    // Fetch the current version
    const { data: current } = await supabase
      .from('documents')
      .select()
      .eq('id', doc.id)
      .single();
    
    return {
      success: false,
      message: 'This document was modified. Review the changes below.',
      currentData: current,
      yourChanges: doc
    };
  }

  return { success: true, data };
}

Option 2: Automatic Retry

For non-interactive operations, retry with the latest version:

async function updateWithRetry(
  id: string, 
  updateFn: (current: Document) => Partial<Document>,
  maxRetries = 3
) {
  for (let attempt = 0; attempt < maxRetries; attempt++) {
    // Fetch current state
    const { data: current } = await supabase
      .from('documents')
      .select()
      .eq('id', id)
      .single();

    // Apply your changes
    const changes = updateFn(current);

    // Try to save
    const { data, error } = await supabase
      .from('documents')
      .update(changes)
      .eq('id', id)
      .eq('version', current.version)
      .select()
      .single();

    if (data) return { success: true, data };
    
    // Wait before retry (exponential backoff)
    await new Promise(r => setTimeout(r, 100 * Math.pow(2, attempt)));
  }

  throw new Error('Failed to update after maximum retries');
}

Option 3: Merge Changes

For text-based content, you might implement merge logic:

// Simplified three-way merge example
function mergeChanges(
  base: string,      // Original version both users started from
  theirs: string,    // What's currently in the database
  yours: string      // Your local changes
): { merged: string; hasConflicts: boolean } {
  // Use a library like diff-match-patch for real implementations
  // This is just conceptual
  if (base === theirs) {
    // No one else changed it, your version wins
    return { merged: yours, hasConflicts: false };
  }
  
  // Both changed - need actual merge logic here
  // Consider libraries like diff3 or operational transforms
  return { merged: theirs, hasConflicts: true };
}

Row-Level Locking with PostgreSQL

For cases where optimistic locking isn't sufficient—like financial transactions or inventory management—PostgreSQL provides row-level locks. In self-hosted Supabase, you can use these directly via database functions:

-- Lock row for update (blocks other SELECT FOR UPDATE queries)
CREATE OR REPLACE FUNCTION reserve_inventory(
  p_product_id uuid,
  p_quantity integer
)
RETURNS boolean AS $$
DECLARE
  v_available integer;
BEGIN
  -- Lock the row to prevent concurrent modifications
  SELECT quantity INTO v_available
  FROM inventory
  WHERE product_id = p_product_id
  FOR UPDATE;

  IF v_available >= p_quantity THEN
    UPDATE inventory 
    SET quantity = quantity - p_quantity
    WHERE product_id = p_product_id;
    RETURN true;
  END IF;

  RETURN false;
END;
$$ LANGUAGE plpgsql;

Call it from your application:

const { data, error } = await supabase
  .rpc('reserve_inventory', {
    p_product_id: productId,
    p_quantity: 5
  });

if (data === true) {
  // Reservation successful
} else {
  // Insufficient inventory
}

The FOR UPDATE clause ensures only one transaction can modify the row at a time, preventing overselling.

Real-Time Conflict Prevention

Supabase Realtime can help prevent conflicts before they happen by showing users when others are editing:

// Subscribe to changes on a specific document
const channel = supabase
  .channel('document-changes')
  .on(
    'postgres_changes',
    {
      event: 'UPDATE',
      schema: 'public',
      table: 'documents',
      filter: `id=eq.${documentId}`
    },
    (payload) => {
      if (payload.new.version !== localVersion) {
        // Someone else updated the document
        showNotification('This document was just updated by another user');
        refreshDocument();
      }
    }
  )
  .subscribe();

For collaborative editing, combine this with Presence to show who's currently viewing or editing:

const presenceChannel = supabase.channel('document:' + documentId);

// Track current user
presenceChannel.subscribe(async (status) => {
  if (status === 'SUBSCRIBED') {
    await presenceChannel.track({
      user_id: currentUser.id,
      user_name: currentUser.name,
      editing: true
    });
  }
});

// Listen for others
presenceChannel.on('presence', { event: 'sync' }, () => {
  const state = presenceChannel.presenceState();
  updateActiveEditorsUI(Object.values(state).flat());
});

Advisory Locks for Complex Workflows

For operations spanning multiple tables or requiring coordination, PostgreSQL advisory locks provide application-defined locking:

CREATE OR REPLACE FUNCTION process_order(p_order_id uuid)
RETURNS boolean AS $$
DECLARE
  v_lock_key bigint;
  v_acquired boolean;
BEGIN
  -- Generate a consistent lock key from order ID
  v_lock_key := ('x' || substr(p_order_id::text, 1, 16))::bit(64)::bigint;
  
  -- Try to acquire advisory lock (returns immediately)
  SELECT pg_try_advisory_lock(v_lock_key) INTO v_acquired;
  
  IF NOT v_acquired THEN
    RAISE EXCEPTION 'Order is being processed by another request';
  END IF;
  
  BEGIN
    -- Your multi-step operation here
    UPDATE orders SET status = 'processing' WHERE id = p_order_id;
    -- ... more operations ...
    
    -- Release lock on success
    PERFORM pg_advisory_unlock(v_lock_key);
    RETURN true;
  EXCEPTION WHEN OTHERS THEN
    -- Release lock on error
    PERFORM pg_advisory_unlock(v_lock_key);
    RAISE;
  END;
END;
$$ LANGUAGE plpgsql;

Performance Considerations

Optimistic locking scales well because there's no actual locking until write time. However, consider these factors:

Index your version column if you're querying by it frequently:

CREATE INDEX idx_documents_id_version ON documents(id, version);

Batch operations should use transactions to ensure consistency:

const { data, error } = await supabase.rpc('batch_update_with_versions', {
  updates: [
    { id: 'uuid1', version: 5, changes: {...} },
    { id: 'uuid2', version: 3, changes: {...} }
  ]
});

Monitor conflict rates in production. High conflict rates suggest either:

• Your application needs better real-time coordination
• You might need pessimistic locking for specific operations
• Data model changes could reduce contention

Testing Concurrent Writes

Before deploying, test your concurrency handling with tools like pgTAP:

-- Test optimistic locking prevents lost updates
BEGIN;

SELECT plan(1);

-- Simulate concurrent read
SELECT * FROM documents WHERE id = 'test-id'; -- version = 1

-- Simulate another user's update
UPDATE documents SET content = 'Other user' WHERE id = 'test-id';

-- Our update should fail (no rows affected)
UPDATE documents SET content = 'Our update' 
WHERE id = 'test-id' AND version = 1;

SELECT is(
  (SELECT content FROM documents WHERE id = 'test-id'),
  'Other user',
  'Optimistic lock prevented lost update'
);

SELECT * FROM finish();
ROLLBACK;

How Supascale Helps

Managing concurrency in production requires monitoring query performance, tracking deadlocks, and ensuring your database handles the load. Supascale simplifies self-hosted Supabase operations with:

• One-click deployment to get your self-hosted instance running quickly with production-ready Docker configurations
• Automated backups to recover from any concurrency bugs that slip through to production via S3-compatible storage
• Performance monitoring to identify slow queries and lock contention

At just $39.99 one-time, you get unlimited projects without worrying about per-seat or per-project costs as your application scales. Check out our pricing for details.

Conclusion

Concurrency control is essential for any multi-user application. For self-hosted Supabase:

1. Start with optimistic locking—it handles most cases well
2. Use version columns and conditional updates
3. Handle conflicts gracefully with appropriate UX
4. Add real-time notifications to reduce conflicts proactively
5. Use PostgreSQL locks only when truly necessary

The patterns covered here work with any PostgreSQL database, giving you portable solutions that aren't locked to any specific platform—one of the key benefits of self-hosting Supabase.

Further Reading

• Row Level Security for Self-Hosted Supabase
• PostgreSQL Performance Tuning Guide
• Building Real-Time Collaborative Apps
• Database Testing with pgTAP