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

On Sat, Jun 10, 2017 at 03:16:26PM -0400, Tom Lane wrote:
> Rob Nikander <rob.nikander@gmail.com> writes:
> >> On Jun 10, 2017, at 10:34 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> >> […] but it'd be better to adjust the query to ensure a deterministic
> >> update order.
>
> > Thank you for the answer. Since `update` has no `order by` clause, I’m guessing there’s no way to do this with just
the`update` statement, and that I should use `select … order by … for update’ for this. 
>
> Yeah, that's one easy answer.  You can probably force it with a sub-select
> in the UPDATE, as well, but it will take more thought.

I have a question about this ... I'm already using SELECT .. FOR UDPATE,
prefixed with "SET synchronize_seqscans=off".

..using "ORDER BY ctid" since our SELECT statements for a given table may
differ (previously I'd tried using "ORDER BY 1,2,...n" for each key column).

And yet I still see deadlocks.

Example:

session_id|593be2ff.68f7
log_time|2017-06-10 01:16:37.786-11
pid|26871
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
session_line|2923
message|deadlock detected

session_id|593be2ff.68f7
log_time|2017-06-10 01:16:27.633-11
pid|26871
detail|
session_line|2917
message|statement: BEGIN

session_id|593be2ff.68f7
log_time|2017-06-10 01:16:27.638-11
pid|26871
detail|
session_line|2918
message|statement: SET synchronize_seqscans=off

session_id|593be2ff.68f7
log_time|2017-06-10 01:16:27.64-11
pid|26871
detail|
session_line|2919
message|statement: SELECT db_column_name,table_name FROM huawei_m2000_counter_details ORDER BY ctid FOR UPDATE

session_id|593be2ff.68f7
log_time|2017-06-10 01:16:28.994-11
pid|26871
detail|Process holding the lock: 29467. Wait queue: 26871, 26646.
session_line|2920
message|process 26871 still waiting for ShareLock on transaction 13693494 after 1000.070 ms

session_id|593be2ff.68f7
log_time|2017-06-10 01:16:36.786-11
pid|26871
detail|
session_line|2921
message|process 26871 acquired ShareLock on transaction 13693494 after 8791.608 ms

session_id|593be2ff.68f7
log_time|2017-06-10 01:16:37.786-11
pid|26871
detail|Process holding the lock: 26646. Wait queue: .
session_line|2922
message|process 26871 detected deadlock while waiting for ShareLock on transaction 13693505 after 1000.080 ms

2nd process:

session_id|593be2fd.6816
log_time|2017-06-10 01:16:28.947-11
pid|26646
detail|
session_line|2301
message|statement: BEGIN

session_id|593be2fd.6816
log_time|2017-06-10 01:16:28.949-11
pid|26646
detail|
session_line|2302
message|statement: SET synchronize_seqscans=off

session_id|593be2fd.6816
log_time|2017-06-10 01:16:28.949-11
pid|26646
detail|
session_line|2303
message|statement: SELECT db_column_name,table_name FROM huawei_m2000_counter_details ORDER BY ctid FOR UPDATE

session_id|593be2fd.6816
log_time|2017-06-10 01:16:29.956-11
pid|26646
detail|Process holding the lock: 29467. Wait queue: 26871, 26646.
session_line|2304
message|process 26646 still waiting for ShareLock on transaction 13693494 after 1000.076 ms

session_id|593be2fd.6816
log_time|2017-06-10 01:16:36.786-11
pid|26646
detail|
session_line|2305
message|process 26646 acquired ShareLock on transaction 13693494 after 7829.560 ms

session_id|593be2fd.6816
log_time|2017-06-10 01:16:37.833-11
pid|26646
detail|
session_line|2306
message|statement: RESET synchronize_seqscans

Thanks in advance for any clue or insight.

Justin