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