On Sun, Oct 15, 2017 at 8:01 AM, Seamus Abshere <sea...@abshere.net> wrote:
> > On Sat, Oct 14, 2017 at 10:30 AM, Seamus Abshere <sea...@abshere.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.
