Thread: Waiting on a transaction
I've read over the docs on Concurrency Control but still not clear about when transactions block other updates, and how to deal with that on the application level. If I do a BEGIN and an UPDATE in one psql session and then try and do an UPDATE in another psql session that UPDATE waits until either a COMMIT or ROLLBACK. Is it common for applications using Postgresql to set a timer on updates and abort? -- Bill Moseley moseley@hank.org
On Tue, Aug 16, 2005 at 11:05:35AM -0700, Bill Moseley wrote: > I've read over the docs on Concurrency Control but still not clear > about when transactions block other updates, and how to deal with that > on the application level. > > If I do a BEGIN and an UPDATE in one psql session and then try and do > an UPDATE in another psql session that UPDATE waits until either a > COMMIT or ROLLBACK. > > Is it common for applications using Postgresql to set a timer on > updates and abort? It is not normal to hold a transaction open while doing nothing. If you always send transactions without delays the issue doesn't come up because you never have to wait long enough for it matter. If you want to see the issues it causes, search the web for "idle in transaction"... There may be a timeout, I've never needed it though... Hope this helps, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them.
Attachment
On Tue, Aug 16, 2005 at 08:25:25PM +0200, Martijn van Oosterhout wrote: > On Tue, Aug 16, 2005 at 11:05:35AM -0700, Bill Moseley wrote: > > I've read over the docs on Concurrency Control but still not clear > > about when transactions block other updates, and how to deal with that > > on the application level. > > > > If I do a BEGIN and an UPDATE in one psql session and then try and do > > an UPDATE in another psql session that UPDATE waits until either a > > COMMIT or ROLLBACK. > > > > Is it common for applications using Postgresql to set a timer on > > updates and abort? > > It is not normal to hold a transaction open while doing nothing. If you > always send transactions without delays the issue doesn't come up > because you never have to wait long enough for it matter. Ok, that will be the normal case. I was just wondering because I was running test code today and it hung. I wondered what was happening and found out I left a psql window open last night in the middle of a transaction. That shouldn't happen in production. So then I wondered if my application should set an alarm and timeout with an error if, by odd chance, an update hangs. Trying to be a bit more robust -- not that the application could recover, but at least it could spit out an error other than hang. -- Bill Moseley moseley@hank.org
On Tue, 2005-08-16 at 12:01 -0700, Bill Moseley wrote: > I wondered if my application should set an alarm and timeout > with an error if, by odd chance, an update hangs. Here's a way to handle this under the upcoming 8.1 release: Before you execute the update you can execute SELECT ... FOR UPDATE NOWAIT to select anything (e.g. dummy constant) from the row that you want to update. If no error is thrown then you have a lock on your row and you can confidently proceed with your update. Otherwise someone has that row locked. At that point you can loop and retry if you want.
Wow, non-blocking lock failure? Can I take this chance to say an overdue thanks to the Postgresql developers? A truly commercial grade feature set in a free database... On 8/16/05, Matt Miller <mattm@epx.com> wrote: > On Tue, 2005-08-16 at 12:01 -0700, Bill Moseley wrote: > > I wondered if my application should set an alarm and timeout > > with an error if, by odd chance, an update hangs. > > Here's a way to handle this under the upcoming 8.1 release: > > Before you execute the update you can execute SELECT ... FOR UPDATE > NOWAIT to select anything (e.g. dummy constant) from the row that you > want to update. If no error is thrown then you have a lock on your row > and you can confidently proceed with your update. Otherwise someone has > that row locked. At that point you can loop and retry if you want. > > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend >
On Aug 16, 2005, at 3:01 PM, Bill Moseley wrote: > So then I wondered if my application should set an alarm and timeout > with an error if, by odd chance, an update hangs. Trying to be a bit > more robust -- not that the application could recover, but at least it > could spit out an error other than hang. > Robustness in the face of breakage is always good. Since you're programming in transactions already, you have planned for failed commits anyhow, so adding timeouts will probably not cause any significant harm to your program. The trick is dealing with statement timeouts on shared pool connections over mod_perl and Apache::DBI. I haven't satisfied myself yet that the timeout will be unset when the next connection uses the DB... Vivek Khera, Ph.D. +1-301-869-4449 x806
On Thu, Aug 18, 2005 at 01:33:18PM -0400, Vivek Khera wrote: Hey Vivek! Nice to hear from you over here. ;) > The trick is dealing with statement timeouts on shared pool > connections over mod_perl and Apache::DBI. I haven't satisfied > myself yet that the timeout will be unset when the next connection > uses the DB... You mean other than setting "alarm 0;"? -- Bill Moseley moseley@hank.org