Re: Horizontal scalability/sharding

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.