Re: deadlock_timeout at < PGC_SIGHUP?

On Tue, Mar 29, 2011 at 1:38 AM, Noah Misch <noah(at)leadboat(dot)com> wrote:
> A few years ago, this list had a brief conversation on $SUBJECT:
> http://archives.postgresql.org/message-id/1215443493.4051.600.camel@ebony.site
>
> What is notable/surprising about the behavior when two backends have different
> values for deadlock_timeout?  After sleeping to acquire a lock, each backend
> will scan for deadlocks every time its own deadlock_timeout elapses.  Some might
> be surprised that a larger-deadlock_timeout backend can still be the one to give
> up; consider this timeline:
>
> Backend Time    Command
> A       N/A     SET deadlock_timeout = 1000
> B       N/A     SET deadlock_timeout = 100
> A       0       LOCK t
> B       50      LOCK u
> A       100     LOCK u
> B       1050    LOCK t
> (Backend A gets an ERROR at time 1100)
>
> More generally, one cannot choose deadlock_timeout values for two sessions such
> that a specific session will _always_ get the ERROR.  However, one can drive the
> probability rather high.  Compare to our current lack of control.
> Is some other behavior that only arises when backends have different
> deadlock_timeout settings more surprising than that one?
>
> If we could relax deadlock_timeout to a GucContext below PGC_SIGHUP, it would
> probably need to stop at PGC_SUSET for now.  Otherwise, an unprivileged user
> could increase deadlock_timeout to hide his lock waits from log_lock_waits.  One
> could remove that limitation by introducing a separate log_lock_waits timeout,
> but that patch would be significantly meatier.  Some might also object to
> PGC_USERSET on the basis that a user could unfairly preserve his transaction by
> setting a high deadlock_timeout.  However, that user could accomplish a similar
> denial of service by idly holding locks or trying deadlock-prone lock
> acquisitions in subtransactions.

I'd be inclined to think that PGC_SUSET is plenty. It's actually not
clear to me what the user could usefully do other than trying to
preserve his transaction by setting a high deadlock_timeout - what is
the use case, other than that?

Is it worth thinking about having an explicit setting for deadlock
priority? That'd be more work, of course, and I'm not sure it it's
worth it, but it'd also provide stronger guarantees than you can get
with this proposal.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company