Thread: AW: timeout on lock feature
> 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
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, Pennsylvania19026
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
<p><font size="2">Sorry for my forgetfulness (and a search through geocrawler didn't turn up anything useful), but what wasthe problem with something like NOWAIT?</font><p><font size="2">e.g.: SELECT * FROM a FOR UPDATE NOWAIT;</font><p><fontsize="2">where, if the required lock could not be obtained immediately, this statement would raisean error.</font><p><font size="2">Cheers...</font><br /><p><font size="2">MikeA</font><br /><p><font size="2">>>-----Original Message-----</font><br /><font size="2">>> From: Tom Lane [<a href="mailto:tgl@sss.pgh.pa.us">mailto:tgl@sss.pgh.pa.us</a>]</font><br/><font size="2">>> Sent: 17 April 2001 15:49</font><br/><font size="2">>> To: Bruce Momjian</font><br /><font size="2">>> Cc: Zeugswetter Andreas SB;Henryk Szal; pgsql-hackers@postgresql.org</font><br /><font size="2">>> Subject: Re: AW: [HACKERS] timeout on lockfeature </font><br /><font size="2">>> </font><br /><font size="2">>> </font><br /><font size="2">>>Bruce Momjian <pgman@candle.pha.pa.us> writes:</font><br /><font size="2">>> > Added to TODO:</font><br/><font size="2">>> > * Add SET parameter to timeout if waiting for lock too long</font><br /><fontsize="2">>> </font><br /><font size="2">>> I repeat my strong objection to any global (ie, affecting alllocks)</font><br /><font size="2">>> timeout. Such a "feature" will have unpleasant consequences.</font><br /><fontsize="2">>> </font><br /><font size="2">>> regards, tom lane</font><br /><font size="2">>></font><br /><font size="2">>> ---------------------------(end of </font><br /><font size="2">>>broadcast)---------------------------</font><br /><font size="2">>> TIP 5: Have you checked our extensiveFAQ?</font><br /><font size="2">>> </font><br /><font size="2">>> <a href="http://www.postgresql.org/users-lounge/docs/faq.html" target="_blank">http://www.postgresql.org/users-lounge/docs/faq.html</a></font><br/><font size="2">>> </font><code><fontsize="3"><br /><br /> _________________________________________________________________________<br /> Thise-mail and any attachments are confidential and may also be privileged and/or copyright <br /> material of Intec TelecomSystems PLC (or its affiliated companies). If you are not an <br /> intended or authorised recipient of this e-mailor have received it in error, please delete <br /> it immediately and notify the sender by e-mail. In such a case,reading, reproducing, <br /> printing or further dissemination of this e-mail is strictly prohibited and may be unlawful.<br /> Intec Telecom Systems PLC. does not represent or warrant that an attachment hereto is free <br /> from computerviruses or other defects. The opinions expressed in this e-mail and any <br /> attachments may be those of the authorand are not necessarily those of Intec Telecom <br /> Systems PLC. <br /><br /> This footnote also confirms that thisemail message has been swept by<br /> MIMEsweeper for the presence of computer viruses. <br /> __________________________________________________________________________<br/></font></code>
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
> > 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
This option will be OPTIONAL. Tom Lane wrote in message <23613.987518927@sss.pgh.pa.us>... >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