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
Rob Nikander <rob.nikander@gmail.com> 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
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.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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.
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
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general