PostgreSQL transaction isolation levels for concurrent operations

<p>I have concurrent reads and writes and am seeing unexpected behavior: dirty reads, non-repeatable reads, or phantom rows. I need to understand PostgreSQL isolation levels and when to use each.</p>

<p>PostgreSQL isolation levels and when to use them:</p> <div class="highlight"><pre><span></span><code><span class="c1">-- Default: READ COMMITTED (most operations)</span> <span class="c1">-- Each statement sees data committed BEFORE that statement started</span> <span class="k">BEGIN</span><span class="p">;</span> <span class="k">SELECT</span><span class="w"> </span><span class="n">trust_score</span><span class="w"> </span><span class="k">FROM</span><span class="w"> </span><span class="n">traces</span><span class="w"> </span><span class="k">WHERE</span><span class="w"> </span><span class="n">id</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="err">$</span><span class="mi">1</span><span class="p">;</span><span class="w"> </span><span class="c1">-- Reads committed data</span> <span class="k">UPDATE</span><span class="w"> </span><span class="n">traces</span><span class="w"> </span><span class="k">SET</span><span class="w"> </span><span class="n">trust_score</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="err">$</span><span class="mi">2</span><span class="w"> </span><span class="k">WHERE</span><span class="w"> </span><span class="n">id</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="err">$</span><span class="mi">1</span><span class="p">;</span> <span class="k">COMMIT</span><span class="p">;</span> <span class="c1">-- REPEATABLE READ (for reports and analytics)</span> <span class="c1">-- All reads in transaction see same snapshot from transaction start</span> <span class="k">BEGIN</span><span class="w"> </span><span class="k">ISOLATION</span><span class="w"> </span><span class="k">LEVEL</span><span class="w"> </span><span class="k">REPEATABLE</span><span class="w"> </span><span class="k">READ</span><span class="p">;</span> <span class="k">SELECT</span><span class="w"> </span><span class="k">count</span><span class="p">(</span><span class="o">*</span><span class="p">)</span><span class="w"> </span><span class="k">FROM</span><span class="w"> </span><span class="n">traces</span><span class="w"> </span><span class="k">WHERE</span><span class="w"> </span><span class="n">status</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="s1">'validated'</span><span class="p">;</span><span class="w"> </span><span class="c1">-- Snapshot taken here</span> <span class="c1">-- ... other reads see same snapshot</span> <span class="k">COMMIT</span><span class="p">;</span> <span class="c1">-- SERIALIZABLE (for financial transactions, audit-critical operations)</span> <span class="c1">-- Transactions execute as if they ran serially, one at a time</span> <span class="k">BEGIN</span><span class="w"> </span><span class="k">ISOLATION</span><span class="w"> </span><span class="k">LEVEL</span><span class="w"> </span><span class="k">SERIALIZABLE</span><span class="p">;</span> <span class="k">SELECT</span><span class="w"> </span><span class="k">sum</span><span class="p">(</span><span class="n">amount</span><span class="p">)</span><span class="w"> </span><span class="k">FROM</span><span class="w"> </span><span class="n">accounts</span><span class="w"> </span><span class="k">WHERE</span><span class="w"> </span><span class="n">user_id</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="err">$</span><span class="mi">1</span><span class="p">;</span> <span class="k">UPDATE</span><span class="w"> </span><span class="n">accounts</span><span class="w"> </span><span class="k">SET</span><span class="w"> </span><span class="n">amount</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="n">amount</span><span class="w"> </span><span class="o">-</span><span class="w"> </span><span class="err">$</span><span class="mi">2</span><span class="w"> </span><span class="k">WHERE</span><span class="w"> </span><span class="n">user_id</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="err">$</span><span class="mi">1</span><span class="p">;</span> <span class="k">COMMIT</span><span class="p">;</span><span class="w"> </span><span class="c1">-- May fail with serialization failure -- retry required</span> </code></pre></div> <p>In SQLAlchemy:</p> <div class="highlight"><pre><span></span><code><span class="c1"># Set isolation for specific operations:</span> <span class="k">async</span> <span class="k">with</span> <span class="n">session</span><span class="o">.</span><span class="n">begin</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">'SET TRANSACTION ISOLATION LEVEL REPEATABLE READ'</span><span class="p">))</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">complex_analytics_query</span><span class="p">)</span> <span class="c1"># Per-engine isolation level:</span> <span class="n">engine</span> <span class="o">=</span> <span class="n">create_async_engine</span><span class="p">(</span><span class="n">url</span><span class="p">,</span> <span class="n">isolation_level</span><span class="o">=</span><span class="s1">'REPEATABLE READ'</span><span class="p">)</span> </code></pre></div> <p>Key points: - READ COMMITTED is correct for 99% of operations -- no phantom reads in typical OLTP - REPEATABLE READ for reports where consistent snapshot matters - SERIALIZABLE has overhead and retry requirement -- avoid unless truly needed - PostgreSQL does NOT have dirty reads even at READ UNCOMMITTED - Retry serialization failures with exponential backoff</p>