Thread: Locking
Is there any way to request postgres to immediately fail, or better yet fail after some small time when trying to obtain a conflicting lock ? I have ported some old code from Informix (mainly esql) and the default behaviour with the version I was using was to simply return an error. If not, is it planned for the furture ? (I am using 7.0.2 now) If the answer is no I have quite a bit of re-writing to do :-} Thanks, Regards, Ian West
Ian West <ian@niw.com.au> writes: > Is there any way to request postgres to immediately fail, or better yet > fail after some small time when trying to obtain a conflicting lock ? Deadlocks should be detected within about a second. If you have an example where one is not, let's see it... regards, tom lane
On Sat, Aug 12, 2000 at 11:24:31PM -0400, Tom Lane wrote: > Ian West <ian@niw.com.au> writes: > > Is there any way to request postgres to immediately fail, or better yet > > fail after some small time when trying to obtain a conflicting lock ? > > Deadlocks should be detected within about a second. If you have an > example where one is not, let's see it... > > regards, tom lane I don't think my problem is a deadlock, and it is almost certainly a limitation of my code. The problem arises where one does a 'select [info] from [table] for update' by one user, which locks the row just fine. The user then proceeds to edit the info, and in some (bad) cases go to lunch. Another user then decides to update the same [info] record in the same table. They hang in the 'select for update' bit forever. (Or at least days) If I terminate the original user process, they proceed fine, which is as it should be. With the old Informix libs, in the same situation I would get an immediate error on the second client session (record in use by another user) and it would identify the user who had the record locked, which was enormously handy. (In sample above where 'user' had literally gone out to lunch half way through an update.) I understand that I can use non-blocking io, and poll for data, and send an abort after a delay if I don't get my lock within a reasonable time, but this doesn't help with ecpg as far as I can tell. (Although I may very well be missing the obvious here :-) The question I think is more can I set the default action on requiring a lock on a record which is in use to be fail, rather than wait. Or can I specify how long to wait. Thanks, Ian West
Ian West <ian@niw.com.au> writes: > The question I think is more can I set the default action on requiring a > lock on a record which is in use to be fail, rather than wait. Or can I > specify how long to wait. OK, I see. No, there's no such facility at the moment, though it seems like something worth thinking about. > I understand that I can use non-blocking io, and poll for data, and send > an abort after a delay if I don't get my lock within a reasonable time, > but this doesn't help with ecpg as far as I can tell. (Although I may > very well be missing the obvious here :-) Well, you could fire off a subprocess that would simply sleep for X amount of time and then issue the cancel request if it hadn't been killed meanwhile. (There's no restriction that the cancel request come from the same process that issued the query.) A tad tedious but it'd work. Perhaps a better answer is to restructure your application's queries so that you avoid holding FOR UPDATE locks for long periods. regards, tom lane