Thread: [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
morethan 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

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
morethan 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
thisupdate, 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

> 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. 

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

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

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