From: | Melvin Davidson <melvin6925(at)gmail(dot)com> |
---|---|
To: | Seamus Abshere <seamus(at)abshere(dot)net> |
Cc: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Non-overlapping updates blocking each other |
Date: | 2017-10-15 14:59:25 |
Message-ID: | CANu8Fiw5BjkTrO+-wOdrP_qeWxsF0-8fnM3C9N8gb5fTDsKoKA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Sun, Oct 15, 2017 at 8:01 AM, Seamus Abshere <seamus(at)abshere(dot)net> wrote:
> > On Sat, Oct 14, 2017 at 10:30 AM, Seamus Abshere <seamus(at)abshere(dot)net>
> > > UPDATE [...] WHERE id BETWEEN 'ff000000-0000-0000-0000-000000000000'
> AND
> > > 'ff0fffff-ffff-ffff-ffff-ffffffffffff'
> > > and
> > > UPDATE [...] WHERE id BETWEEN 'f8c00000-0000-0000-0000-000000000000'
> AND
> > > 'f8ffffff-ffff-ffff-ffff-ffffffffffff'
> > > Yet one blocks the other one. How is this possible?
>
> On Sat, Oct 14, 2017, at 12:32 PM, Melvin Davidson wrote:
> > More than likely, the optimizer has determined that a table scan is best,
> > in which case it will use a table lock.
> > You can also execute the following query and check the wait_event_type to
> > verify.
>
> hi Melvin,
>
> Very interesting! The result:
>
> wait_event | page
> wait_event_type | Lock
>
> So I guess this means that the ids don't overlap, but they are sometimes
> found in the same page, and the whole page gets locked?
>
> Any narrative (pretending I don't know anything) would be very helpful.
>
> Thanks!
> Seamus
>
> PS. I do a SELECT 1 FROM x WHERE [ID_RANGE] FOR UPDATE right before the
> update, but that's to prevent a race condition. The id ranges still
> don't overlap.
>
Seamus,
As Tom suggests, to get an exact cause of your problem, it is very
important we get the following additional information
1. Exact PostgreSQL version. IE: SELECT version();
2. Your O/S
3. The full structure of your table in query, including constraints
4. The full, exact queries.
5. The FULL output from the query I gave you.
--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
From | Date | Subject | |
---|---|---|---|
Next Message | Igal @ Lucee.org | 2017-10-15 19:28:36 | Re: Delete Duplicates with Using |
Previous Message | Greg Sabino Mullane | 2017-10-15 13:26:13 | Re: Is pgbouncer still maintained? |