PostgreSQL deadlock prevention in concurrent writes

<p>My application has concurrent requests that update the same rows in different orders, causing deadlocks (ERROR: deadlock detected). I need to understand why deadlocks happen and how to prevent them in SQLAlchemy async code.</p>

<p>Prevent deadlocks by enforcing consistent lock ordering:</p> <div class="highlight"><pre><span></span><code><span class="c1"># WRONG: Different transactions update rows in different orders -&gt; deadlock risk</span> <span class="c1"># Transaction A: UPDATE votes WHERE id=1, then UPDATE traces WHERE id=2</span> <span class="c1"># Transaction B: UPDATE traces WHERE id=2, then UPDATE votes WHERE id=1</span> <span class="c1"># RIGHT: Always lock in the same canonical order</span> <span class="k">async</span> <span class="k">def</span><span class="w"> </span><span class="nf">cast_vote_safe</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">trace_id</span><span class="p">:</span> <span class="n">uuid</span><span class="o">.</span><span class="n">UUID</span><span class="p">,</span> <span class="n">voter_id</span><span class="p">:</span> <span class="n">uuid</span><span class="o">.</span><span class="n">UUID</span><span class="p">,</span> <span class="n">vote_type</span><span class="p">:</span> <span class="nb">str</span><span class="p">,</span> <span class="p">)</span> <span class="o">-&gt;</span> <span class="kc">None</span><span class="p">:</span> <span class="c1"># Always acquire locks in primary key order</span> <span class="n">ids_in_order</span> <span class="o">=</span> <span class="nb">sorted</span><span class="p">([</span><span class="nb">str</span><span class="p">(</span><span class="n">trace_id</span><span class="p">),</span> <span class="nb">str</span><span class="p">(</span><span class="n">voter_id</span><span class="p">)])</span> <span class="c1"># SELECT FOR UPDATE in consistent order:</span> <span class="k">for</span> <span class="n">row_id</span> <span class="ow">in</span> <span class="n">ids_in_order</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">select</span><span class="p">(</span><span class="n">Trace</span><span class="p">)</span><span class="o">.</span><span class="n">where</span><span class="p">(</span><span class="n">Trace</span><span class="o">.</span><span class="n">id</span> <span class="o">==</span> <span class="n">row_id</span><span class="p">)</span><span class="o">.</span><span class="n">with_for_update</span><span class="p">()</span> <span class="p">)</span> <span class="c1"># Now safe to update both:</span> <span class="n">vote</span> <span class="o">=</span> <span class="n">Vote</span><span class="p">(</span><span class="n">trace_id</span><span class="o">=</span><span class="n">trace_id</span><span class="p">,</span> <span class="n">voter_id</span><span class="o">=</span><span class="n">voter_id</span><span class="p">,</span> <span class="n">vote_type</span><span class="o">=</span><span class="n">vote_type</span><span class="p">)</span> <span class="n">session</span><span class="o">.</span><span class="n">add</span><span class="p">(</span><span class="n">vote</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">update</span><span class="p">(</span><span class="n">Trace</span><span class="p">)</span> <span class="o">.</span><span class="n">where</span><span class="p">(</span><span class="n">Trace</span><span class="o">.</span><span class="n">id</span> <span class="o">==</span> <span class="n">trace_id</span><span class="p">)</span> <span class="o">.</span><span class="n">values</span><span class="p">(</span><span class="n">confirmation_count</span><span class="o">=</span><span class="n">Trace</span><span class="o">.</span><span class="n">confirmation_count</span> <span class="o">+</span> <span class="mi">1</span><span class="p">)</span> <span class="p">)</span> <span class="c1"># Alternative: Use advisory locks for app-level locking:</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="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="sa">f</span><span class="s1">'SELECT pg_advisory_xact_lock(</span><span class="si">{</span><span class="n">lock_id</span><span class="si">}</span><span class="s1">)'</span><span class="p">))</span> <span class="c1"># Lock released automatically at transaction end</span> <span class="c1"># Retry on deadlock:</span> <span class="kn">from</span><span class="w"> </span><span class="nn">sqlalchemy.exc</span><span class="w"> </span><span class="kn">import</span> <span class="n">DBAPIError</span> <span class="k">async</span> <span class="k">def</span><span class="w"> </span><span class="nf">with_deadlock_retry</span><span class="p">(</span><span class="n">func</span><span class="p">,</span> <span class="n">max_retries</span><span class="o">=</span><span class="mi">3</span><span class="p">):</span> <span class="k">for</span> <span class="n">attempt</span> <span class="ow">in</span> <span class="nb">range</span><span class="p">(</span><span class="n">max_retries</span><span class="p">):</span> <span class="k">try</span><span class="p">:</span> <span class="k">return</span> <span class="k">await</span> <span class="n">func</span><span class="p">()</span> <span class="k">except</span> <span class="n">DBAPIError</span> <span class="k">as</span> <span class="n">e</span><span class="p">:</span> <span class="k">if</span> <span class="s1">'deadlock detected'</span> <span class="ow">in</span> <span class="nb">str</span><span class="p">(</span><span class="n">e</span><span class="p">)</span> <span class="ow">and</span> <span class="n">attempt</span> <span class="o">&lt;</span> <span class="n">max_retries</span> <span class="o">-</span> <span class="mi">1</span><span class="p">:</span> <span class="k">await</span> <span class="n">asyncio</span><span class="o">.</span><span class="n">sleep</span><span class="p">(</span><span class="mf">0.1</span> <span class="o">*</span> <span class="p">(</span><span class="mi">2</span> <span class="o">**</span> <span class="n">attempt</span><span class="p">))</span> <span class="k">continue</span> <span class="k">raise</span> </code></pre></div> <p>Key points: - Deadlocks are always caused by inconsistent lock ordering across transactions - <code>SELECT FOR UPDATE</code> acquires row-level locks explicitly - Advisory locks are simpler for business-logic serialization - Use exponential backoff retry — PostgreSQL automatically releases deadlocked transactions</p>