Re: Non-overlapping updates blocking each other

On Sat, Oct 14, 2017 at 10:30 AM, Seamus Abshere <seamus(at)abshere(dot)net> wrote:

> hi,
>
> I've got 2 updates on non-overlapping uuid (primary key) ranges. For
> example:
>
> 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?
>
> Thanks,
> Seamus
>
> --
> Seamus Abshere, SCEA
> https://www.faraday.io
> https://github.com/seamusabshere
> https://linkedin.com/in/seamusabshere
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

Have you done and EXPLAIN on each of the updates?
More than likely, the optimizer has determined that a table scan is best,
in which case it will use a table lock.
That means one updates will be blocking each other.

You can also execute the following query and check the wait_event_type to
verify.

SELECT c.datname,
c.pid as pid,
c.client_addr,
c.usename as user,
c.query,
c.wait_event,
c.wait_event_type,
l.pid as blocked_by,
c.query_start,
current_timestamp - c.query_start as duration
FROM pg_stat_activity c
LEFT JOIN pg_locks l1 ON (c.pid = l1.pid and not l1.granted)
LEFT JOIN pg_locks l2 on (l1.relation = l2.relation and l2.granted)
LEFT JOIN pg_stat_activity l ON (l2.pid = l.pid)
LEFT JOIN pg_stat_user_tables t ON (l1.relation = t.relid)
WHERE pg_backend_pid() <> c.pid
ORDER BY datname,
query_start;

--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.