On Sat, Oct 14, 2017 at 10:30 AM, Seamus Abshere <sea...@abshere.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@postgresql.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.
