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