Re: [GENERAL] Deadlock with single update statement?
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 ORDER BY FOR UPDATE where is indexed. -- Álvaro Herrerahttps://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Deadlock with single update statement?
On Sat, Jun 10, 2017 at 03:16:26PM -0400, Tom Lane wrote: > Rob Nikander writes: > >> On Jun 10, 2017, at 10:34 AM, Tom Lane 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
Re: [GENERAL] Deadlock with single update statement?
Rob Nikander writes: >> On Jun 10, 2017, at 10:34 AM, Tom Lane 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. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Deadlock with single update statement?
> On Jun 10, 2017, at 10:34 AM, Tom Lane 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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Deadlock with single update statement?
Rob Nikander writes: > I’m trying to track down a deadlock happening in a live app. I’m wondering > about statements like this, which select more than one row to update: > update t set num = 1 where name = ‘foo’; > It appears to be causing a deadlock, but I can’t reproduce it on my test > database. Could two threads, each running this update, get in a deadlock? In > other words, are both of the following true: > 1. The update locks each row in sequence, not all at once. > 2. The order of the row locking could vary from one thread to the next. Yes and yes. I can think of at least two explanations for (2): A. Different sessions are picking different plans for the query. This seems unlikely if the queries are really exactly identical in each session, but if there are additional WHERE conditions that could vary, then it seems entirely plausible. B. The query selects enough rows-to-be-modified that the plan ends up being basically a seqscan, and the table is large enough that the "synchronized scan" logic kicks in. In that case each session will scan the table circularly from an essentially-random start point, producing a different row locking order. If it's (B) you could ameliorate the problem by disabling syncscan, but it'd be better to adjust the query to ensure a deterministic update order. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Deadlock with single update statement?
Hi, I’m trying to track down a deadlock happening in a live app. I’m wondering about statements like this, which select more than one row to update: update t set num = 1 where name = ‘foo’; It appears to be causing a deadlock, but I can’t reproduce it on my test database. Could two threads, each running this update, get in a deadlock? In other words, are both of the following true: 1. The update locks each row in sequence, not all at once. 2. The order of the row locking could vary from one thread to the next. thanks, Rob -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general