From: | Seamus Abshere <seamus(at)abshere(dot)net> |
---|---|
To: | Melvin Davidson <melvin6925(at)gmail(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Non-overlapping updates blocking each other |
Date: | 2017-10-15 12:01:32 |
Message-ID: | 1508068892.2188016.1139279144.0E7EF58B@webmail.messagingengine.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
> 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.
From | Date | Subject | |
---|---|---|---|
Next Message | Alban Hertroys | 2017-10-15 12:02:16 | Re: EAV Designs for Multi-Tenant Applications |
Previous Message | legrand legrand | 2017-10-15 10:07:53 | Re: SAP Application deployment on PostgreSQL |