MIME-Version: 1.0
In-Reply-To: <CAPqV3pQquw63Fv_fCJzAAnUWk86qHfg1cH=+mJ2N=4LGdDyrNw@mail.gmail.com>
References: <CAPqV3pRO=u1Mno06WLCFxoyxbzkYTetiqTs7WaW=_fkjRPgYhw@mail.gmail.com>
 <CAPqV3pQquw63Fv_fCJzAAnUWk86qHfg1cH=+mJ2N=4LGdDyrNw@mail.gmail.com>
From: "David G. Johnston" <david.g.johnston@gmail.com>
Date: Fri, 3 May 2024 20:10:31 -0700
Message-ID: <CAKFQuwb2MtE02QgMQ3vvLOj7bvGMar6qwnigCBBaYLBKsdk3xg@mail.gmail.com>
Subject: Re: Question regarding how databases support atomicity
To: Siddharth Jain <siddhsql@gmail.com>
Cc: "pgsql-general@lists.postgresql.org" <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000e191c70617982d42"
Archived-At: <https://www.postgresql.org/message-id/CAKFQuwb2MtE02QgMQ3vvLOj7bvGMar6qwnigCBBaYLBKsdk3xg%40mail.gmail.com>
Precedence: bulk

--000000000000e191c70617982d42
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Friday, May 3, 2024, Siddharth Jain <siddhsql@gmail.com> wrote:

>
>
> On Fri, May 3, 2024 at 8:00=E2=80=AFPM Siddharth Jain <siddhsql@gmail.com=
> wrote:
>
>> I am trying to sharpen my understanding of databases. Let's say there is
>> an operation foo as part of the public API that internally translates to
>> more than 1 operation - I am sure there are examples like this in postgr=
es.
>> So to do foo we have to do following in order in all or none fashion:
>>
>> 1. Step 1
>> 2. Step 2
>> 3. Step 3
>>
>> 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.
>>
>
Not in PostgreSQL.  All work performed is considered provisional until a
commit succeeds.  At which point all provisional work, which had been
tagged with the same transaction identifier, becomes reality to the rest of
the system, by virtue of marking the transaction live.  If the commit never
happens, either because of error, rollback, or session end, the transaction
ends up being left unalive and eventually is cleaned up.

You need to ensure a =E2=80=9Cbegin=E2=80=9D happens before Step 1 and a =
=E2=80=9Ccommit=E2=80=9D after
Step 3.

David J.

--000000000000e191c70617982d42
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Friday, May 3, 2024, Siddharth Jain &lt;<a href=3D"mailto:siddhsql@gmail=
.com">siddhsql@gmail.com</a>&gt; wrote:<br><blockquote class=3D"gmail_quote=
" style=3D"margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><=
div dir=3D"ltr"><br></div><br><div class=3D"gmail_quote"><div dir=3D"ltr" c=
lass=3D"gmail_attr">On Fri, May 3, 2024 at 8:00=E2=80=AFPM Siddharth Jain &=
lt;<a href=3D"mailto:siddhsql@gmail.com" target=3D"_blank">siddhsql@gmail.c=
om</a>&gt; wrote:<br></div><blockquote class=3D"gmail_quote" style=3D"margi=
n:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex=
"><div dir=3D"ltr">I am trying to sharpen my understanding of databases. Le=
t&#39;s say there is an operation foo as part of the public API that intern=
ally translates to more than 1 operation - I am sure there are examples lik=
e this in postgres. So to do foo we have to do following in order in all or=
 none fashion:<div><br></div><div>1. Step 1</div><div>2. Step 2</div><div>3=
. Step 3</div><div><br></div><div>The way I understand this is that if ther=
e is a failure in-between, we start undoing and reverting the previous oper=
ations one by one.</div></div></blockquote></div></blockquote><div><br></di=
v><div>Not in PostgreSQL.=C2=A0 All work performed is considered provisiona=
l until a commit succeeds.=C2=A0 At which point all provisional work, which=
 had been tagged with the same transaction identifier, becomes reality to t=
he rest of the system, by virtue of marking the transaction live.=C2=A0 If =
the commit never happens, either because of error, rollback, or session end=
, the transaction ends up being left unalive and eventually is cleaned up.<=
/div><div><br></div><div>You need to ensure a =E2=80=9Cbegin=E2=80=9D happe=
ns before Step 1 and a =E2=80=9Ccommit=E2=80=9D after Step 3.</div><div><br=
></div><div>David J.</div><div><br></div>

--000000000000e191c70617982d42--