Re: Question on SSI - Serializable Snapshot Isolation

[Siddharth Jain]

ok thanks tom.

On Mon, Apr 17, 2023 at 2:39 PM Tom Lane  wrote:

> Siddharth Jain  writes:
> > When using SSI , lets say we have
> two
> > transactions T1 and T2 and there is a serialization conflict. Postgres
> > knows when one or the other transaction is doomed to fail
> > [image: image.png]
>
> Please don't use images for things that could perfectly well be
> expressed as text.  They're not quotable, they may not show up
> in the archives (as this one doesn't), etc etc.  email is a text
> medium, despite Google's attempts to convince you otherwise.
>
> > but will not raise serialization error until the transaction commits.
> This
> > can cause a massive perf hit because the transactions could be long
> > running. Why not raise the error early on when the conflict is detected
> to
> > avoid wasting CPU and other resources? Can anyone explain this to me?
>
> Try src/backend/storage/lmgr/README-SSI, notably this bit:
>
> * This SSI implementation makes an effort to choose the
> transaction to be canceled such that an immediate retry of the
> transaction will not fail due to conflicts with exactly the same
> transactions.  Pursuant to this goal, no transaction is canceled
> until one of the other transactions in the set of conflicts which
> could generate an anomaly has successfully committed.  This is
> conceptually similar to how write conflicts are handled.
>
> The main point here is that "at least one of these transactions
> will have to fail" is very different from "all of these transactions
> have to fail".  If the implementation prematurely forecloses on one
> of them, it may be that *no* useful work gets done because the others
> also fail later on for other reasons; moreover, it might be that the
> victim transaction could have committed after those others failed.
> Withholding judgment about which one to cancel until something has
> committed ensures that more than zero work gets completed.
>
> Also note that AFAICS we do notice fairly promptly once a transaction
> has been marked as doomed; it's not the case that we wait till the
> transaction's own commit to check that.
>
> regards, tom lane
>

Re: Question on SSI - Serializable Snapshot Isolation

[Tom Lane]

Siddharth Jain  writes:
> When using SSI , lets say we have two
> transactions T1 and T2 and there is a serialization conflict. Postgres
> knows when one or the other transaction is doomed to fail
> [image: image.png]

Please don't use images for things that could perfectly well be
expressed as text.  They're not quotable, they may not show up
in the archives (as this one doesn't), etc etc.  email is a text
medium, despite Google's attempts to convince you otherwise.

> but will not raise serialization error until the transaction commits. This
> can cause a massive perf hit because the transactions could be long
> running. Why not raise the error early on when the conflict is detected to
> avoid wasting CPU and other resources? Can anyone explain this to me?

Try src/backend/storage/lmgr/README-SSI, notably this bit:

* This SSI implementation makes an effort to choose the
transaction to be canceled such that an immediate retry of the
transaction will not fail due to conflicts with exactly the same
transactions.  Pursuant to this goal, no transaction is canceled
until one of the other transactions in the set of conflicts which
could generate an anomaly has successfully committed.  This is
conceptually similar to how write conflicts are handled.

The main point here is that "at least one of these transactions
will have to fail" is very different from "all of these transactions
have to fail".  If the implementation prematurely forecloses on one
of them, it may be that *no* useful work gets done because the others
also fail later on for other reasons; moreover, it might be that the
victim transaction could have committed after those others failed.
Withholding judgment about which one to cancel until something has
committed ensures that more than zero work gets completed.

Also note that AFAICS we do notice fairly promptly once a transaction
has been marked as doomed; it's not the case that we wait till the
transaction's own commit to check that.

regards, tom lane

Question on SSI - Serializable Snapshot Isolation

[Siddharth Jain]

Hi All,

When using SSI , lets say we have two
transactions T1 and T2 and there is a serialization conflict. Postgres
knows when one or the other transaction is doomed to fail
[image: image.png]
but will not raise serialization error until the transaction commits. This
can cause a massive perf hit because the transactions could be long
running. Why not raise the error early on when the conflict is detected to
avoid wasting CPU and other resources? Can anyone explain this to me? Is
this an area that could be improved upon? Thanks.

S.