Re: Horizontal scalability/sharding

On Wed, Sep 2, 2015 at 3:55 PM, Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp>
wrote:

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

Thanks,
Pavan

--
Pavan Deolasee http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services