Re: basic pg lock question

On Wed, 2005-02-02 at 10:07, 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!

Contrary to popular belief, application level locking (what you'll have
to do here) is not basic stuff, and is prone to errors. The two basic
models are 1: Use a separate field to hold a lock key of some kind, and
time it out every so often to prevent permanently locked records because
User A went to lunch and forgot about his edit, or 2: Conflict
resolution handled at checkin time.

Method 2 often provides all the protection you need and is quite easy to
program. You basically do something like:

test=# select data, md5(data) from table where id=$idnum;

data | md5
------+----------------------------------
abc | 900150983cd24fb0d6963f7d28e17f72
(1 row)

And then when you insert it, you make sure the md5 sums match:

test=# update locktest set data='xyz' where id=1 and
md5(data)='900150983cd24fb0d6963f7d28e17f72';
UPDATE 1

Note that if someone had changed the data field underneath your app, you
sould see this instead:

test=# update locktest set data='xyz' where id=1 and
md5(data)='900150983cd24fb0d6963f7d28e17f72';
UPDATE 0

So you can use pg_affected_rows to see if the change took place and
handle it in userland.