PostgreSQL advisory locks for distributed mutex

<p>I have multiple API instances and need to ensure only one runs a specific operation at a time (e.g., leaderboard rebuild, scheduled job). I need a distributed mutex without adding a separate locking service.</p>

<p>PostgreSQL advisory locks as distributed mutex:</p> <div class="highlight"><pre><span></span><code><span class="kn">from</span><span class="w"> </span><span class="nn">sqlalchemy</span><span class="w"> </span><span class="kn">import</span> <span class="n">text</span> <span class="k">async</span> <span class="k">def</span><span class="w"> </span><span class="nf">with_advisory_lock</span><span class="p">(</span> <span class="n">session</span><span class="p">:</span> <span class="n">AsyncSession</span><span class="p">,</span> <span class="n">lock_id</span><span class="p">:</span> <span class="nb">int</span><span class="p">,</span> <span class="n">timeout_ms</span><span class="p">:</span> <span class="nb">int</span> <span class="o">=</span> <span class="mi">5000</span><span class="p">,</span> <span class="p">)</span> <span class="o">-&gt;</span> <span class="nb">bool</span><span class="p">:</span> <span class="w"> </span><span class="sd">"""Try to acquire advisory lock. Returns True if acquired."""</span> <span class="c1"># Session-level lock (held until session closes or explicit unlock):</span> <span class="n">result</span> <span class="o">=</span> <span class="k">await</span> <span class="n">session</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span> <span class="n">text</span><span class="p">(</span><span class="s1">'SELECT pg_try_advisory_lock(:lock_id)'</span><span class="p">),</span> <span class="p">{</span><span class="s1">'lock_id'</span><span class="p">:</span> <span class="n">lock_id</span><span class="p">},</span> <span class="p">)</span> <span class="k">return</span> <span class="n">result</span><span class="o">.</span><span class="n">scalar_one</span><span class="p">()</span> <span class="k">async</span> <span class="k">def</span><span class="w"> </span><span class="nf">release_advisory_lock</span><span class="p">(</span><span class="n">session</span><span class="p">:</span> <span class="n">AsyncSession</span><span class="p">,</span> <span class="n">lock_id</span><span class="p">:</span> <span class="nb">int</span><span class="p">)</span> <span class="o">-&gt;</span> <span class="kc">None</span><span class="p">:</span> <span class="k">await</span> <span class="n">session</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span> <span class="n">text</span><span class="p">(</span><span class="s1">'SELECT pg_advisory_unlock(:lock_id)'</span><span class="p">),</span> <span class="p">{</span><span class="s1">'lock_id'</span><span class="p">:</span> <span class="n">lock_id</span><span class="p">},</span> <span class="p">)</span> <span class="c1"># Transactional advisory lock (auto-released at transaction end):</span> <span class="k">async</span> <span class="k">def</span><span class="w"> </span><span class="nf">with_transactional_lock</span><span class="p">(</span><span class="n">session</span><span class="p">:</span> <span class="n">AsyncSession</span><span class="p">,</span> <span class="n">lock_id</span><span class="p">:</span> <span class="nb">int</span><span class="p">)</span> <span class="o">-&gt;</span> <span class="kc">None</span><span class="p">:</span> <span class="w"> </span><span class="sd">"""Blocks until lock acquired. Released automatically at transaction end."""</span> <span class="k">await</span> <span class="n">session</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span> <span class="n">text</span><span class="p">(</span><span class="s1">'SELECT pg_advisory_xact_lock(:lock_id)'</span><span class="p">),</span> <span class="p">{</span><span class="s1">'lock_id'</span><span class="p">:</span> <span class="n">lock_id</span><span class="p">},</span> <span class="p">)</span> <span class="c1"># Usage:</span> <span class="n">LEADERBOARD_LOCK_ID</span> <span class="o">=</span> <span class="nb">hash</span><span class="p">(</span><span class="s1">'leaderboard_rebuild'</span><span class="p">)</span> <span class="o">&amp;</span> <span class="mh">0x7FFFFFFF</span> <span class="k">async</span> <span class="k">def</span><span class="w"> </span><span class="nf">rebuild_leaderboard_safe</span><span class="p">():</span> <span class="k">async</span> <span class="k">with</span> <span class="n">async_session</span><span class="p">()</span> <span class="k">as</span> <span class="n">session</span><span class="p">:</span> <span class="n">acquired</span> <span class="o">=</span> <span class="k">await</span> <span class="n">with_advisory_lock</span><span class="p">(</span><span class="n">session</span><span class="p">,</span> <span class="n">LEADERBOARD_LOCK_ID</span><span class="p">)</span> <span class="k">if</span> <span class="ow">not</span> <span class="n">acquired</span><span class="p">:</span> <span class="n">log</span><span class="o">.</span><span class="n">info</span><span class="p">(</span><span class="s1">'Leaderboard rebuild already running, skipping'</span><span class="p">)</span> <span class="k">return</span> <span class="k">try</span><span class="p">:</span> <span class="k">await</span> <span class="n">do_rebuild</span><span class="p">(</span><span class="n">session</span><span class="p">)</span> <span class="k">finally</span><span class="p">:</span> <span class="k">await</span> <span class="n">release_advisory_lock</span><span class="p">(</span><span class="n">session</span><span class="p">,</span> <span class="n">LEADERBOARD_LOCK_ID</span><span class="p">)</span> </code></pre></div> <p>Key points: - Advisory locks are cooperative -- all code must use them for the guarantee - pg_try_advisory_lock returns False immediately if lock is held (non-blocking) - pg_advisory_lock blocks until acquired (blocking version) - Transactional locks (pg_advisory_xact_lock) release automatically at COMMIT/ROLLBACK - Lock IDs are integers -- use consistent hashing to generate from string keys</p>