Preventing Race Conditions with SERIALIZABLE Isolation in Supabase for High-Concurrency Updates

**Question:** I'm working with Supabase/PostgreSQL in a Next.js application and need to ensure that an is_processing flag is updated only once per user, even if multiple requests are sent in parallel. The goal is to prevent any duplicate operations by ensuring that once is_processing = true, additional requests won’t proceed until it’s reset. **Approach So Far** I initially created a PostgreSQL function using SERIALIZABLE isolation to handle concurrency, with the function attempting to set is_processing to true if it’s initially false. Here’s the function:

CREATE OR REPLACE FUNCTION serialized_update_processing_flag(user_id UUID) RETURNS BOOLEAN AS $$
DECLARE
    result BOOLEAN;
BEGIN
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

    UPDATE users
    SET is_processing = true
    WHERE id = user_id
      AND is_processing = false
    RETURNING true INTO result;

    RETURN result IS NOT NULL;
EXCEPTION
    WHEN serialization_failure THEN
        RETURN FALSE;
END;
$$ LANGUAGE plpgsql;

**Problem:** Running this function directly throws an error: "SET TRANSACTION ISOLATION LEVEL must be called before any query." **Alternative Implementation in Next.js** I moved the transaction management into my Next.js code, starting with SERIALIZABLE isolation at the application level. Here’s the revised approach:

const client = await pool.connect();

try {
  // Start a transaction with SERIALIZABLE isolation level
  await client.query('BEGIN');
  await client.query('SET TRANSACTION ISOLATION LEVEL SERIALIZABLE');

  // Call the RPC function
  const { rows } = await client.query(
    'SELECT serialized_update_recharge_flag($1) AS success',
    [userId]
  );

  await client.query('COMMIT');

  // Check the result
  console.log('ROWWW', rows);
  return rows.success;
} catch (error) {
  await client.query('ROLLBACK');
  console.error(Error in serialized transaction: ${error});
  throw error;
} finally {
  client.release();
}

Issue with High-Concurrency Requests This approach has been somewhat effective, but when I simulate around 30 parallel requests, some of them still manage to bypass the SERIALIZABLE isolation level, resulting in multiple operations setting is_processing = true concurrently. **Questions** How can I reliably enforce strict SERIALIZABLE isolation in this setup to prevent any concurrent updates from slipping through? Are there alternative methods in PostgreSQL or Supabase for handling high-concurrency, single-update situations with atomicity? I’d appreciate any suggestions or solutions to ensure consistent behavior and prevent duplicate operations, especially under high parallel loads. Thank you!