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