Thread: timeout on lock feature
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?
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,
Pennsylvania19026
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,
Pennsylvania19026
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, Pennsylvania19026
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, Pennsylvania19026
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>... >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 > >---------------------------(end of broadcast)--------------------------- >TIP 6: Have you searched our list archives? > >http://www.postgresql.org/search.mpl
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) ***************************************** > >---------------------------(end of broadcast)--------------------------- >TIP 6: Have you searched our list archives? > >http://www.postgresql.org/search.mpl
"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.
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. > >---------------------------(end of broadcast)--------------------------- >TIP 6: Have you searched our list archives? > >http://www.postgresql.org/search.mpl
"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