有谁知道SQL数据库如何检测可串行的隔离违规行为（SIV）？似乎只是简单地强迫所有交易执行的置换，以找到并发执行结果的匹配，以验证序列化性不会扩展。
根据第三方研究人员的本文：
SIV发生在同一时间进行两次交易时，而最近的一行交易却承诺了一些已删除的行，这是较不太最新的交易后来又尝试删除的。 MVCC无法处理的情况，因此必须与SIV进行中止。
这对于检测SIV的涉及查询的查询是有意义的，但是当仅使用选择和插入查询时，我不明白如何检测到SIV。例如，此示例在AWS文档中： htttps：htttps：// aws。 Amazon.com/premuimsupport/knowledge-center/redshift-serializable-isolation/
有人有任何想法吗？
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.
According to this paper from a third party researcher: https://amazonredshiftresearchproject.org/white_papers/downloads/multi_version_concurrency_control_and_serialization_isolation_failure.pdf
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.
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: https://aws.amazon.com/premiumsupport/knowledge-center/redshift-serializable-isolation/
Does anyone have any idea?
发布评论
评论（2）
让我简化事情，因为发生的很多事情很复杂，很容易错过树木的森林。
在开始时与数据库状态匹配的数据库状态
发生。
其他交易的初始状态。
就是这样。 RedShift并不“知道”另一笔交易对本交易所产生的结果的重要变化。只是它可能是物质的。由于可能是物质的，因此存在序列化危害，并且一项交易被中止以防止不确定结果的可能性。
这个主题有很多复杂性和细微差别，只有在您试图理解某些案例，时间和SQL工作而其他人不行的情况下，只有很重要。这将陷入谓词锁定，这就是红移“知道”的方式，如果在某个地方进行了一些更改，则会影响此交易重要的初始状态的一部分。即一堆簿记。这就是为什么在链接的知识中心文章中“从tab1中选择 *”很重要的原因 - 它为此交易创建了“谓词锁”。
Let me simplify things down as a lot of what is going on is complicated and it is easy to miss the forest for the trees.
database state that matches the database state at the time the BEGIN
occurred.
other transaction's initial state.
That's it. Redshift doesn't "know" that the changes that the other transaction is material to the results this transaction is making. Just that it COULD be material. Since it COULD be material then the serialization hazard exists and one transaction is aborted to prevent the possibility of indeterminant results.
There's a lot of complexity and nuance to this topic that only is important if you are trying to understand why certain cases, timings, and SQL worked and others didn't. This gets into predicate locking which is how Redshift "knows" if some change being made somewhere else is effecting a part of the initial state that is material to this transaction. I.E. a bunch of bookkeeping. This is why the "select * from tab1" matters in the linked knowledge-center article - it creates the "predicate lock" for this transaction.
PostgreSQL使用启发式方法检测违规行为。读取数据会导致谓词锁（
sireadlock），并检查危险结构，这必然发生在每种序列化违规中。这意味着您可以获得假积极的序列化错误，但绝不是虚假的。这都是描述的和在文档中在那引用的科学论文那个区域。
PostgreSQL detects serialization violations using a heuristics. Reading data causes predicate locks (
SIReadLock) to be taken, and it checks for dangerous structures, which necessarily occur in every serialization violation. That means that you can get false positive serialization errors, but never false negatives.This is all described in the documentation and in the scientific paper referenced there, and we can hope that Amazon didn't hack up PostgreSQL too badly in that area.