<p>PostgreSQL detects serialization violations using a heuristics. Reading data causes predicate locks (<code>SIReadLock</code>) to be taken, and it checks for <em>dangerous structures</em>, which necessarily occur in every serialization violation. That means that you can get false positive serialization errors, but never false negatives.</p>
<p>This is all described <a href="https://www.postgresql.org/docs/current/transaction-iso.html#XACT-SERIALIZABLE" rel="nofollow noreferrer">in the documentation</a> and in the <a href="https://arxiv.org/pdf/1208.4179.pdf" rel="nofollow noreferrer">scientific paper referenced there</a>, and we can hope that Amazon didn't hack up PostgreSQL too badly in that area.</p>
How are serializable isolation violations detected?
<p>Does anyone know how SQL databases detect serializable isolation violations (SIV's)? It seems like simply brute forcing every permutation of transaction executions to find a match for the concurrent execution results to verify serializability wouldn't scale.</p>
<p>According to this paper from a third party researcher: <a href="https://amazonredshiftresearchproject.org/white_papers/downloads/multi_version_concurrency_control_and_serialization_isolation_failure.pdf" rel="nofollow noreferrer">https://amazonredshiftresearchproject.org/white_papers/downloads/multi_version_concurrency_control_and_serialization_isolation_failure.pdf</a></p>
<p>SIV's occur when two transactions are occurring at the same time and the more recent one commits some deleted rows that the less recent transaction later tries to delete as well. This is a situation that MVCC is unable to deal with and thus has to abort with SIV.</p>
<p>This makes sense for detecting SIV's involving queries that delete rows in MVCC, but I don't understand how SIV's are detected when only select and insert queries are used. For example, this example in AWS docs: <a href="https://aws.amazon.com/premiumsupport/knowledge-center/redshift-serializable-isolation/" rel="nofollow noreferrer">https://aws.amazon.com/premiumsupport/knowledge-center/redshift-serializable-isolation/</a></p>
<p>Does anyone have any idea?</p>