Re: regarding threads and transactions - problem 2 - Mailing list pgsql-general
From | Surabhi Ahuja |
---|---|
Subject | Re: regarding threads and transactions - problem 2 |
Date | |
Msg-id | CE5C48E227F8ED4990FAC4332100ADC621B566@EVS.iiitb.ac.in Whole thread Raw |
In response to | regarding threads and transactions - problem 2 ("Surabhi Ahuja " <surabhi.ahuja@iiitb.ac.in>) |
List | pgsql-general |
DECLARE
patId bigint; oid1 int4;
val retval;
patKey text;
BEGIN
patKey := $4;
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);
SELECT patient_id INTO val.id from patient where patient_key = patKey;
SELECT INTO val.insert TRUE;
else
val.id := patId;
SELECT INTO val.insert FALSE;
end if;
RETURN val;
END;
'LANGUAGE plpgsql;
Sent: Fri 8/26/2005 2:02 PM
To: Surabhi Ahuja
Cc: pgsql-general@postgresql.org; pingo.bgm@gmail.com
Subject: Re: [GENERAL] regarding threads and transactions - problem 2
***********************
Your mail has been scanned by InterScan VirusWall.
***********-***********
Surabhi Ahuja wrote:
> BEGIN
> patKey := $4;
> 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);
> The output that i am getting (on executing it on a dual processor machine) is as follows:
> Status is : PGRES_FATAL_ERROR
> Result message : ERROR: duplicate key violates unique constraint "patient_patient_key_key"
> CONTEXT: SQL statement "insert into patient(patient_name,org_pat_id,birth_date,patient_key) values( $1 ,trim( $2 ), $3 , $4
> )"
> Please check the block in red. Why is it happening? insnt the call to the stored procedure considered one atomic operation?
> Please tell me what is going wrong?
(For those viewing in plain-text, the red block is the "duplicate pkey"
error)
> Cant I avoid such red blocks? and get messages like the ones obained from the other threads
> I can impose locks but would not that lower down the performance?
> Please suggest other solutions
There is no free solution to the problem of concurrent updates to the
same resource. You have two options:
1. Optimistically try the insert and if you get an error catch it and
issue the update instead.
2. Lock the resource for the duration of your update and deal with the
fact that some updates might time-out/fail to get the lock and need to
be retried.
3. Don't actually have a shared resource (e.g. use auto-generated
sequence values for meaningless ID numbers).
In a nutshell, those are the options available to you, but I would
recommend getting a good technical book on concurrency and spending a
couple of days with it.
In your example, I'm a little confused as to what your primary key is
(patient_id or patient_key) and what purpose the other column serves.
--
Richard Huxton
Archonet Ltd
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
pgsql-general by date: