PL/pgsql locking policy

From: Richard Emberson <emberson(at)phc(dot)net>
To: pgsql-general(at)postgresql(dot)org
Subject: PL/pgsql locking policy
Date: 2002-03-24 22:26:37
Message-ID: 3C9E529D.DA5E4C96@phc.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general


If you have a PL/pgsql procedure that first reads from a given table to
verify a condition and
then if the condition is true write to that same table ... and at the
same time you want other
processes to be able to simply read from the table, what is the best
locking policy within the procedure?

While ACCESS EXCLUSIVE MODE prevents others from reading.
allowing other to read means that two process might be concurrently
reading (verifying) within
the procedure, reach the same condition value, and then both attempt to
write data.

Is it better to simply have a helper table, a table created just for
this one procedure, the only mechanism
where writing to the table actually occurs, and use this helper table as
the locking mechanism.

Richard

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Richard Emberson 2002-03-24 22:58:14 SELECT ... FOR UPDATE ...
Previous Message Bruce Momjian 2002-03-24 22:26:05 Re: Large Object Location in 7.3