[BUG] Deadlock detected when update with multiple clients

From: "tanghy(dot)fnst(at)fujitsu(dot)com" <tanghy(dot)fnst(at)fujitsu(dot)com>
To: PostgreSQL mailing lists <pgsql-bugs(at)lists(dot)postgresql(dot)org>
Subject: [BUG] Deadlock detected when update with multiple clients
Date: 2021-09-14 03:03:40
Message-ID: OS0PR01MB611318903C12DB5B08562B95FBDA9@OS0PR01MB6113.jpnprd01.prod.outlook.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

Hi

I met a deadlock when I update amounts of text-type data with multiple clients.
And there's no deadlock if I set autovacuum to off. Error messages about the
deadlock are as follows.

2021-09-14 09:18:40.392 CST [428452] ERROR: deadlock detected
2021-09-14 09:18:40.392 CST [428452] DETAIL: Process 428452 waits for ShareLock on transaction 1725; blocked by process 428455.
Process 428455 waits for ShareLock on transaction 1723; blocked by process 428452.
Process 428452: UPDATE tbl SET body = upper(body);
Process 428455: UPDATE tbl SET body = upper(body);
2021-09-14 09:18:40.392 CST [428452] HINT: See server log for query details.
2021-09-14 09:18:40.392 CST [428452] CONTEXT: while rechecking updated tuple (0,1) in relation "tbl"
2021-09-14 09:18:40.392 CST [428452] STATEMENT: UPDATE tbl SET body = upper(body);

I'm not sure if I used it inappropriately, or it's unexpected. Could someone
take a look at this problem?

Attach a script to reproduce it. It took about 2~3 minutes to run it on my
machine.

FYI, I found this problem on HEAD and I could reproduce at PG14 and PG13. I
didn't try other versions.

Regards
Tang

Attachment Content-Type Size
run.sh application/octet-stream 643 bytes

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message hubert depesz lubaczewski 2021-09-14 07:00:19 Re: [BUG] Deadlock detected when update with multiple clients
Previous Message Euler Taveira 2021-09-13 14:13:41 Re: BUG #17185: PostgreSQL performance GNU vs LLVM