Обсуждение: basic pg lock question

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!

"Rick Schumeyer" <rschumeyer@ieee.org> writes:
> 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?

You can, but it's usually considered bad practice to hold locks for long
enough for people to edit rows.  (What if they go off to lunch or
something while your app is holding the row locked?)

Better is to add some logic that checks when the user hits "update" to
see if the row has changed since you read it in.  If so, you can either
punt or try to merge the changes.  This requires a very short-term lock
(just long enough to avoid a race condition from simultaneous update
attempts) which you can grab with SELECT FOR UPDATE while reading the
current value of the row.

There are discussions of this issue in the mailing list archives.

            regards, tom lane

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

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.

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

To use this to prevent simultaneous inserts of the same data (for
example if two employees try to insert the same contact into the DB), I
suppose you could use a constraint (before insert) that checks that
there is no data matching the md5 checksum, right?

--
Alban Hertroys
MAG Productions

T: +31(0)53 4346874
F: +31(0)53 4346876
E: alban@magproductions.nl
W: http://www.magproductions.nl

> To use this to prevent simultaneous inserts of the same data (for
> example if two employees try to insert the same contact into the DB), I
> suppose you could use a constraint (before insert) that checks that
> there is no data matching the md5 checksum, right?


CREATE TABLE blah
(
    mymd5    TEXT NOT NULL,
    UNIQUE( mymd5 )
);

will do this for you automatically and create an index to enforce it
(don'tcreate another index !)