timeout on lock feature

I can imagine some people wanting this. However, 7.1 has new deadlock
detection code, so I would you make a 7.1 version and send it over. We
can get it into 7.2. I think we need a SET variable, and it should
default to OFF.

Good idea. Thanks.

Hi,

I implement additional server functionality. Currently (v7.0.3), executing
SQL update statement on the same
row from inside two different processess results in blocking second process
to the end of transaction in
the first one. In real OLTP application second process can't wait too long.
After few seconds server should
return to the application message:'lock timeout exceeded'. I modify postgres
lock manager source code to
obtain that functionality. I take advantage of deadlock detection mechanism.
Currently deadlock
detection routine initialy check for simple deadlock detection between two
processess, next insert lock
into lock queue and after DEADLOCK_CHECK_TIMER seconds run HandleDeadLock to
comprehensive deadlock detection.
To obtain 'timeout on lock' feature I do as follow:

1. Add new configure parameter. Currently I add #define statement in file
include/config.in
#define NO_WAIT_FOR_LOCK 1
In the future somebody can add new option to SQL SET command

2. Modify HandleDeadLock routine. In file backend/storage/lmgr/proc.c change
lines 866-870

if (!DeadLockCheck(MyProc, MyProc->waitLock))
{
UnlockLockTable();
return;
}

to

if (!NO_WAIT_FOR_LOCK)
{
if (!DeadLockCheck(MyProc, MyProc->waitLock))
{
UnlockLockTable();
return;
}
}

With this modyfication every conflicting lock wait DEADLOCK_CHECK_TIMER
seconds in queue and returns with error
'deadlock detect'.

Who can add this simply 'timeout on lock' implementation to the next
postgres server release?

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

If you can't handle the SET variable stuff, we can do it over here.

Thanks.

Hi,

I implement additional server functionality. Currently (v7.0.3), executing
SQL update statement on the same
row from inside two different processess results in blocking second process
to the end of transaction in
the first one. In real OLTP application second process can't wait too long.
After few seconds server should
return to the application message:'lock timeout exceeded'. I modify postgres
lock manager source code to
obtain that functionality. I take advantage of deadlock detection mechanism.
Currently deadlock
detection routine initialy check for simple deadlock detection between two
processess, next insert lock
into lock queue and after DEADLOCK_CHECK_TIMER seconds run HandleDeadLock to
comprehensive deadlock detection.
To obtain 'timeout on lock' feature I do as follow:

1. Add new configure parameter. Currently I add #define statement in file
include/config.in
#define NO_WAIT_FOR_LOCK 1
In the future somebody can add new option to SQL SET command

2. Modify HandleDeadLock routine. In file backend/storage/lmgr/proc.c change
lines 866-870

if (!DeadLockCheck(MyProc, MyProc->waitLock))
{
UnlockLockTable();
return;
}

to

if (!NO_WAIT_FOR_LOCK)
{
if (!DeadLockCheck(MyProc, MyProc->waitLock))
{
UnlockLockTable();
return;
}
}

With this modyfication every conflicting lock wait DEADLOCK_CHECK_TIMER
seconds in queue and returns with error
'deadlock detect'.

Who can add this simply 'timeout on lock' implementation to the next
postgres server release?

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Bruce Momjian <pgman@candle.pha.pa.us> writes:

I can imagine some people wanting this. However, 7.1 has new deadlock
detection code, so I would you make a 7.1 version and send it over. We
can get it into 7.2.

I object strongly to any such "feature" in the low-level form that
Henryk proposes, because it would affect *ALL* locking. Do you really
want all your other transactions to go belly-up if, say, someone vacuums
pg_class?

A variant of LOCK TABLE that explicitly requests a timeout might make
sense, though.

regards, tom lane

I was thinking SET because UPDATE does an auto-lock.

Bruce Momjian <pgman@candle.pha.pa.us> writes:

I can imagine some people wanting this. However, 7.1 has new deadlock
detection code, so I would you make a 7.1 version and send it over. We
can get it into 7.2.

I object strongly to any such "feature" in the low-level form that
Henryk proposes, because it would affect *ALL* locking. Do you really
want all your other transactions to go belly-up if, say, someone vacuums
pg_class?

A variant of LOCK TABLE that explicitly requests a timeout might make
sense, though.

regards, tom lane

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Bruce Momjian <pgman@candle.pha.pa.us> writes:

I was thinking SET because UPDATE does an auto-lock.

Not to mention a ton of implicit locks acquired on various system tables
during parsing/planning. You really want auto timeout on all of those?
I sure don't.

The appropriate way to do this given a LOCK TABLE option would be like

BEGIN;
LOCK TABLE foo IN ROW EXCLUSIVE MODE WITH TIMEOUT n;
UPDATE foo SET ...;
COMMIT;

which restricts the scope of the timeout behavior to just the specific
lock that the user is thinking of, and doesn't risk breaking fundamental
system operations.

regards, tom lane

The appropriate way to do this given a LOCK TABLE option would be like

BEGIN;
LOCK TABLE foo IN ROW EXCLUSIVE MODE WITH TIMEOUT n;
UPDATE foo SET ...;
COMMIT;

which restricts the scope of the timeout behavior to just the specific
lock that the user is thinking of, and doesn't risk breaking fundamental
system operations.

This is pretty tough because the user has to know the proper lock type,
right?

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

