René's URL Explorer Experiment

{"@context":"https://schema.org","@type":"DiscussionForumPosting","headline":"Bug: multiple workers reserving the same key in Job.reserve() in 2.1","articleBody":"## Bug Report\n\n### Description\nWhen using populate(reserve_jobs=True) with multiple workers (e.g., SLURM array jobs), multiple workers can successfully \"reserve\" the same key and call make() simultaneously. \nI noticed this when sumbitting multiple SLURM jobs that populate the same table: their output logs confirmed that 6/7 jobs were executing make() on the same key. This job reservation system has always been a nice feature of datajoint for distributed computing - preventing redundant computations and collisions. \n\n### Current workaround\nCurrently I think this is a rare occurance, probably made slightly more common by the fact that I am accessing my server across a network (not hosted locally) and submitting sbatch arrays. My workaround right now has been adding a random sleep (0-30s) before `populate()` in the SLURM script to stagger worker start times. With that in place I have not noticed the duplicate make / race. \n\nI'm not very familiar with how datajoint manages job reservations, but below is what claude suggested might be the issue. Adding it to this post in case it is useful.\n\n\u003e \n\u003e `Job.reserve()` in DataJoint 2.1 uses a non-atomic SELECT-then-UPDATE pattern that allows multiple workers to reserve the same job simultaneously. This is a regression from the 0.13.x approach which used an atomic `INSERT ... ON DUPLICATE KEY` pattern that was inherently atomic.\n\u003e \n\u003e ### Root Cause\n\u003e \n\u003e `Job.reserve()` (jobs.py:430-473) performs a **check-then-act** without atomicity:\n\u003e \n\u003e ```python\n\u003e def reserve(self, key: dict) -\u003e bool:\n\u003e     # Step 1: SELECT — check if job is pending\n\u003e     job = (self \u0026 key \u0026 \"status='pending'\" \u0026 \"scheduled_time \u003c= CURRENT_TIMESTAMP(3)\").to_dicts()\n\u003e     if not job:\n\u003e         return False\n\u003e \n\u003e     # Step 2: UPDATE — mark as reserved\n\u003e     pk = self._get_pk(key)\n\u003e     update_row = {**pk, \"status\": \"reserved\", ...}\n\u003e     try:\n\u003e         self.update1(update_row)  # UPDATE ... SET status='reserved' WHERE \u003cpk\u003e\n\u003e         return True\n\u003e     except Exception:\n\u003e         return False\n\u003e ```\n\u003e \n\u003e The UPDATE's WHERE clause matches only on primary key, **not** on `status='pending'`. So if two workers both read the row as `'pending'` before either updates, both UPDATEs succeed — the second simply overwrites the first worker's reservation.\n\u003e \n\u003e ### Comparison with 0.13.x\n\u003e \n\u003e The old `JobTable.reserve()` (0.13.x) used an atomic INSERT pattern:\n\u003e \n\u003e ```python\n\u003e def reserve(self, table_name, key):\n\u003e     job = dict(key, table_name=table_name, status='reserved',\n\u003e                host=platform.node(), pid=os.getpid(), ...)\n\u003e     try:\n\u003e         self.insert1(job)  # INSERT — fails with DuplicateError if row exists\n\u003e     except DuplicateError:\n\u003e         return False        # Another worker already has this key\n\u003e     return True\n\u003e ```\n\u003e \n\u003e This is inherently atomic: the first INSERT wins, all others get `DuplicateError`. No window exists between check and action.\n\u003e \n\u003e ### Suggested Fix\n\u003e \n\u003e Option A — Add a WHERE clause to the UPDATE:\n\u003e \n\u003e ```sql\n\u003e UPDATE jobs SET status='reserved', ...\n\u003e WHERE table_name=... AND key_hash=... AND status='pending'\n\u003e ```\n\u003e \n\u003e Then check `affected_rows == 1` to determine success. This is a single atomic operation.\n\u003e \n\u003e Option B — Use `SELECT ... FOR UPDATE` before the check:\n\u003e \n\u003e ```sql\n\u003e SELECT * FROM jobs WHERE ... AND status='pending' FOR UPDATE\n\u003e ```\n\u003e \n\u003e This acquires a row-level lock, preventing concurrent readers from seeing the row as pending.\n\u003e \n\u003e Option C — Restore the INSERT-based approach from 0.13.x, which was atomic by design.\n\u003e \n\u003e ### Environment\n\u003e \n\u003e - datajoint 2.1.0\n\u003e - Python 3.12\n\u003e - MySQL 8.0\n\u003e - SLURM cluster, 7 concurrent array tasks calling `populate(reserve_jobs=True)`\n\u003e \n\u003e ","author":{"url":"https://github.com/noahpettit","@type":"Person","name":"noahpettit"},"datePublished":"2026-02-14T18:57:50.000Z","interactionStatistic":{"@type":"InteractionCounter","interactionType":"https://schema.org/CommentAction","userInteractionCount":3},"url":"https://github.com/1398/datajoint-python/issues/1398"}