From: Tom Lane [mailto:tgl@xxxxxxxxxxxxx]
Sent: Fri 8/19/2005 7:16 PM
To: Surabhi Ahuja
Cc: pgsql-general@xxxxxxxxxxxxxx
Subject: Re: threads and transaction ...sample code and stored procedure
"Surabhi Ahuja " <surabhi.ahuja@xxxxxxxxxxx>
writes:
> CREATE OR REPLACE FUNCTION insert_patient
(varchar(65),varchar(65),date,var=
> char(256)) RETURNS retval AS'
>
...
> LOCK TABLE patient in ACCESS EXCLUSIVE mode;
> select
patient_id into patId from patient where patient_key = patKey;
> if not
found
> THEN
> insert into
patient(patient_name,org_pat_id,birth_date,patient_key) values(=
>
$1,trim($2),$3,$4);
What Postgres version is this? I'd only expect
the above to work
properly in 8.0 and up. Prior versions didn't advance
the transaction
snapshot within plpgsql functions, so that even though you
hold an
exclusive lock, the SELECT sees a snapshot of the table dating
from
before the lock was taken, and thus possibly before a competing
thread
put in the key you are looking for. (Even in 8.0, it won't work
in
SERIALIZABLE transaction mode.)
If you aren't in a position to
update to 8.0, I think you have to issue
the commands from your application
instead of wrapping them in a function.
Or at least do "BEGIN; LOCK TABLE;
SELECT function(...); COMMIT;" from
the application.
BTW, ACCESS
EXCLUSIVE lock is overkill and will probably just lead to
deadlocks.
There's no need to lock out readers of the table, so why
not use plain
EXCLUSIVE?
regards, tom
lane
