Postgres Pro
Downloads
Home   >   mailing lists

Re: [GENERAL] Deadlock with single update statement? - Mailing list pgsql-general

From Alvaro Herrera
Subject Re: [GENERAL] Deadlock with single update statement?
Date
Msg-id 20170610234115.letm3s5pj5a5snvl@alvherre.pgsql
Whole thread Raw
In response to Re: [GENERAL] Deadlock with single update statement?  (Justin Pryzby <pryzby@telsasoft.com>)
List pgsql-general
Tree view 
Justin Pryzby wrote:

> detail|Process 26871 waits for ShareLock on transaction 13693505; blocked by process 26646.
> Process 26646 waits for ShareLock on transaction 13693504; blocked by process 26871.
> Process 26871: SELECT db_column_name,table_name FROM huawei_m2000_counter_details ORDER BY ctid FOR UPDATE
> Process 26646: SELECT db_column_name,table_name FROM huawei_m2000_counter_details ORDER BY ctid FOR UPDATE

Uh, this is locking the whole table.  Is there no useful WHERE?  What
you should be doing is SELECT WHERE <key values> ORDER BY <key> FOR
UPDATE where <key> is indexed.

--
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

pgsql-general by date:

Previous
From: rob stone
Date:
Subject: Re: [GENERAL] Inconsistent performance with LIKE and bind variableon long-lived connection
Next
From: Alvaro Herrera
Date:
Subject: Re: [GENERAL] ERROR: unexpected chunk number 0 (expected 1) fortoast value 76753264 in pg_toast_10920100