Global Deadlock Information

Hi,

I'd like to start a thread for discussion of the second item on the
ClusterFeatures [1] list: Global Deadlock Information.

IIRC there are two aspects to this item: a) the plain notification of a
deadlock and b) some way to control or intercept deadlock resolution.

The problem this item seems to address is the potential for deadlocks
between transactions on different nodes. Or put another way: between a
local transaction and one that's to be applied from a remote node (or
even between two remote ones - similar issue, though). To ensure
congruency between nodes, they must take the same measures to resolve
the deadlock, i.e. abort the same transaction(s).

I certainly disagree with the statement on the wiki that the
"statement_timeout is the way to avoid global deadlocks", because I
don't want to have to wait that long until a deadlock gets resolved.
Further it doesn't even guarantee congruency, depending on the
implementation of your clustering solution.

I fail to see how a plain notification API would help much. After all,
this could result in one node notifying having aborted transaction A to
resolve a deadlock while another node notifies having aborted
transaction B. You'd end up having to abort two (or more) transaction
instead of just one to resolve a conflict.

It could get more useful, if enabling such a notification would turn off
the existing deadlock resolver and leave the resolution of the deadlock
to the clustering solution. I'd call that an interception.

Such an interception API should IMO provide a way to register a
callback, which replaces the current deadlock resolver. Upon detection
of a deadlock, the callback should get a list of transaction ids that
are part of the lock cycle. It's then up to that callback, to chose one
and abort that to resolve the conflict.

And now, Greg's List:
> 1) What feature does this help add from a user perspective?

Preventing cluster-wide deadlocks (while maintaining congruency of
replicas).

> 2) Which replication projects would be expected to see an improvement
> from this addition?

I suspect all multi-master solutions are affected, certainly Postgres-R
would benefit. Single-master ones certainly don't need it.

> 3) What makes it difficult to implement?

I don't see any real stumbling block. Deciding on an API needs consensus.

> 4) Are there any other items on the list this depends on, or that it
> is expected to have a significant positive/negative interaction with?

Not that I know of.

> 5) What replication projects include a feature like this already, or a
> prototype of a similar one, that might be used as a proof of concept
> or example implementation?

Old Postgres-R versions once had such an interception, but it currently
lacks a solution for this problem. I don't know of any other project
that's already solved this.

> 6) Who is already working on it/planning to work on it/needs it for
> their related project?

I'm not currently working on it and don't plan to do so (at least) until
PgCon 2010.

Cluster hackers, is this a good summary which covers your needs as well?
Something missing?

Regards

Markus Wanner

[1]: feature wish list of cluster hackers:
http://wiki.postgresql.org/wiki/ClusterFeatures

Hi Markus,

I attempted in two ways to resolve global deadlock situation
in the PostgresForest development.

(1) Use the lock_timeout to avoid from a global deadlock.

The lock_timeout feature is a very simple way to avoid
from the global deadlock situation.

I disagree "statement_timeout is the way to avoid global
deadlocks" too, because the statement_timeout kills
the healthy/long-running transaction by its timeout.

Some developers (including me!) proposed the lock_timeout
GUC option.

http://archives.postgresql.org/pgsql-hackers/2004-06/msg00935.php
http://archives.postgresql.org/pgsql-hackers/2010-01/msg01167.php

I still believe the "lock timeout" feature could help
resolving a global deadlock in the cluster environment.

(2) Use the global wait-for graph to detect a global deadlock.

I had an experimental implemetation to use the global wait-for
graph to prevent the global deadlock.

http://en.wikipedia.org/wiki/Deadlock#Distributed_deadlock

I used the node(server) identifiers and the pg_locks information
to build the global wait-for graph, and the kill signal
(or pg_cancel()?) to abort a victim transaction causing
the deadlock.

I don't think the callback function is needed to replace
the current deadlock resolution feature,
but I agree we need a consensus how we could avoid
the global deadlock situation in the cluster.

Thanks,

