Thread: Read Lock For Foreign Key

How come having foreign keys take out a read lock on a parent row rather than
a write lock is not on the todo list?  I had someone tell me that this is
difficult because the SQL standard does not include syntax for read lock.
Does the fact that it will be difficult mean that it isn't needed?

I'm having trouble getting Postgre to scale when the db is properly
normalized and related and there are many clients.  One of the main
contention points is with the product table.  When a product first comes out,
there may be many many people that are interested.  Because the FK triggers
take out a write lock, orders being placed referencing the same product must
wait in line to get a lock.  Under high load, that wait can be substantial.

I wish I had the resources to help with Postgre development.  For now, I can
only offer my observations.

Jon Swinth wrote:
>
> How come having foreign keys take out a read lock on a parent row rather than
> a write lock is not on the todo list?  I had someone tell me that this is
> difficult because the SQL standard does not include syntax for read lock.
> Does the fact that it will be difficult mean that it isn't needed?

The problem is that the row has to be locked against concurrent updates
until the end of the transaction. As of now, the only way to do that
under PostgreSQLs MVCC is to grab a lock FOR UPDATE, which is exclusive.

The fact that it is difficult does not mean it isn't needed. It is
simply the reason why it hasn't been done yet.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #

On Tue, 13 Aug 2002, Jon Swinth wrote:

> How come having foreign keys take out a read lock on a parent row rather than
> a write lock is not on the todo list?  I had someone tell me that this is
> difficult because the SQL standard does not include syntax for read lock.
> Does the fact that it will be difficult mean that it isn't needed?

Well, dealing with the locking may not be on the official TODO list but
it's on mine but I don't have alot of time to work on stuff so it's in the
eventual future. Just switching to a read lock may not actually fix some
of the deadlock issues that could be solved by switching up some details
of how the triggers work. But, each solution takes a bunch of time to
check possible failure cases either direction (towards deadlock or towards
an unsatisfied constraint).

Thanks for your reply Jan.  I fully understand that the difficulty is why is
has not already been done.  I was simply trying to take the first step in
getting something done in a software project... getting it on the todo list.

On Tuesday 13 August 2002 12:14 pm, Jan Wieck wrote:
> Jon Swinth wrote:
> > How come having foreign keys take out a read lock on a parent row rather
> > than a write lock is not on the todo list?  I had someone tell me that
> > this is difficult because the SQL standard does not include syntax for
> > read lock. Does the fact that it will be difficult mean that it isn't
> > needed?
>
> The problem is that the row has to be locked against concurrent updates
> until the end of the transaction. As of now, the only way to do that
> under PostgreSQLs MVCC is to grab a lock FOR UPDATE, which is exclusive.
>
> The fact that it is difficult does not mean it isn't needed. It is
> simply the reason why it hasn't been done yet.
>
>
> Jan

Thanks for your reply Stephan.  I am glad that you have placed this on your
own todo list.  I am not a fan of using triggers for foreign key constraints.
 I think foreign keys are too much an integrel part of the DB to be simple
trigger code.  It also make the description of tables rather messy.  However,
I am not in a place to complain about how things are implemented because I
cannot help with an alternative.

I do hope we can get this on the official todo list so that everyone agrees
that it is needed.

On Tuesday 13 August 2002 12:15 pm, Stephan Szabo wrote:
> On Tue, 13 Aug 2002, Jon Swinth wrote:
> > How come having foreign keys take out a read lock on a parent row rather
> > than a write lock is not on the todo list?  I had someone tell me that
> > this is difficult because the SQL standard does not include syntax for
> > read lock. Does the fact that it will be difficult mean that it isn't
> > needed?
>
> Well, dealing with the locking may not be on the official TODO list but
> it's on mine but I don't have alot of time to work on stuff so it's in the
> eventual future. Just switching to a read lock may not actually fix some
> of the deadlock issues that could be solved by switching up some details
> of how the triggers work. But, each solution takes a bunch of time to
> check possible failure cases either direction (towards deadlock or towards
> an unsatisfied constraint).

Jon Swinth <jswinth@atomicpc.com> writes:
> How come having foreign keys take out a read lock on a parent row
> rather than a write lock is not on the todo list?

It is, although I agree that the language is something other than plain
English:

* Implement dirty reads and use them in RI triggers

            regards, tom lane

Tom Lane wrote:
> Jon Swinth <jswinth@atomicpc.com> writes:
> > How come having foreign keys take out a read lock on a parent row
> > rather than a write lock is not on the todo list?
>
> It is, although I agree that the language is something other than plain
> English:
>
> * Implement dirty reads and use them in RI triggers

I was wondering if that is what they were discussing.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Thanks for you comment Tom.  I don't see how dirty reads have anything to do
with foreign keys.  Maybe I don't understand what a dirty read is in this
context.

On Tuesday 13 August 2002 02:15 pm, Tom Lane wrote:
> Jon Swinth <jswinth@atomicpc.com> writes:
> > How come having foreign keys take out a read lock on a parent row
> > rather than a write lock is not on the todo list?
>
> It is, although I agree that the language is something other than plain
> English:
>
> * Implement dirty reads and use them in RI triggers
>
>             regards, tom lane