From: Laurenz Albe <laurenz.albe@cybertec.at>
To: ROHIT SACHDEVA <sachdeva.rohit648@gmail.com>
Cc: pgsql-admin@lists.postgresql.org
Subject: Re: ERROR: could not serialize access due to concurrent update
Date: Wed, 17 May 2023 08:10:08 +0200
Message-ID: <c7a7b116bd28a89231c889c2c827d62568b50f93.camel@cybertec.at> (raw)
In-Reply-To: <CAKDb7aFLkz2btDpnkmefDBa-N=kUJ9HYhPmmD1o5+sLqYewY6A@mail.gmail.com>
References: <CAKDb7aFR1B7wvaLBxBDdark7jx=y31BY74WKdrPF1Zq5xwT+mA@mail.gmail.com>
<CAKDb7aHL=NXzcFyL5oM3V6=D9P4VVdx_Gd=FskdbeqCotKFSSg@mail.gmail.com>
<580a59b7e6ef605000c5dde862300e4290d9445a.camel@cybertec.at>
<CAKDb7aGaZ94nxr1gSw2NfLu5arec9G+SOBDGRV7njxG_C5TZeg@mail.gmail.com>
<6d2162718b0af8504c5f85013b78dc97a2046d59.camel@cybertec.at>
<CAKDb7aFLkz2btDpnkmefDBa-N=kUJ9HYhPmmD1o5+sLqYewY6A@mail.gmail.com>
I'm getting a bit tired of reformatting your replies into the correct order...
On Tue, 2023-05-16 at 22:54 +0530, ROHIT SACHDEVA wrote:
> On Tue, 16 May, 2023, 10:05 pm Laurenz Albe, <laurenz.albe@cybertec.at> wrote:
> > On Tue, 2023-05-16 at 18:39 +0530, ROHIT SACHDEVA wrote:
> > > I am getting this error in the application logs, and this error is frequently coming.
> > > Could someone suggest what would be the best solution to overcome these types of errors.
> > > Isolation of the database is read committed. Should I change the isolation?
> > >
> > > Also foreign table scan is there on query.
> > >
> > > It's a postgres_fdw i am using.
> >
> > That is normal if you modify any data via FDW, because one local query can result in several
> > foreign queries, which have to see the same snapshot of the data.
> >
> > Repeat queries that fail that way, and keep your transactions as short as you can.
>
> But due to this error our transaction is aborted and application team are getting these errors in their logs.
>
> How can I figure this out?
> Can there be work around for not getting this error.
You should be able to avoid these serialization conflicts by using SELECT ... FOR NO KEY UPDATE
whenever you read data that you intend to modify later.
Yours,
Laurenz Albe
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 using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: laurenz.albe@cybertec.at, sachdeva.rohit648@gmail.com, pgsql-admin@lists.postgresql.org
Subject: Re: ERROR: could not serialize access due to concurrent update
In-Reply-To: <c7a7b116bd28a89231c889c2c827d62568b50f93.camel@cybertec.at>
* 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