I was thinking SET because UPDATE does an auto-lock.

Optimal would imho be a SET that gives a maximum amount of time in seconds
the client is willing to wait for any lock. But I liked the efficiency of Henryk's code.

Bruce Momjian <pgman@candle.pha.pa.us> writes:

I can imagine some people wanting this. However, 7.1 has new deadlock
detection code, so I would you make a 7.1 version and send it over. We
can get it into 7.2.

I object strongly to any such "feature" in the low-level form that
Henryk proposes, because it would affect *ALL* locking. Do you really
want all your other transactions to go belly-up if, say, someone vacuums
pg_class?

Yes, if a non batch client already blocked for over x seconds. Of course a more
sophisticated client can send querycancel() but that involves a more complicated
program (threads, timer ...).

A variant of LOCK TABLE that explicitly requests a timeout might make
sense, though.

I do not think that a solution for one particular lock is very helpful. If your dml then
blocks on some unforseen lock (parse, plan ...) , the client is in exactly the situation
it tried to avoid in the first place.

Andreas

YES, this feature should affect ALL locks.
'Timeout on lock' parameter says to server "I CAN'T WAIT WITH THIS
TRANSACTION TOO LONG BECAUSE OF (ANY) LOCK",
so if my process is in conflict with another (system or user) process, then
i want to abort
my transaction. Somebody can set timeout to bigger value (minutes, for
example). In my OLTP applications
i set this value to 10 sec. because i have a lot of short transactions
generated by operators.
LOCK TABLE is deficient because i need not wait also on some technical locks
(if this locks blocks me too long!).

Tom Lane wrote in message <4547.987180295@sss.pgh.pa.us>...

Tom Lane wrote in message <4982.987184866@sss.pgh.pa.us>...

Bruce Momjian <pgman@candle.pha.pa.us> writes:

I was thinking SET because UPDATE does an auto-lock.

Not to mention a ton of implicit locks acquired on various system tables
during parsing/planning. You really want auto timeout on all of those?
I sure don't.

*****************************************
YES, I DO! My transaction can't wait.
If parser on planner is blocked, then i want to abort my transaction.
*****************************************

The appropriate way to do this given a LOCK TABLE option would be like

BEGIN;
LOCK TABLE foo IN ROW EXCLUSIVE MODE WITH TIMEOUT n;
UPDATE foo SET ...;
COMMIT;

*****************************************
With this solution, some server processes can block me!
*****************************************

which restricts the scope of the timeout behavior to just the specific
lock that the user is thinking of, and doesn't risk breaking fundamental
system operations.

regards, tom lane

*****************************************
This is real problem, but i think other postgres modules are ready for my
solution
(because it is extension to deadlock detection mechanism)
*****************************************

Added to TODO:

* Add SET parameter to timeout if waiting for lock too long

I was thinking SET because UPDATE does an auto-lock.

Optimal would imho be a SET that gives a maximum amount of time in seconds
the client is willing to wait for any lock. But I liked the efficiency of Henryk's code.

Bruce Momjian <pgman@candle.pha.pa.us> writes:

I can imagine some people wanting this. However, 7.1 has new deadlock
detection code, so I would you make a 7.1 version and send it over. We
can get it into 7.2.

I object strongly to any such "feature" in the low-level form that
Henryk proposes, because it would affect *ALL* locking. Do you really
want all your other transactions to go belly-up if, say, someone vacuums
pg_class?

Yes, if a non batch client already blocked for over x seconds. Of course a more
sophisticated client can send querycancel() but that involves a more complicated
program (threads, timer ...).

A variant of LOCK TABLE that explicitly requests a timeout might make
sense, though.

I do not think that a solution for one particular lock is very helpful. If your dml then
blocks on some unforseen lock (parse, plan ...) , the client is in exactly the situation
it tried to avoid in the first place.

Andreas

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Bruce Momjian <pgman@candle.pha.pa.us> writes:

Added to TODO:
* Add SET parameter to timeout if waiting for lock too long

I repeat my strong objection to any global (ie, affecting all locks)
timeout. Such a "feature" will have unpleasant consequences.

regards, tom lane

Bruce Momjian <pgman@candle.pha.pa.us> writes:

I envisioned:

SET TIMEOUT TO 10;
UPDATE tab SET col = 3;
RESET TIMEOUT

Can't we get that work work properly? Let the timeout only apply to the
'tab' table and none of the others.

As Henryk has implemented it, it WON'T only apply to the 'tab' table;
it'll affect all locks grabbed anywhere, including those that the system
locks internally. That scares the heck out of me, Andreas' objections
notwithstanding.

Can't we exclude system tables from being affected by the timeout?

How will you do that? The lock manager makes a point of not knowing the
semantics associated with any particular lock tag. It's even less
likely to know the difference between a "system" grab and a "user" grab
on what might be the very same lock (consider an "UPDATE pg_class"
command).

Requiring a LOCK statement that matches
the UPDATE/DELETE and wrapping the whole thing in a transaction seems
needlessly complex to me.

As opposed to your three-step proposal above? That doesn't look
very much simpler to me...

regards, tom lane

Bruce Momjian <pgman@candle.pha.pa.us> writes:

Added to TODO:
* Add SET parameter to timeout if waiting for lock too long

I repeat my strong objection to any global (ie, affecting all locks)
timeout. Such a "feature" will have unpleasant consequences.