On 2010/02/06 18:13, Markus Wanner wrote:
> Hi,
>
> I'd like to start a thread for discussion of the second item on the
> ClusterFeatures [1] list: Global Deadlock Information.
>
> IIRC there are two aspects to this item: a) the plain notification of a
> deadlock and b) some way to control or intercept deadlock resolution.
>
> The problem this item seems to address is the potential for deadlocks
> between transactions on different nodes. Or put another way: between a
> local transaction and one that's to be applied from a remote node (or
> even between two remote ones - similar issue, though). To ensure
> congruency between nodes, they must take the same measures to resolve
> the deadlock, i.e. abort the same transaction(s).
>
> I certainly disagree with the statement on the wiki that the
> "statement_timeout is the way to avoid global deadlocks", because I
> don't want to have to wait that long until a deadlock gets resolved.
> Further it doesn't even guarantee congruency, depending on the
> implementation of your clustering solution.
>
> I fail to see how a plain notification API would help much. After all,
> this could result in one node notifying having aborted transaction A to
> resolve a deadlock while another node notifies having aborted
> transaction B. You'd end up having to abort two (or more) transaction
> instead of just one to resolve a conflict.
>
> It could get more useful, if enabling such a notification would turn off
> the existing deadlock resolver and leave the resolution of the deadlock
> to the clustering solution. I'd call that an interception.
>
> Such an interception API should IMO provide a way to register a
> callback, which replaces the current deadlock resolver. Upon detection
> of a deadlock, the callback should get a list of transaction ids that
> are part of the lock cycle. It's then up to that callback, to chose one
> and abort that to resolve the conflict.
>
> And now, Greg's List:
> > 1) What feature does this help add from a user perspective?
>
> Preventing cluster-wide deadlocks (while maintaining congruency of
> replicas).
>
> > 2) Which replication projects would be expected to see an improvement
> > from this addition?
>
> I suspect all multi-master solutions are affected, certainly Postgres-R
> would benefit. Single-master ones certainly don't need it.
>
> > 3) What makes it difficult to implement?
>
> I don't see any real stumbling block. Deciding on an API needs consensus.
>
> > 4) Are there any other items on the list this depends on, or that it
> > is expected to have a significant positive/negative interaction with?
>
> Not that I know of.
>
> > 5) What replication projects include a feature like this already, or a
> > prototype of a similar one, that might be used as a proof of concept
> > or example implementation?
>
> Old Postgres-R versions once had such an interception, but it currently
> lacks a solution for this problem. I don't know of any other project
> that's already solved this.
>
> > 6) Who is already working on it/planning to work on it/needs it for
> > their related project?
>
> I'm not currently working on it and don't plan to do so (at least) until
> PgCon 2010.
>
>
> Cluster hackers, is this a good summary which covers your needs as well?
> Something missing?
>
> Regards
>
> Markus Wanner
>
> [1]: feature wish list of cluster hackers:
> http://wiki.postgresql.org/wiki/ClusterFeatures
>
>

--
NAGAYASU Satoshi <satoshi(dot)nagayasu(at)gmail(dot)com>

Hi,

I'm very interested in how log it takes to determine the global
deadlock using global wait-for graph and if global deadlock detection
disturb other on-going transactions.

----------
Koichi Suzuki

