Thread: regarding threads and transactions - problem 2
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;
Connection Made
Status is : PGRES_COMMAND_OK
Result message :
Status is : PGRES_COMMAND_OK
Result message :
Status is : PGRES_TUPLES_OK
Result message :
number of rows = 1 , fields returned = 1
(2,t)
Connection Made
Connection Made
Status is : PGRES_COMMAND_OK
Result message :
Status is : PGRES_COMMAND_OK
Result message :
********Status is : PGRES_COMMAND_OK
********Result message :
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
)"
PL/pgSQL function "insert_patient" line 11 at SQL statement
********Result message :
Status is : PGRES_TUPLES_OK
Result message :
number of rows = 1 , fields returned = 1
(2,f)
Status is : PGRES_TUPLES_OK
Result message :
********Status is : PGRES_COMMAND_OK
********Result message :
number of rows = 1 , fields returned = 1
(2,f)
********Status is : PGRES_COMMAND_OK
********Result message :
Connection Made
Status is : PGRES_COMMAND_OK
Result message :
Status is : PGRES_TUPLES_OK
Result message :
number of rows = 1 , fields returned = 1
(2,f)
********Status is : PGRES_COMMAND_OK
********Result message : All threads completed successfully
Attachment
On Fri, Aug 26, 2005 at 01:28:51PM +0530, Surabhi Ahuja wrote: > Dear All, > > This is in reference to a problem which I had put up sometime back. > Please take some time to study it > > The piece of code that i am trying to execute is attached (itsa cpp file) > The stored procedure (that the program calls) insert_patient is as follows: Firstly, this has nothing to do with threads. Any programs executing this in parallel would have the same problem. > 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? > > 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 Well, you guarentee atomicity by using locks. And yes, locks do decrease performance overall. Since you don't take any locks the procedure can execute in parallel with anything else. Your options are: 1. Lock the table in the procedure 2. Change to using a system that doesn't require such a strange update procedure. 3. Trap the error and retry. Option 2 would be the best, though option 3 would be faster than option 1. Hope this helps, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them.
Attachment
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
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