I envisioned:

SET TIMEOUT TO 10;
UPDATE tab SET col = 3;
RESET TIMEOUT

Can't we get that work work properly? Let the timeout only apply to the
'tab' table and none of the others. Can't we exclude system tables from
being affected by the timeout?

Why exactly would you be willing to wait longer for an implicit system table lock?
If this was the case you should also be willing to wait for the row lock, no ?
The timeout will be useful to let the client or user decide on an alternate course
of action other that killing his application (without the need for timers or threads in
the client program).

Requiring a LOCK statement that matches
the UPDATE/DELETE and wrapping the whole thing in a transaction seems
needlessly complex to me.

Agreed.

Andreas

I envisioned:

SET TIMEOUT TO 10;
UPDATE tab SET col = 3;
RESET TIMEOUT

Can't we get that work work properly? Let the timeout only

apply to the

'tab' table and none of the others.

As Henryk has implemented it, it WON'T only apply to the 'tab' table;
it'll affect all locks grabbed anywhere, including those that the system
locks internally. That scares the heck out of me, Andreas' objections
notwithstanding.

What exactly scares you ? Surely the deadlock resolution should
handle the above decision to ABORT in the same way it currently does.
If not we have something to fix, no?

Of course this might rather be something to consider for 7.2 and not 7.1.1.

Andreas

Added to TODO:
* Add SET parameter to timeout if waiting for lock too long

I repeat my strong objection to any global (ie, affecting all locks)
timeout. Such a "feature" will have unpleasant consequences.

Except that other people like myself, see those consequences
as a pleasant thing :-) And we are talking about something that has to be
requested by the client explicitly (at least in a default installation).

It simply does make sense for an interactive client to not block
more than ~ 30 seconds.

Andreas

Zeugswetter Andreas SB <ZeugswetterA@wien.spardat.at> writes:

The timeout will be useful to let the client or user decide on an
alternate course of action other that killing his application (without
the need for timers or threads in the client program).

This assumes (without evidence) that the client has a good idea of what
the timeout limit ought to be. I think this "feature" has no real use
other than encouraging application programmers to shoot themselves in
the foot. I see no reason that we should make it easy to misdesign
applications.

regards, tom lane

The timeout will be useful to let the client or user decide on an
alternate course of action other that killing his application (without
the need for timers or threads in the client program).

This assumes (without evidence) that the client has a good idea of what
the timeout limit ought to be.

Yes, the application programmer would need to know how long his users
are willing to wait before they start to "hammer at their monitors, kick their pc's
or throw the mouse across the room :-O". It must be made clear that it is very counter
productive to use too short timeouts.

Andreas

Michael Ansley <Michael.Ansley@intec-telecom-systems.com> writes:

Sorry for my forgetfulness (and a search through geocrawler didn't turn up
anything useful), but what was the problem with something like NOWAIT?
e.g.: SELECT * FROM a FOR UPDATE NOWAIT;
where, if the required lock could not be obtained immediately, this
statement would raise an error.

I have no objection to that ... it does not cover anything except FOR
UPDATE, though, which is probably less general than some of the other
folks want.

regards, tom lane

Sorry for my forgetfulness (and a search through geocrawler didn't turn up
anything useful), but what was the problem with something like NOWAIT?

e.g.: SELECT * FROM a FOR UPDATE NOWAIT;

where, if the required lock could not be obtained immediately, this
statement would raise an error.

Cheers...

MikeA

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: 17 April 2001 15:49
To: Bruce Momjian
Cc: Zeugswetter Andreas SB; Henryk Szal; pgsql-hackers@postgresql.org
Subject: Re: AW: [HACKERS] timeout on lock feature

Bruce Momjian <pgman@candle.pha.pa.us> writes:

Added to TODO:
* Add SET parameter to timeout if waiting for lock too long

I repeat my strong objection to any global (ie, affecting all locks)
timeout. Such a "feature" will have unpleasant consequences.

regards, tom lane

