Thread: 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 !)