From: Tom Lane <tgl@sss.pgh.pa.us>
To: Siddharth Jain <siddhsql@gmail.com>
Cc: pgsql-general@lists.postgresql.org
Subject: Re: Question on SSI - Serializable Snapshot Isolation
Date: Mon, 17 Apr 2023 17:39:41 -0400
Message-ID: <2518822.1681767581@sss.pgh.pa.us> (raw)
In-Reply-To: <CAPqV3pTLaijfRegcKXkhqXnFVxWxXR7sSmDvbXFts_cn+bmquQ@mail.gmail.com>
References: <CAPqV3pTLaijfRegcKXkhqXnFVxWxXR7sSmDvbXFts_cn+bmquQ@mail.gmail.com>
Siddharth Jain <siddhsql@gmail.com> writes:
> When using SSI <https://wiki.postgresql.org/wiki/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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-general@postgresql.org
Cc: tgl@sss.pgh.pa.us, siddhsql@gmail.com
Subject: Re: Question on SSI - Serializable Snapshot Isolation
In-Reply-To: <2518822.1681767581@sss.pgh.pa.us>
References: <CAPqV3pTLaijfRegcKXkhqXnFVxWxXR7sSmDvbXFts_cn+bmquQ@mail.gmail.com> <2518822.1681767581@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox