On Sun, Oct 15, 2017 at 8:01 AM, Seamus Abshere <seamus@xxxxxxxxxxx> wrote:
> On Sat, Oct 14, 2017 at 10:30 AM, Seamus Abshere <seamus@xxxxxxxxxxx>
> > UPDATE [...] WHERE id BETWEEN 'ff000000-0000-0000-0000-000000000000' AND On Sat, Oct 14, 2017, at 12:32 PM, Melvin Davidson wrote:
> > '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?
> 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.![]()
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