2010/2/7 Satoshi Nagayasu <satoshi(dot)nagayasu(at)gmail(dot)com>:
> Hi Markus,
>
> I attempted in two ways to resolve global deadlock situation
> in the PostgresForest  development.
>
> (1) Use the lock_timeout to avoid from a global deadlock.
>
> The lock_timeout feature is a very simple way to avoid
> from the global deadlock situation.
>
> I disagree "statement_timeout is the way to avoid global
> deadlocks" too, because the statement_timeout kills
> the healthy/long-running transaction by its timeout.
>
> Some developers (including me!) proposed the lock_timeout
> GUC option.
>
> http://archives.postgresql.org/pgsql-hackers/2004-06/msg00935.php
> http://archives.postgresql.org/pgsql-hackers/2010-01/msg01167.php
>
> I still believe the "lock timeout" feature could help
> resolving a global deadlock in the cluster environment.
>
> (2) Use the global wait-for graph to detect a global deadlock.
>
> I had an experimental implemetation to use the global wait-for
> graph to prevent the global deadlock.
>
> http://en.wikipedia.org/wiki/Deadlock#Distributed_deadlock
>
> I used the node(server) identifiers and the pg_locks information
> to build the global wait-for graph, and the kill signal
> (or pg_cancel()?) to abort a victim transaction causing
> the deadlock.
>
> I don't think the callback function is needed to replace
> the current deadlock resolution feature,
> but I agree we need a consensus how we could avoid
> the global deadlock situation in the cluster.
>
> Thanks,
>
> On 2010/02/06 18:13, Markus Wanner wrote:
>>
>> Hi,
>>
>> I'd like to start a thread for discussion of the second item on the
>> ClusterFeatures [1] list: Global Deadlock Information.
>>
>> IIRC there are two aspects to this item: a) the plain notification of a
>> deadlock and b) some way to control or intercept deadlock resolution.
>>
>> The problem this item seems to address is the potential for deadlocks
>> between transactions on different nodes. Or put another way: between a
>> local transaction and one that's to be applied from a remote node (or
>> even between two remote ones - similar issue, though). To ensure
>> congruency between nodes, they must take the same measures to resolve
>> the deadlock, i.e. abort the same transaction(s).
>>
>> I certainly disagree with the statement on the wiki that the
>> "statement_timeout is the way to avoid global deadlocks", because I
>> don't want to have to wait that long until a deadlock gets resolved.
>> Further it doesn't even guarantee congruency, depending on the
>> implementation of your clustering solution.
>>
>> I fail to see how a plain notification API would help much. After all,
>> this could result in one node notifying having aborted transaction A to
>> resolve a deadlock while another node notifies having aborted
>> transaction B. You'd end up having to abort two (or more) transaction
>> instead of just one to resolve a conflict.
>>
>> It could get more useful, if enabling such a notification would turn off
>> the existing deadlock resolver and leave the resolution of the deadlock
>> to the clustering solution. I'd call that an interception.
>>
>> Such an interception API should IMO provide a way to register a
>> callback, which replaces the current deadlock resolver. Upon detection
>> of a deadlock, the callback should get a list of transaction ids that
>> are part of the lock cycle. It's then up to that callback, to chose one
>> and abort that to resolve the conflict.
>>
>> And now, Greg's List:
>>  > 1) What feature does this help add from a user perspective?
>>
>> Preventing cluster-wide deadlocks (while maintaining congruency of
>> replicas).
>>
>>  > 2) Which replication projects would be expected to see an improvement
>>  > from this addition?
>>
>> I suspect all multi-master solutions are affected, certainly Postgres-R
>> would benefit. Single-master ones certainly don't need it.
>>
>>  > 3) What makes it difficult to implement?
>>
>> I don't see any real stumbling block. Deciding on an API needs consensus.
>>
>>  > 4) Are there any other items on the list this depends on, or that it
>>  > is expected to have a significant positive/negative interaction with?
>>
>> Not that I know of.
>>
>>  > 5) What replication projects include a feature like this already, or a
>>  > prototype of a similar one, that might be used as a proof of concept
>>  > or example implementation?
>>
>> Old Postgres-R versions once had such an interception, but it currently
>> lacks a solution for this problem. I don't know of any other project
>> that's already solved this.
>>
>>  > 6) Who is already working on it/planning to work on it/needs it for
>>  > their related project?
>>
>> I'm not currently working on it and don't plan to do so (at least) until
>> PgCon 2010.
>>
>>
>> Cluster hackers, is this a good summary which covers your needs as well?
>> Something missing?
>>
>> Regards
>>
>> Markus Wanner
>>
>> [1]: feature wish list of cluster hackers:
>> http://wiki.postgresql.org/wiki/ClusterFeatures
>>
>>
>
>
> --
> NAGAYASU Satoshi <satoshi(dot)nagayasu(at)gmail(dot)com>
>
> --
> Sent via pgsql-cluster-hackers mailing list
> (pgsql-cluster-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-cluster-hackers
>

I don't think something special/extra required for the global wait-for graph logging,
because each sub-transaction should be processed (and recovered) as a local transaction on the each RM (Resource Manager)
under the distributed transaction environment.

A running transaction is not prepared/commited,
so no more special/extra record is needed when a local transaction is aborted.

-----Original Message-----
From: Koichi Suzuki <koichi(dot)szk(at)gmail(dot)com>
Date: Sun, 7 Feb 2010 01:23:36
To: Satoshi Nagayasu<satoshi(dot)nagayasu(at)gmail(dot)com>
Cc: Markus Wanner<markus(at)bluegap(dot)ch>; <pgsql-cluster-hackers(at)postgresql(dot)org>
Subject: Re: [pgsql-cluster-hackers] Global Deadlock Information

Hi,

I'm very interested in how log it takes to determine the global
deadlock using global wait-for graph and if global deadlock detection
disturb other on-going transactions.

----------
Koichi Suzuki

2010/2/7 Satoshi Nagayasu <satoshi(dot)nagayasu(at)gmail(dot)com>:
> Hi Markus,
>
> I attempted in two ways to resolve global deadlock situation
> in the PostgresForest development.
>
> (1) Use the lock_timeout to avoid from a global deadlock.
>
> The lock_timeout feature is a very simple way to avoid
> from the global deadlock situation.
>
> I disagree "statement_timeout is the way to avoid global
> deadlocks" too, because the statement_timeout kills
> the healthy/long-running transaction by its timeout.
>
> Some developers (including me!) proposed the lock_timeout
> GUC option.
>
> http://archives.postgresql.org/pgsql-hackers/2004-06/msg00935.php
> http://archives.postgresql.org/pgsql-hackers/2010-01/msg01167.php
>
> I still believe the "lock timeout" feature could help
> resolving a global deadlock in the cluster environment.
>
> (2) Use the global wait-for graph to detect a global deadlock.
>
> I had an experimental implemetation to use the global wait-for
> graph to prevent the global deadlock.
>
> http://en.wikipedia.org/wiki/Deadlock#Distributed_deadlock
>
> I used the node(server) identifiers and the pg_locks information
> to build the global wait-for graph, and the kill signal
> (or pg_cancel()?) to abort a victim transaction causing
> the deadlock.
>
> I don't think the callback function is needed to replace
> the current deadlock resolution feature,
> but I agree we need a consensus how we could avoid
> the global deadlock situation in the cluster.
>
> Thanks,
>
> On 2010/02/06 18:13, Markus Wanner wrote:
>>
>> Hi,
>>
>> I'd like to start a thread for discussion of the second item on the
>> ClusterFeatures [1] list: Global Deadlock Information.
>>
>> IIRC there are two aspects to this item: a) the plain notification of a
>> deadlock and b) some way to control or intercept deadlock resolution.
>>
>> The problem this item seems to address is the potential for deadlocks
>> between transactions on different nodes. Or put another way: between a
>> local transaction and one that's to be applied from a remote node (or
>> even between two remote ones - similar issue, though). To ensure
>> congruency between nodes, they must take the same measures to resolve
>> the deadlock, i.e. abort the same transaction(s).
>>
>> I certainly disagree with the statement on the wiki that the
>> "statement_timeout is the way to avoid global deadlocks", because I
>> don't want to have to wait that long until a deadlock gets resolved.
>> Further it doesn't even guarantee congruency, depending on the
>> implementation of your clustering solution.
>>
>> I fail to see how a plain notification API would help much. After all,
>> this could result in one node notifying having aborted transaction A to
>> resolve a deadlock while another node notifies having aborted
>> transaction B. You'd end up having to abort two (or more) transaction
>> instead of just one to resolve a conflict.
>>
>> It could get more useful, if enabling such a notification would turn off
>> the existing deadlock resolver and leave the resolution of the deadlock
>> to the clustering solution. I'd call that an interception.
>>
>> Such an interception API should IMO provide a way to register a
>> callback, which replaces the current deadlock resolver. Upon detection
>> of a deadlock, the callback should get a list of transaction ids that
>> are part of the lock cycle. It's then up to that callback, to chose one
>> and abort that to resolve the conflict.
>>
>> And now, Greg's List:
>> > 1) What feature does this help add from a user perspective?
>>
>> Preventing cluster-wide deadlocks (while maintaining congruency of
>> replicas).
>>
>> > 2) Which replication projects would be expected to see an improvement
>> > from this addition?
>>
>> I suspect all multi-master solutions are affected, certainly Postgres-R
>> would benefit. Single-master ones certainly don't need it.
>>
>> > 3) What makes it difficult to implement?
>>
>> I don't see any real stumbling block. Deciding on an API needs consensus.
>>
>> > 4) Are there any other items on the list this depends on, or that it
>> > is expected to have a significant positive/negative interaction with?
>>
>> Not that I know of.
>>
>> > 5) What replication projects include a feature like this already, or a
>> > prototype of a similar one, that might be used as a proof of concept
>> > or example implementation?
>>
>> Old Postgres-R versions once had such an interception, but it currently
>> lacks a solution for this problem. I don't know of any other project
>> that's already solved this.
>>
>> > 6) Who is already working on it/planning to work on it/needs it for
>> > their related project?
>>
>> I'm not currently working on it and don't plan to do so (at least) until
>> PgCon 2010.
>>
>>
>> Cluster hackers, is this a good summary which covers your needs as well?
>> Something missing?
>>
>> Regards
>>
>> Markus Wanner
>>
>> [1]: feature wish list of cluster hackers:
>> http://wiki.postgresql.org/wiki/ClusterFeatures
>>
>>
>
>
> --
> NAGAYASU Satoshi <satoshi(dot)nagayasu(at)gmail(dot)com>
>
> --
> Sent via pgsql-cluster-hackers mailing list
> (pgsql-cluster-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-cluster-hackers
>

It was just an experimental or the PoC (proof-of-concept) implementation only in a few nodes,
so I don't have any performance evaluation results.

-----Original Message-----
From: Koichi Suzuki <koichi(dot)clarinet(at)gmail(dot)com>
Date: Sun, 7 Feb 2010 02:09:49
To: <satoshi(dot)nagayasu(at)gmail(dot)com>
Cc: Markus Wanner<markus(at)bluegap(dot)ch>; <pgsql-cluster-hackers(at)postgresql(dot)org>
Subject: Re: [pgsql-cluster-hackers] Global Deadlock Information

I mean how long will it take to collect all the wait-for graphs from
all the cluster members. If you have an experience or evaluation,
it's very helpful.

----------
Koichi Suzuki

2010年2月7日1:51 Satoshi Nagayasu <satoshi(dot)nagayasu(at)gmail(dot)com>:
> I don't think something special/extra required for the global wait-for graph logging,
> because each sub-transaction should be processed (and recovered) as a local transaction on the each RM (Resource Manager)
> under the distributed transaction environment.
>
> A running transaction is not prepared/commited,
> so no more special/extra record is needed when a local transaction is aborted.
>
> -----Original Message-----
> From: Koichi Suzuki <koichi(dot)szk(at)gmail(dot)com>
> Date: Sun, 7 Feb 2010 01:23:36
> To: Satoshi Nagayasu<satoshi(dot)nagayasu(at)gmail(dot)com>
> Cc: Markus Wanner<markus(at)bluegap(dot)ch>; <pgsql-cluster-hackers(at)postgresql(dot)org>
> Subject: Re: [pgsql-cluster-hackers] Global Deadlock Information
>
> Hi,
>
> I'm very interested in how log it takes to determine the global
> deadlock using global wait-for graph and if global deadlock detection
> disturb other on-going transactions.
>
> ----------
> Koichi Suzuki
>
>
>
> 2010/2/7 Satoshi Nagayasu <satoshi(dot)nagayasu(at)gmail(dot)com>:
>> Hi Markus,
>>
>> I attempted in two ways to resolve global deadlock situation
>> in the PostgresForest development.
>>
>> (1) Use the lock_timeout to avoid from a global deadlock.
>>
>> The lock_timeout feature is a very simple way to avoid
>> from the global deadlock situation.
>>
>> I disagree "statement_timeout is the way to avoid global
>> deadlocks" too, because the statement_timeout kills
>> the healthy/long-running transaction by its timeout.
>>
>> Some developers (including me!) proposed the lock_timeout
>> GUC option.
>>
>> http://archives.postgresql.org/pgsql-hackers/2004-06/msg00935.php
>> http://archives.postgresql.org/pgsql-hackers/2010-01/msg01167.php
>>
>> I still believe the "lock timeout" feature could help
>> resolving a global deadlock in the cluster environment.
>>
>> (2) Use the global wait-for graph to detect a global deadlock.
>>
>> I had an experimental implemetation to use the global wait-for
>> graph to prevent the global deadlock.
>>
>> http://en.wikipedia.org/wiki/Deadlock#Distributed_deadlock
>>
>> I used the node(server) identifiers and the pg_locks information
>> to build the global wait-for graph, and the kill signal
>> (or pg_cancel()?) to abort a victim transaction causing
>> the deadlock.
>>
>> I don't think the callback function is needed to replace
>> the current deadlock resolution feature,
>> but I agree we need a consensus how we could avoid
>> the global deadlock situation in the cluster.
>>
>> Thanks,
>>
>> On 2010/02/06 18:13, Markus Wanner wrote:
>>>
>>> Hi,
>>>
>>> I'd like to start a thread for discussion of the second item on the
>>> ClusterFeatures [1] list: Global Deadlock Information.
>>>
>>> IIRC there are two aspects to this item: a) the plain notification of a
>>> deadlock and b) some way to control or intercept deadlock resolution.
>>>
>>> The problem this item seems to address is the potential for deadlocks
>>> between transactions on different nodes. Or put another way: between a
>>> local transaction and one that's to be applied from a remote node (or
>>> even between two remote ones - similar issue, though). To ensure
>>> congruency between nodes, they must take the same measures to resolve
>>> the deadlock, i.e. abort the same transaction(s).
>>>
>>> I certainly disagree with the statement on the wiki that the
>>> "statement_timeout is the way to avoid global deadlocks", because I
>>> don't want to have to wait that long until a deadlock gets resolved.
>>> Further it doesn't even guarantee congruency, depending on the
>>> implementation of your clustering solution.
>>>
>>> I fail to see how a plain notification API would help much. After all,
>>> this could result in one node notifying having aborted transaction A to
>>> resolve a deadlock while another node notifies having aborted
>>> transaction B. You'd end up having to abort two (or more) transaction
>>> instead of just one to resolve a conflict.
>>>
>>> It could get more useful, if enabling such a notification would turn off
>>> the existing deadlock resolver and leave the resolution of the deadlock
>>> to the clustering solution. I'd call that an interception.
>>>
>>> Such an interception API should IMO provide a way to register a
>>> callback, which replaces the current deadlock resolver. Upon detection
>>> of a deadlock, the callback should get a list of transaction ids that
>>> are part of the lock cycle. It's then up to that callback, to chose one
>>> and abort that to resolve the conflict.
>>>
>>> And now, Greg's List:
>>> > 1) What feature does this help add from a user perspective?
>>>
>>> Preventing cluster-wide deadlocks (while maintaining congruency of
>>> replicas).
>>>
>>> > 2) Which replication projects would be expected to see an improvement
>>> > from this addition?
>>>
>>> I suspect all multi-master solutions are affected, certainly Postgres-R
>>> would benefit. Single-master ones certainly don't need it.
>>>
>>> > 3) What makes it difficult to implement?
>>>
>>> I don't see any real stumbling block. Deciding on an API needs consensus.
>>>
>>> > 4) Are there any other items on the list this depends on, or that it
>>> > is expected to have a significant positive/negative interaction with?
>>>
>>> Not that I know of.
>>>
>>> > 5) What replication projects include a feature like this already, or a
>>> > prototype of a similar one, that might be used as a proof of concept
>>> > or example implementation?
>>>
>>> Old Postgres-R versions once had such an interception, but it currently
>>> lacks a solution for this problem. I don't know of any other project
>>> that's already solved this.
>>>
>>> > 6) Who is already working on it/planning to work on it/needs it for
>>> > their related project?
>>>
>>> I'm not currently working on it and don't plan to do so (at least) until
>>> PgCon 2010.
>>>
>>>
>>> Cluster hackers, is this a good summary which covers your needs as well?
>>> Something missing?
>>>
>>> Regards
>>>
>>> Markus Wanner
>>>
>>> [1]: feature wish list of cluster hackers:
>>> http://wiki.postgresql.org/wiki/ClusterFeatures
>>>
>>>
>>
>>
>> --
>> NAGAYASU Satoshi <satoshi(dot)nagayasu(at)gmail(dot)com>
>>
>> --
>> Sent via pgsql-cluster-hackers mailing list
>> (pgsql-cluster-hackers(at)postgresql(dot)org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-cluster-hackers
>>
>
>

Hi,

I'm glad you are joining this discussion, thank you.

Satoshi Nagayasu wrote:
> Some developers (including me!) proposed the lock_timeout
> GUC option.
>
> http://archives.postgresql.org/pgsql-hackers/2004-06/msg00935.php
> http://archives.postgresql.org/pgsql-hackers/2010-01/msg01167.php

Thanks for these pointers.

> I still believe the "lock timeout" feature could help
> resolving a global deadlock in the cluster environment.

Well, you'd always need to find a compromise between waiting long enough
to not kill transactions just because of high contention, but still
react promptly enough to to resolve real deadlocks. I'd like to avoid
such nifty configuration and tuning settings.

> (2) Use the global wait-for graph to detect a global deadlock.

Can you please elaborate on the replication solution that needs such a
global wait-for graph? Why do you need a global graph, if you replicate
all of your transaction anyway? Does that global graph imply a global
abort decision as well?

IMO a local wait-for graph is absolutely sufficient. The problem is just
that different nodes might reach different decisions on how to resolve
the deadlock. But if you replicate to all nodes, they will all be able
to "see" the deadlock, no?

> http://en.wikipedia.org/wiki/Deadlock#Distributed_deadlock

That very article states:

"In a Commitment ordering based distributed environment (including the
Strong strict two-phase locking (SS2PL, or rigorous) special case)
distributed deadlocks are resolved automatically by the atomic
commitment protocol (e.g. two-phase commit (2PC)), and no global
wait-for graph or other resolution mechanism are needed."

