On Sat, Jun 10, 2017 at 03:16:26PM -0400, Tom Lane wrote: > Rob Nikander <rob.nikan...@gmail.com> writes: > >> On Jun 10, 2017, at 10:34 AM, Tom Lane <t...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
