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
---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
