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(at)starnix(dot)com> starnix inc.
tollfree: 1-87-pro-linux thornhill, ontario, canada
http://www.starnix.com professional linux services & products