And the issue with "phantom deadlocks" doesn't really excite me either,
so I'd rather like not having to deal with such things.

> I don't think the callback function is needed to replace
> the current deadlock resolution feature,

Obviously this wish list item needs more discussion. It seems we want
two rather different things, then.

How does your replication solution cope with the current deadlock
resolver? How do you prevent it aborting

> but I agree we need a consensus how we could avoid
> the global deadlock situation in the cluster.

How do you get to the situation where you have a global deadlock, but
not a local one? That seems to imply that you are not replicating locks
to all nodes.

How do you think Postgres core could help with determining such global
deadlocks? That seems more like a solution-specific thing to me.

Are we even talking about the same level of locking, namely regular,
heavy-weight locks (as per the storage/lmgr/README)?

Kind Regards

Markus Wanner

> > (2) Use the global wait-for graph to detect a global deadlock.
>
> Can you please elaborate on the replication solution that needs such a
> global wait-for graph? Why do you need a global graph, if you replicate
> all of your transaction anyway? Does that global graph imply a global
> abort decision as well?
>
> IMO a local wait-for graph is absolutely sufficient. The problem is just
> that different nodes might reach different decisions on how to resolve
> the deadlock. But if you replicate to all nodes, they will all be able
> to "see" the deadlock, no?
>
> > http://en.wikipedia.org/wiki/Deadlock#Distributed_deadlock
>
> That very article states:
>
> "In a Commitment ordering based distributed environment (including the
> Strong strict two-phase locking (SS2PL, or rigorous) special case)
> distributed deadlocks are resolved automatically by the atomic
> commitment protocol (e.g. two-phase commit (2PC)), and no global
> wait-for graph or other resolution mechanism are needed."
>
> And the issue with "phantom deadlocks" doesn't really excite me either,
> so I'd rather like not having to deal with such things.

