Re: IO related waits

On Thu, 19 Sept, 2024, 8:40 pm Adrian Klaver, <adrian(dot)klaver(at)aklaver(dot)com>
wrote:

> On 9/19/24 05:24, Greg Sabino Mullane wrote:
> > On Thu, Sep 19, 2024 at 5:17 AM veem v <veema0000(at)gmail(dot)com
>
> > This is really difficult to diagnose from afar with only snippets of
> > logs and half-complete descriptions of your business logic. Pull
> > everyone involved into a room with a whiteboard, and produce a document
> > describing exactly what your application does, and how it is doing it.
> > Switch from reactive to proactive.
>
>
Able to reproduce this deadlock graph as below. Now my question is , this
is a legitimate scenario in which the same ID can get inserted from
multiple sessions and in such cases it's expected to skip that (thus "On
conflict Do nothing" is used) row. But as we see it's breaking the code
with deadlock error during race conditions where a lot of parallel threads
are operating. So how should we handle this scenario? Will setting the
"lock_timeout" parameter at session level will help us anyway here?

Create table t1(id numeric primary key);

Session 1:-
Begin
Insert into table1 values(1) on conflict(id) do nothing;

Session 2:
Begin
Insert into table1 values(2) on conflict(id) do nothing;

session 1:-
Insert into table1 values (2) on conflict(id) do nothing;

It got hung as it waits for the session-2 to commit/rollback the transaction

Session-2:-
Insert into table1 values(1) on conflict(id) do nothing;

deadlock detected... and this session terminated.

Regards
Veem