Thread: [GENERAL] Non-overlapping updates blocking each other
On Sat, Oct 14, 2017 at 10:30 AM, Seamus Abshere <seamus@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.
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
On Sat, Oct 14, 2017 at 12:01 PM, Thomas Kellerer <spam_eater@gmx.net> wrote:
Melvin Davidson schrieb am 14.10.2017 um 17:32: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.
Since when does Postgres lock the whole table during an update?
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
>Since when does Postgres lock the whole table during an update?
When the optimizer thinks it needs to do a TABLE SCAN!
--
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.
On Sun, Oct 15, 2017 at 8:01 AM, Seamus Abshere <seamus@abshere.net> wrote:
> On Sat, Oct 14, 2017 at 10:30 AM, Seamus Abshere <seamus@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.
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.