On 2015-09-02 PM 06:41, Amit Langote wrote: > > I think Albe may have a point here... > > Even inherited updates case appears to cause a deadlock if they are in > different queries. Demonstrated below: > > -- setup > CREATE TABLE t(a int); > CREATE TABLE t1() INHERITS(t); > CREATE TABLE t2() INHERITS(t); > > INSERT INTO t1 VALUES (1); > INSERT INTO t2 VALUES (2); > > -- in session 1 > BEGIN; > UPDATE t SET a = a + 1 WHERE a = 1; > <ok> > > -- in session 2 > BEGIN; > UPDATE t SET a = a + 1 WHERE a = 2; > <ok> > > -- back in session 1 > UPDATE t SET a = a + 1 WHERE a = 2; > <waits> > > -- back in session 2 > UPDATE t SET a = a + 1 WHERE a = 1; > <deadlock is detected> >
Which, I now realize, is not the worry Amit Kapila's expresses.
The deadlock was *indeed detected* in this case, with all the locks in the same PG instance. In a sharded environment with multiple PG instances, that becomes tricky. DLM (distributed lock manager/deadlock detector) seems indeed necessary as Amit K. suspects.
Right. XC/XL did not address this issue and they rely on statement timeouts to break distributed deadlocks.
From:
Fujii Masao Date: Subject:
Re: PENDING_LIST_CLEANUP_SIZE - maximum size of GIN pending list Re:
HEAD seems to generate larger WAL regarding GIN index