Re: [NOVICE] Help with "locking" please

How do you guarantee that clients actually "unlock" the rows? What if a client
crashes or unplugs the network connection? There needs to be some kind of
timeout. So then you need a separate process to manage the timeouts, and for
the clients to periodically renew the locks. Urgh.

Of course if you are happy to go and manually remove stale locks, then just
stick a "current user" column in the table. This is workable for, say 5 or 10
clients.

I would see if it was OK to simply use a "last modified" timestamp on the
record, and before updating it, detect if someone else has changed it. If so,
then give the option to cancel, overwrite or maybe even merge the changes.
You can refine this by rechecking for changes when the user first edits the
record.

A better solution might be to rearchitect the database to avoid the whole
issue. I don't know what your application does, but you mention a Contacts
table. So if you are updating a Text column by appending details of each
conversation you have, change the design so that you add a new row that
describes the change, instead of updating an existing row. (Somewhat
analogous to the "Command" design pattern).

AM

On Thursday 06 Nov 2003 11:25 pm, Boris Popov wrote:
> Hello,
>
> Here's a scenario I am trying to find a solution for. I have a
> contacts table individual rows of which can be edited in our app. I'd
> like to be able to mark a row as 'opened by someone else' when editor
> is opened and un-mark it when window is closed. No problems there, I
> could add a procpid(int4) column and update it with current process'
> PID on window open and with null on window close. One case however is
> an unexpected error where client app doesn't reset the procpid to
> null and that's a problem I'm trying to solve.
>
> My first instict would be to make procpid reference
> pg_stat_activity(procpid), but of course that's a view, so its not
> going to fly.
>
> What's a common pattern that deals with these kinds of issues? I'd
> like to be able to do this in other areas of the application also.
>
> Any help will be appreciated!
>
> --
> -Boris
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

--

Aled Morris

Home email address: aled(at)tesco(dot)net