Обсуждение: Q: Record Updating/Locking in Web Environments

Hi guys,

I'm looking for some advice from someone that has probably done this before.
I have a server this is connected to by various client programs (CLI and Web
Interface for now).

This server is about to have a concept of users as far as the client/server
part goes but the server connects to the PostgreSQL database as a specific
user.

The problem now is how to do record locking when updating information.
Providing the user with a record(s) is done in one transaction.  There's
no difference [yet] of whether they're using that data to edit or read.
Sending the data back for updating is done as a second transaction.


I could just associate the timestamp of the record with the user's requested
data so that if they try to update it and it's been changed out from under
them, they get an appropriate error.

I could associate a server->PG connection with each use and keep those
pooled.  I'm assuming that record locks only last for the transaction and are
connection centric, not user centric, right?


I hate to admit it here but the server is written so that other databases
can be used at the backend so, I guess, that could alter the criteria a bit.

Any thoughts on this would be most welcome.  Regards,
--
matthew rice <matt@starnix.com>                               starnix inc.
tollfree: 1-87-pro-linux                        thornhill, ontario, canada
http://www.starnix.com              professional linux services & products

At 11:01 AM 3/31/01 -0400, Matthew Rice wrote:
>Hi guys,
>
>I'm looking for some advice from someone that has probably done this before.
>I have a server this is connected to by various client programs (CLI and Web
>Interface for now).

>The problem now is how to do record locking when updating information.
>Providing the user with a record(s) is done in one transaction.  There's
>no difference [yet] of whether they're using that data to edit or read.
>Sending the data back for updating is done as a second transaction.

It's rather difficult because:
1) The users can leave the app anytime without you knowing.
2) The users can open up more than one browser window....

You could implement your own application level MVCC however 2) makes it
difficult in some cases.

It can be a pain - in one app a few of my tables had columns with a
"committed flag", and a transaction id. And the selects all had to have
committed=true.

And this was years ago when I was using MySQL ;).

>I could just associate the timestamp of the record with the user's requested
>data so that if they try to update it and it's been changed out from under
>them, they get an appropriate error.

Yeah that could work.

Cheerio,
Link.