I'm not sure if I understand the concept of CO at all, but for me it
seems sometimes transaction control which does not use CO is
usefull. For example, we could send SELECT queries to each DB odes
without any consideration of CO. This will achieve higher concurrent
processing with a price of occasional "global dealock", still this
will be better solution in mostly-readonly-transaction environment.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

> > I don't think the callback function is needed to replace
> > the current deadlock resolution feature,
>
> Obviously this wish list item needs more discussion. It seems we want
> two rather different things, then.
>
> How does your replication solution cope with the current deadlock
> resolver? How do you prevent it aborting
>
> > but I agree we need a consensus how we could avoid
> > the global deadlock situation in the cluster.
>
> How do you get to the situation where you have a global deadlock, but
> not a local one? That seems to imply that you are not replicating locks
> to all nodes.
>
> How do you think Postgres core could help with determining such global
> deadlocks? That seems more like a solution-specific thing to me.
>
> Are we even talking about the same level of locking, namely regular,
> heavy-weight locks (as per the storage/lmgr/README)?
>
> Kind Regards
>
> Markus Wanner
>
>
> --
> Sent via pgsql-cluster-hackers mailing list (pgsql-cluster-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-cluster-hackers

This discussion wrapped up with enough detail for me to be happy I
understood the basics.
http://wiki.postgresql.org/wiki/ClusterFeatures#Global_deadlock_information
has the summary I wrote of the discussion here.

--
Greg Smith 2ndQuadrant Baltimore, MD
PostgreSQL Training, Services and Support
greg(at)2ndQuadrant(dot)com www.2ndQuadrant.com

Hi,

I think we are talking across each other here. I'm having Postgres-R in
mind, while I'm not sure what kind of clustering solution you are
talking about.

Tatsuo Ishii wrote:
> I'm not sure if I understand the concept of CO at all, but for me it
> seems sometimes transaction control which does not use CO is
> usefull.

Well, it depends a bit on what they mean by commit ordering. For
Postgres-R, an atomic broadcast protocol takes care of "ordering"
commits. I guess that counts as a commit ordering protocol.

> For example, we could send SELECT queries to each DB odes
> without any consideration of CO.

SELECTs are mostly read-only, so I don't quite understand what those
have to do with the order of commits.

> This will achieve higher concurrent
> processing with a price of occasional "global dealock", still this
> will be better solution in mostly-readonly-transaction environment.

Please elaborate on what kind of replication solution you have in mind
and how that generates global deadlocks.

In Postgres-R, only writing transactions need to be replicated. So only
their commits need ordering. All possibly global deadlocks will turn
into local deadlocks, so I'm only concerned with consistent resolution
of multiple local deadlocks.

I'd appreciate to hear your needs. Very possibly, there still is a huge
amount of overlap.

Regards

Markus Wanner