Re: [HACKERS] FOR SHARE LOCK clause ? - Mailing list pgsql-hackers

Vadim Mikheev wrote:
> Clark Evans wrote:
> > > Ok, in multi-version systems readers never lock
> > > selected rows and so never block writers. Nice but
> > > cause problems in some cases: if you want
> > > to implement referential integrity at the
> > > application level then you'll have to use
> > > LOCK TABLE IN SHARE MODE or SELECT FOR UPDATE to
> > > prevent updation of primary keys etc. Not so good...

I'm trying to understand.  Please excuse my ignorance
of database implementation issues.  From a application
developers's perspective:

Suppose that I have three tables: ORDER, PRODUCT, 
and ORDER_LINE. ORDER_NO is the primary key of ORDER,
PRODUCT_NO is the primary key for PRODUCT, and ORDER_NO
and PRODUCT_NO are foreign keys in ORDER_LINE.

Now picture a data entry person with an order entry
screen, with order information above, a grid with
a row for each order line, and a drop down list box
for each possible product.

The problem you identified above would happen if
while Lucy was inserting order lines for ORDER_NO=100
PRODUCT_NO=230, someone else changes the order_no
from 100 to 101 in the order table _or_ if someone 
else deletes from product where product_no = 230.

There are a number of solutions to this pattern.

a) Pessimistic -  In this pattern, when a child is
about to be edited or added, the parent record is
locked.   When the updates to the child table are
done, a commit frees the parent.  This is coupled
with code to check to see if the row is locked 
when it is read, if so, then the parent and the
child are brought back "read-only" with a status
of "being updated..."   This solution is great when
the foreign key points to a "natural" parent, such
as an order pointing to an order_line... where deleting
the order would cascade to the order lines.  The 
solution works wonderfully when the parent and child 
are on the same screen.

b) Optimistic - In this pattern, when a child is 
inserted/updated and a parent key is not found, 
an exception is thrown by the database - "foreign
key violation".  In this case, the client application
verifies which foreign key it was, and informs the
user about the failure: "Oval Vase (203) is no
longer a valid product.  Order line addition
canceled.  Updating product list..."  This solution
works well when the object being referenced is 
maintained by a different group or is configured
by a different part of the application.  In this
case, you wouldn't want the product database to
be constantly locked up as people are ordering 
products... fixing a product name would be a pain!
In general, if deleting the parent would not cascade
to the child, this pattern is the best one to use.
This solution works great if the parent/child are
on different screens.

In any case, I'm not really sure what this locking
in share mode would get you... could you explain
in the context of the above examples.

Thanks!

Clark