Content-Type: text/plain;
	charset=us-ascii
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3774.500.171.1.1\))
Subject: Re: Question regarding how databases support atomicity
From: Christophe Pettus <xof@thebuild.com>
In-Reply-To: <CAPqV3pQquw63Fv_fCJzAAnUWk86qHfg1cH=+mJ2N=4LGdDyrNw@mail.gmail.com>
Date: Fri, 3 May 2024 20:20:49 -0700
Cc: pgsql-general@lists.postgresql.org
Content-Transfer-Encoding: quoted-printable
Message-Id: <BA0D2CFA-7BFF-407A-98E8-41FD40B211BD@thebuild.com>
References: <CAPqV3pRO=u1Mno06WLCFxoyxbzkYTetiqTs7WaW=_fkjRPgYhw@mail.gmail.com>
 <CAPqV3pQquw63Fv_fCJzAAnUWk86qHfg1cH=+mJ2N=4LGdDyrNw@mail.gmail.com>
To: Siddharth Jain <siddhsql@gmail.com>
Archived-At: <https://www.postgresql.org/message-id/BA0D2CFA-7BFF-407A-98E8-41FD40B211BD%40thebuild.com>
Precedence: bulk


> On May 3, 2024, at 20:02, Siddharth Jain <siddhsql@gmail.com> wrote:
>=20
>=20
> The way I understand this is that if there is a failure in-between, we =
start undoing and reverting the previous operations one by one. But what =
if there is a failure and we are not able to revert an operation. How is =
that situation handled? e.g., something failed when we tried to do Step =
3. now we revert Step 2 and succeed. but when we try to revert step 1 we =
fail. what happens now? To me, it seems its impossible to guarantee true =
atomicity in general.

PostgreSQL does not "undo" operations as such.  When modifications are =
made to the database, those modifications (inserts, updates, deletes) =
are marked with the ID of the transaction that made them.  A COMMIT or =
ROLLBACK in PostgreSQL just notes if those modifications are now =
"permanent" (if the transaction committed) or "invisible" (if the =
transaction rolled back).  This technique in general is called =
Multi-Version Concurrency Control.  Here's a good presentation that =
describes how it works in PostgreSQL:

	https://momjian.us/main/writings/pgsql/mvcc.pdf=