---------------------------(end of
broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

_________________________________________________________________________
This e-mail and any attachments are confidential and may also be privileged and/or copyright
material of Intec Telecom Systems PLC (or its affiliated companies). If you are not an
intended or authorised recipient of this e-mail or have received it in error, please delete
it immediately and notify the sender by e-mail. In such a case, reading, reproducing,
printing or further dissemination of this e-mail is strictly prohibited and may be unlawful.
Intec Telecom Systems PLC. does not represent or warrant that an attachment hereto is free
from computer viruses or other defects. The opinions expressed in this e-mail and any
attachments may be those of the author and are not necessarily those of Intec Telecom
Systems PLC.

This footnote also confirms that this email message has been swept by
MIMEsweeper for the presence of computer viruses.
__________________________________________________________________________

Zeugswetter Andreas SB <ZeugswetterA@wien.spardat.at> writes:

The timeout will be useful to let the client or user decide on an
alternate course of action other that killing his application (without
the need for timers or threads in the client program).

Okay, let's take a close look at this assumption.

1. Why is 10 seconds (or 1, or 30) a magic number? If you've waited
that long, why wouldn't you be willing to wait a little longer? How
will you know what value to pick?

2. If you do want a timeout to support an interactive application, seems
to me that you want to specify it as a total time for the whole query,
not the maximum delay to acquire any individual lock. Under normal
circumstances lock delays are likely to be just a small part of total
query time.

3. Since we already have deadlock detection, there is no need for
timeouts as a defense against deadlock. A timeout would only be useful
to defend against other client applications that are sitting idle or
executing long-running operations while holding locks that conflict
with your real-time query. This scenario strikes me as a flaw in the
overall application design, which should be fixed by fixing those other
clients and/or the lock usage. A lock timeout is just a bandaid
to cope (poorly) with broken application design.

4. The correct way to deal with overly-long queries in an interactive
application is to let the user interactively cancel queries (which we
already support). This is much better than any application-specified
fixed timeout, because the application is unlikely to be aware of
extenuating circumstances --- say, the system is heavily overloaded at
the moment because of lots of activity. I can think of few things more
annoying than an application-set timeout that kills my unfinished query
whenever the system is under load.

In short, I think lock timeout is a solution searching in vain for a
problem. If we implement it, we are just encouraging bad application
design.

regards, tom lane

Added to TODO:
* Add SET parameter to timeout if waiting for lock too long

I repeat my strong objection to any global (ie, affecting all locks)
timeout. Such a "feature" will have unpleasant consequences.

But LOCK TABLE T IN ROW EXCLUSIVE MODE WITH TIMEOUT X will not give
required results not only due to parser/planner locks - what if
UPDATE T will have to wait for other transactions commit/abort
(same row update)? Lock on pseudo-table is acquired in this case...

Vadim

The timeout will be useful to let the client or user decide
on an alternate course of action other that killing his
application (without the need for timers or threads in the
client program).

This assumes (without evidence) that the client has a good
idea of what the timeout limit ought to be. I think this "feature"
has no real use other than encouraging application programmers to
shoot themselves in the foot. I see no reason that we should make
it easy to misdesign applications.

AFAIR, Big Boys have this feature. If its implementation is safe,
ie will not affect applications not using it, why do not implement it?

Vadim

On Tue, Apr 17, 2001 at 12:56:11PM -0400, Tom Lane wrote:

In short, I think lock timeout is a solution searching in vain for a
problem. If we implement it, we are just encouraging bad application
design.

I agree with Tom completely here.

In any real-world application the database is the key component of a
larger system: the work it does is the most finicky, and any mistakes
(either internally or, more commonly, from misuse) have the most
far-reaching consequences. The responsibility of the database is to
provide a reliable and easily described and understood mechanism to
build on.

Timeouts are a system-level mechanism that to be useful must refer to
system-level events that are far above anything that PG knows about.
The only way PG could apply reasonable timeouts would be for the
application to dictate them, but the application can better implement
them itself.

You can think of this as another aspect of the "end-to-end" principle:
any system-level construct duplicated in a lower-level system component
can only improve efficiency, not provide the corresponding high-level
service. If we have timeouts in the database, they should be there to
enable the database to better implement its abstraction, and not pretend
to be a substitute for system-level timeouts.

There's no upper limit on how complicated a database interface can
become (cf. Oracle). The database serves its users best by having
the simplest interface that can possibly provide the needed service.

Nathan Myers
ncm@zembu.com

"Henryk Szal" <szal@doctorq.com.pl> writes:

YES, this feature should affect ALL locks.
'Timeout on lock' parameter says to server "I CAN'T WAIT WITH THIS
TRANSACTION TOO LONG BECAUSE OF (ANY) LOCK",

It still seems to me that what such an application wants is not a lock
timeout at all, but an overall limit on the total elapsed time for the
query. If you can't afford to wait to get a lock, why is it OK to wait
(perhaps much longer) for I/O or computation?

Such a limit would be best handled by sending a query-cancel request
when you run out of patience, it seems to me.

regards, tom lane

"Henryk Szal" <szal@doctorq.com.pl> writes:

YES, I DO! My transaction can't wait.
If parser on planner is blocked, then i want to abort my transaction.

What are your actual timing constraints? Is the constraint ``no
database table access may take longer than 10 seconds?'' Or is it
``no database transaction may take longer than 10 seconds?'' Or is
the constraint ``this operation may not take longer than 10 seconds?''

If the first is the actual constraint, then indeed a timeout on table
access is appropriate. But that would be a weird constraint. Can you
explain further why you need this?

If the second is the actual constraint, that also sounds strange; a
database transaction is not normally a complete transaction. You
usually have to worry about other communication overhead.

If the third is the actual constraint, then shouldn't you do the
timeout at the operation level, rather than at the database level?
What is preventing you from doing that?

Ian

---------------------------(end of broadcast)---------------------------
TIP 3988: A computer scientist is someone who fixes things that aren't broken.

Timeouts are a system-level mechanism that to be useful must refer to
system-level events that are far above anything that PG knows about.
The only way PG could apply reasonable timeouts would be for the
application to dictate them, but the application can better implement
them itself.

OK we have the following scenario

Session A Session B

begin begin

insert -- on unique constraint

insert -- on same unique constraint

-- Session A becomes idle

: -- Session B becomes ...

or we have (Informix Online)

Session A Session B

set lock mode to wait [seconds] set lock mode to wait [seconds]

begin begin

insert -- on unique constraint

insert -- on same unique constraint

* resource not available error *

-- Session B carries on

Oracle 7 (OCI) has oopt() call to set wait options for requested
resources. Oracle 8 OCI has the same behaviour as PG ie. oopt()
is no longer available.

I believe that the ability to switch the database to either not wait
for resources, or wait a specified period or wait forever
(default) is essential especially for interactive applications.

Regards
Theo

In short, I think lock timeout is a solution searching in vain for a
problem. If we implement it, we are just encouraging bad > application
design.

I agree with Tom completely here.

In any real-world application the database is the key component of a
larger system: the work it does is the most finicky, and any mistakes
(either internally or, more commonly, from misuse) have the most
far-reaching consequences. The responsibility of the database is to
provide a reliable and easily described and understood mechanism to
build on.

It is not something that makes anything unrelyable or less robust.
It is also simple: "I (the client) request that you (the backend) dont wait for
any lock longer than x seconds"

Timeouts are a system-level mechanism that to be useful must refer to
system-level events that are far above anything that PG knows about.

I think you are talking about different kinds of timeouts here.

The only way PG could apply reasonable timeouts would be for the
application to dictate them,

That is exactly what we are talking about here.

but the application can better implement them itself.

It can, but it makes the program more complicated (needs timers or threads,
which violates your last statement "simplest interface".

You can think of this as another aspect of the "end-to-end" principle:
any system-level construct duplicated in a lower-level system component
can only improve efficiency, not provide the corresponding high-level
service. If we have timeouts in the database, they should be there to
enable the database to better implement its abstraction, and not pretend
to be a substitute for system-level timeouts.

Mentioned functionality has nothing to do with above statement which
I can fully support.

There's no upper limit on how complicated a database interface can
become (cf. Oracle). The database serves its users best by having
the simplest interface that can possibly provide the needed service.

Agreed.

Andreas

"Henryk Szal" <szal@doctorq.com.pl> writes:

YES, this feature should affect ALL locks.
'Timeout on lock' parameter says to server "I CAN'T WAIT WITH THIS
TRANSACTION TOO LONG BECAUSE OF (ANY) LOCK",

It still seems to me that what such an application wants is not a lock
timeout at all, but an overall limit on the total elapsed time for the
query. If you can't afford to wait to get a lock, why is it OK to wait
(perhaps much longer) for I/O or computation?

Yes, that is a valid argument. The only thing I can counter is that (in OLTP)
it is usually easy to predict the amount of work that needs to be done
for your own tx (we are typically talking about 1 - 200 ms here), but it is not easy
to predict how long another session needs to complete it's transaction
(the other session might be OLAP, vacuum ...).

Andreas

Hi,

for 10 years i develop DB application using 'timeout on lock' feature
(Informix,Ingres,AdabasD,RDB,...).
I think about migrate with this application to postgresql, and with this
feature i don't need to modify my ready to run code specially for
postgresql. This feature guard me against blocking terminals, because long
query
initialized by operator (or administrator).

"Mikheev, Vadim" wrote in message
<8F4C99C66D04D4118F580090272A7A234D33AC@sectorbase1.sectorbase.com>...

This option will be OPTIONAL.

Tom Lane wrote in message <23613.987518927@sss.pgh.pa.us>...

My typical short transaction run in 3 seconds (on heavy loaded system 30
sec.). But without 'timeout
on lock' it can run 60-180 minutes because someone (user or administrator)
run long transaction.
Timeout value is negligible. I set one to 10 sec. because if my two (3 sec.)
transaction are in conflict, then
both will be executed (second 3 sec. later).

Ian Lance Taylor wrote in message ...

"Henryk Szal" <szal@doctorq.com.pl> writes:

YES, I DO! My transaction can't wait.
If parser on planner is blocked, then i want to abort my transaction.

What are your actual timing constraints? Is the constraint ``no
database table access may take longer than 10 seconds?'' Or is it
``no database transaction may take longer than 10 seconds?'' Or is
the constraint ``this operation may not take longer than 10 seconds?''

If the first is the actual constraint, then indeed a timeout on table
access is appropriate. But that would be a weird constraint. Can you
explain further why you need this?

If the second is the actual constraint, that also sounds strange; a
database transaction is not normally a complete transaction. You
usually have to worry about other communication overhead.

If the third is the actual constraint, then shouldn't you do the
timeout at the operation level, rather than at the database level?
What is preventing you from doing that?

Ian

---------------------------(end of broadcast)---------------------------
TIP 3988: A computer scientist is someone who fixes things that aren't

broken.

Zeugswetter Andreas SB <ZeugswetterA@wien.spardat.at> writes:

It is not something that makes anything unrelyable or less robust.

How can you argue that? The presence of a lock timeout *will* make
operations fail that otherwise would have succeeded; moreover that
failure will be pretty unpredictable (at least from the point of view
of the application that issued the command). That qualifies as
"unreliable and not robust" in my book. A persistent SET variable
also opens up the risk of completely unwanted failures in critical
operations --- all you have to do is forget to reset the variable
when the effect is no longer wanted. (Murphy's Law guarantees that
you won't find out such a mistake until the worst possible time.
That's even less robust.)

The only way PG could apply reasonable timeouts would be for the
application to dictate them,

That is exactly what we are talking about here.

The *real* problem is that the application cannot determine reasonable
timeouts either. Perhaps the app can decide how long it is willing to
wait overall, but how can it translate that into the low-level notion of
an appropriate lock timeout? It does not know how many locks might get
locked in the course of a query, nor which locks they are exactly, nor
what the likely distribution of wait intervals is for those locks.

Given that, using a lock timeout "feature" is just a crapshoot. If you
say "set lock timeout X", you have no real idea how that translates to
application-visible performance nor how big a risk you are taking of
inducing unwanted failures. You don't even get to average out the
uncertainty across a whole query, because if any one of the lock waits
exceeds X, your query blows up. Yet making X large destroys the
usefulness of the feature entirely, so there will always be a strong
temptation to set it too low.

This is the real reason why I've been holding out for restricting the
feature to a specific LOCK TABLE statement: if it's designed that way,
at least you know which lock you are applying the timeout to, and have
some chance of being able to estimate an appropriate timeout.

regards, tom lane

It is not something that makes anything unrelyable or less robust.

How can you argue that? The presence of a lock timeout *will* make
operations fail that otherwise would have succeeded; moreover that
failure will be pretty unpredictable (at least from the point of view
of the application that issued the command). That qualifies as
"unreliable and not robust" in my book.
A persistent SET variable
also opens up the risk of completely unwanted failures in critical
operations --- all you have to do is forget to reset the variable

?????? So what, when you e.g. forget to commit you are also in trouble,
I do not see anything special here.

when the effect is no longer wanted. (Murphy's Law guarantees that
you won't find out such a mistake until the worst possible time.
That's even less robust.)

My OLTP clients set it to 30 sec right after connect and leave it at that.

The only way PG could apply reasonable timeouts would be for the
application to dictate them,

That is exactly what we are talking about here.

The *real* problem is that the application cannot determine reasonable
timeouts either. Perhaps the app can decide how long it is willing to
wait overall,

Yes, that is it. As I tried to explain earlier, the amount of work that needs to be
done for the own tx (in OLTP) is pretty well predictable, but the work of other
clients is not.

but how can it translate that into the low-level notion of
an appropriate lock timeout? It does not know how many locks might get
locked in the course of a query, nor which locks they are exactly, nor
what the likely distribution of wait intervals is for those locks.

The above would imho be a wrong approach at determining the timeout.

Given that, using a lock timeout "feature" is just a crapshoot. If you
say "set lock timeout X", you have no real idea how that translates to
application-visible performance nor how big a risk you are taking of
inducing unwanted failures. You don't even get to average out the
uncertainty across a whole query, because if any one of the lock waits
exceeds X, your query blows up. Yet making X large destroys the
usefulness of the feature entirely, so there will always be a strong
temptation to set it too low.

This is the real reason why I've been holding out for restricting the
feature to a specific LOCK TABLE statement: if it's designed that way,
at least you know which lock you are applying the timeout to, and have
some chance of being able to estimate an appropriate timeout.

I do not agree, but such is life :-)

BTW: for distributed txns you need a lock timeout feature anyway, because
detecting remote deadlocks between two or more different servers would be
very complicated. And I do think PostgreSQL will need remote db access a la long.

Andreas

"A Z" == Zeugswetter Andreas SB <ZeugswetterA@wien.spardat.at> writes:

PS: where can I find more on the distributed txn plans for PostgreSQL? Thanks.

A Z> BTW: for distributed txns you need a lock timeout feature
A Z> anyway, because detecting remote deadlocks between two or
A Z> more different servers would be very complicated. And I do
A Z> think PostgreSQL will need remote db access a la long.

A typical distributed transaction management system would consist of

- a transaction manager for that particular transaction
- an associated log manager
- an optional associated lock manager (depending on transaction type:
pessimistic, optimistic, etc)
- one or more resource managers (usually storage)

and thus the lock manager would have a single point of reference for
the existence of a transaction or not. The "distributed" part in that
scenario would be that one {log|lock|resource} manager could be client
to several transaction managers simultaneously. The problem of
deadlock detection is that of cyclic dependency-detection among
several transaction managers. Pessimistic Transaction Managers do use
locks and understand their semantics, thus they can communicate with
their peers that are accessing the shared pool of locks.

I would agree that the simplest solution for deadlock detection is a
timeout, but it certainly is not the only one.

Most desirable would be a measure to choose which transaction to
abort, which simultaneously avoids starvation (no more cycles, ever
for txn X), upper limits (txns beyond X objects / locks / cycles /
... cannot happen), etc.. A timeout mechanism is not going to
approach this measure, but an analysis of the dependency matrix with
the associated information on resource usage of each transaction might
get close.

so long,

Oliver

This is the real reason why I've been holding out for restricting the
feature to a specific LOCK TABLE statement: if it's designed that way,
at least you know which lock you are applying the timeout to, and have
some chance of being able to estimate an appropriate timeout.

As I pointed before - it's half useless.

And I totally do not understand why to object feature

1. that affects users *only when explicitly requested*;
2. whose implementation costs nothing - ie has no drawbacks
for overall system.

It was general practice in project so far: if user want some
feature and it doesn't affect others - let's do it.
What's changed?

Vadim

[ Charset ISO-8859-1 unsupported, converting... ]

This is the real reason why I've been holding out for restricting the
feature to a specific LOCK TABLE statement: if it's designed that way,
at least you know which lock you are applying the timeout to, and have
some chance of being able to estimate an appropriate timeout.

As I pointed before - it's half useless.

And I totally do not understand why to object feature

1. that affects users *only when explicitly requested*;
2. whose implementation costs nothing - ie has no drawbacks
for overall system.

It was general practice in project so far: if user want some
feature and it doesn't affect others - let's do it.
What's changed?

This is another reason to make it be SET TIMEOUT ... because then we
don't have to have this NOWAIT tacked on to every command. It keeps the
parser and manual pages cleaner, and it is a non-standard extension.

One idea Tom had was to make it only active in a transaction, so you do:

BEGIN WORK;
SET TIMEOUT TO 10;
UPDATE tab SET col = 3;
COMMIT

Tom is concerned people will do the SET and forget to RESET it, causing
all queries to be affected by the timeout.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

One idea Tom had was to make it only active in a transaction,
so you do:

BEGIN WORK;
SET TIMEOUT TO 10;
UPDATE tab SET col = 3;
COMMIT

Tom is concerned people will do the SET and forget to RESET
it, causing all queries to be affected by the timeout.

And what? Queries would be just aborted. It's not critical event
to take responsibility from user.

Vadim

[ Charset ISO-8859-1 unsupported, converting... ]

One idea Tom had was to make it only active in a transaction,
so you do:

BEGIN WORK;
SET TIMEOUT TO 10;
UPDATE tab SET col = 3;
COMMIT

Tom is concerned people will do the SET and forget to RESET
it, causing all queries to be affected by the timeout.

And what? Queries would be just aborted. It's not critical event
to take responsibility from user.

Hey, I agree. If the users wants the TIMEOUT, give it to them.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

On Wed, Apr 18, 2001 at 09:54:11AM +0200, Zeugswetter Andreas SB wrote:

In short, I think lock timeout is a solution searching in vain for a
problem. If we implement it, we are just encouraging bad application
design.

I agree with Tom completely here.

In any real-world application the database is the key component of a
larger system: the work it does is the most finicky, and any mistakes
(either internally or, more commonly, from misuse) have the most
far-reaching consequences. The responsibility of the database is to
provide a reliable and easily described and understood mechanism to
build on.

It is not something that makes anything unrelyable or less robust.
It is also simple: "I (the client) request that you (the backend)
dont wait for any lock longer than x seconds"

Many things that are easy to say have complicated consequences.

Timeouts are a system-level mechanism that to be useful must refer to
system-level events that are far above anything that PG knows about.

I think you are talking about different kinds of timeouts here.

Exactly. I'm talking about useful, meaningful timeouts, not random
timeouts attached to invisible events within the database.

The only way PG could apply reasonable timeouts would be for the
application to dictate them,

That is exactly what we are talking about here.

No. You wrote elsewhere that the application sets "30 seconds" and
leaves it. But that 30 seconds doesn't have any application-level
meaning -- an operation could take twelve hours without tripping your
30-second timeout. For the application to dictate the timeouts
reasonably, PG would have to expose all its lock events to the client
and expect it to deduce how they affect overall behavior.

but the application can better implement them itself.

It can, but it makes the program more complicated (needs timers
or threads, which violates your last statement "simplest interface".

It is good for the program to be more complicated if it is doing a
more complicated thing -- if it means the database may remain simple.
People building complex systems have an even greater need for simple
components than people building little ones.

What might be a reasonable alternative would be a BEGIN timeout: report
failure as soon as possible after N seconds unless the timer is reset,
such as by a commit. Such a timeout would be meaningful at the
database-interface level. It could serve as a useful building block
for application-level timeouts when the client environment has trouble
applying timeouts on its own.

Nathan Myers
ncm@zembu.com

What might be a reasonable alternative would be a BEGIN timeout: report
failure as soon as possible after N seconds unless the timer is reset,
such as by a commit. Such a timeout would be meaningful at the
database-interface level. It could serve as a useful building block
for application-level timeouts when the client environment has trouble
applying timeouts on its own.

Now that is a nifty idea. Just put it on one command, BEGIN, and have
it apply for the whole transaction. We could just set an alarm and do a
longjump out on timeout.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

On Wed, Apr 18, 2001 at 07:33:24PM -0400, Bruce Momjian wrote:

What might be a reasonable alternative would be a BEGIN timeout: report
failure as soon as possible after N seconds unless the timer is reset,
such as by a commit. Such a timeout would be meaningful at the
database-interface level. It could serve as a useful building block
for application-level timeouts when the client environment has trouble
applying timeouts on its own.

Now that is a nifty idea. Just put it on one command, BEGIN, and have
it apply for the whole transaction. We could just set an alarm and do a
longjump out on timeout.

Of course, it begs the question why the client couldn't do that
itself, and leave PG out of the picture. But that's what we've
been talking about all along.

Nathan Myers
ncm@zembu.com

On Wed, Apr 18, 2001 at 07:33:24PM -0400, Bruce Momjian wrote:

What might be a reasonable alternative would be a BEGIN timeout: report
failure as soon as possible after N seconds unless the timer is reset,
such as by a commit. Such a timeout would be meaningful at the
database-interface level. It could serve as a useful building block
for application-level timeouts when the client environment has trouble
applying timeouts on its own.

Now that is a nifty idea. Just put it on one command, BEGIN, and have
it apply for the whole transaction. We could just set an alarm and do a
longjump out on timeout.

Of course, it begs the question why the client couldn't do that
itself, and leave PG out of the picture. But that's what we've
been talking about all along.

Yes, they can, but of course, they could code the database in the
application too. It is much easier to put the timeout in a psql script
than to try and code it.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

On Wed, Apr 18, 2001 at 09:39:39PM -0400, Bruce Momjian wrote:

On Wed, Apr 18, 2001 at 07:33:24PM -0400, Bruce Momjian wrote:

What might be a reasonable alternative would be a BEGIN timeout:
report failure as soon as possible after N seconds unless the
timer is reset, such as by a commit. Such a timeout would be
meaningful at the database-interface level. It could serve as a
useful building block for application-level timeouts when the
client environment has trouble applying timeouts on its own.

Now that is a nifty idea. Just put it on one command, BEGIN, and
have it apply for the whole transaction. We could just set an
alarm and do a longjump out on timeout.

Of course, it begs the question why the client couldn't do that
itself, and leave PG out of the picture. But that's what we've
been talking about all along.

Yes, they can, but of course, they could code the database in the
application too. It is much easier to put the timeout in a psql script
than to try and code it.

Good: add a timeout feature to psql.

There's no limit to what features you might add to the database
core once you decide that new features need have nothing to do with
databases. Why not (drum roll...) deliver e-mail?

Nathan Myers
ncm@zembu.com

The only way PG could apply reasonable timeouts would be for the
application to dictate them,

That is exactly what we are talking about here.

No. You wrote elsewhere that the application sets "30 seconds" and
leaves it. But that 30 seconds doesn't have any application-level
meaning

In interactive OLTP it does.

-- an operation could take twelve hours without tripping your
30-second timeout.

Not in OLTP. Using the feature for a batch client with a low timeout
would be plain wrong.

What might be a reasonable alternative would be a BEGIN timeout: report
failure as soon as possible after N seconds unless the timer is reset,
such as by a commit. Such a timeout would be meaningful at the
database-interface level. It could serve as a useful building block
for application-level timeouts when the client environment has trouble
applying timeouts on its own.

I like that, but I do not think it is feasible.

I do not think that you can guarantee an answer within x seconds,
be it positive or negative. But that is what this feature would imply.
If the client needs to react within x sec there is no way around implementing this in
the client (there could be all kinds of trouble between client and backend).

On a very busy server (in OLTP that is the only real reason your query takes too
long other than waiting for a lock) you will produce still more work with this feature.
That is also partly why I think that a lock timeout feature really makes sense
for interactive OLTP clients. It is not a perfect solution, but it usually serves
the purpose very well and keeps the client simple. And I do not agree, that it
is an objective to keep the db code simple at the cost of making a standard client
more complex.

Andreas

OK, we have it on the TODO list, so it will hopefully be added soon, in
some fashion. I like the SET or the BEGIN TIMEOUT options.

Hi,

for 10 years i develop DB application using 'timeout on lock' feature
(Informix,Ingres,AdabasD,RDB,...).
I think about migrate with this application to postgresql, and with this
feature i don't need to modify my ready to run code specially for
postgresql. This feature guard me against blocking terminals, because long
query
initialized by operator (or administrator).

"Mikheev, Vadim" wrote in message
<8F4C99C66D04D4118F580090272A7A234D33AC@sectorbase1.sectorbase.com>...

The timeout will be useful to let the client or user decide
on an alternate course of action other that killing his
application (without the need for timers or threads in the
client program).

This assumes (without evidence) that the client has a good
idea of what the timeout limit ought to be. I think this "feature"
has no real use other than encouraging application programmers to
shoot themselves in the foot. I see no reason that we should make
it easy to misdesign applications.

AFAIR, Big Boys have this feature. If its implementation is safe,
ie will not affect applications not using it, why do not implement it?

Vadim

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://www.postgresql.org/search.mpl

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

"Henryk Szal" <szal@doctorq.com.pl> writes:

My typical short transaction run in 3 seconds (on heavy loaded system 30
sec.). But without 'timeout
on lock' it can run 60-180 minutes because someone (user or administrator)
run long transaction.
Timeout value is negligible. I set one to 10 sec. because if my two (3 sec.)
transaction are in conflict, then
both will be executed (second 3 sec. later).

Thanks, but that actually doesn't answer my question.

I asked: ``What are your actual timing constraints?'' By that I mean,
what real world constraints do you need to satisfy? You aren't
putting in a timeout for your health. You are doing it to acheive
some goal. What is that goal?

I gave three sample goals, still below. Is one of them correct? Or
do you have a different one entirely?

Ian

Ian Lance Taylor wrote in message ...

"Henryk Szal" <szal@doctorq.com.pl> writes:

YES, I DO! My transaction can't wait.
If parser on planner is blocked, then i want to abort my transaction.

What are your actual timing constraints? Is the constraint ``no
database table access may take longer than 10 seconds?'' Or is it
``no database transaction may take longer than 10 seconds?'' Or is
the constraint ``this operation may not take longer than 10 seconds?''

If the first is the actual constraint, then indeed a timeout on table
access is appropriate. But that would be a weird constraint. Can you
explain further why you need this?

If the second is the actual constraint, that also sounds strange; a
database transaction is not normally a complete transaction. You
usually have to worry about other communication overhead.

If the third is the actual constraint, then shouldn't you do the
timeout at the operation level, rather than at the database level?
What is preventing you from doing that?

---------------------------(end of broadcast)---------------------------
TIP 582: There are two major products that come out of Berkeley: LSD and UNIX.
We don't believe this to be a coincidence.
-- Jeremy S. Anderson