Re: basic pg lock question

Rick Schumeyer wrote:
> I have a question about whether or not I need to do locking to a pg
> table being accessed from a php application.
> 
> Let's say two users select rows from the table and display them in
> their browser.  User A clicks on row 1 to edit it.  Since this is php,
> so far it just selects the current values from the row.  Eventually,
> user A will submit his changes and update the row.
> 
> In the meantime, when User B looks at his web page, there will still
> be an 'edit' link for row 1.  I'm pretty sure that I don't want User B
> to try to edit the row, but as far as I understand the default postgres
> locking will not prevent this.  When user A selects row 1, do I need to
> manually lock the row to prevent another select?
> 
> I'm sorry if this is basic stuff!

You can't solve this problem with row locking. Since PHP is web-based 
you can't hold the connection open to the user's web-browser for longer 
than it takes to process one web-page.

So - you'll need one of:
1. Make sure all items are owned by only one user and only that user can 
edit them.
2. Have a user_locks table which your PHP code inserts to when user A 
starts to edit the object. Alternatively have a "locked_by" column on 
the target table and set that. You'll also need to write code to deal 
with the case when user A locks the object and goes to lunch.
3. Check the value(s) of the object before you update it. If it has 
changed since you started editing it, someone else has changed it - stop 
and tell the user. An auto-incrementing sequence can provide a useful 
single-value check for whether an object has changed. Another check 
would be OID, but make sure you understand its limitations.

I've used all three, and choosing between 2/3 will depend on which will 
make your users' lives easier.
--
   Richard Huxton
   Archonet Ltd