threads and transaction ...sample code and stored procedure

Hello all,
this is in regards to a problem that i am facing ..in case of executing transaction in a multi threaded environment.
please take some time out to study it and help me.

The programs is as follows:

(see attachment)

the output on running it as follows:( i have marked the output ..in red ..at the places where i dont expect to see it...)

Connection Made

Status is : PGRES_COMMAND_OK

Result message :

Connection Made

Status is : PGRES_COMMAND_OK

Result message :

Connection Made

Co

Status is : PGRES_COMMAND_OK

Result message : nnection Made

Status is : PGRES_TUPLES_OK

Result message :

number of rows = 1 , fields returned = 1

(721,t)

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

********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

********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

********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

********Status is : PGRES_COMMAND_OK

********Result message : All threads completed successfully

in the code ..the stored procedure 'insert_patient' is being called it is a s follows:

CREATE OR REPLACE FUNCTION insert_patient (varchar(65),varchar(65),date,varchar(256)) RETURNS retval AS'

DECLARE

patId bigint; oid1 int4;

val retval;

patKey text;

BEGIN

patKey := $4;

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);

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;

Ok the expected output is:

now instead of all the red sentences ..there are 4 of such red blocks ..corresponding to 4 out of 5 threads

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

however i want to see ..

Status is : PGRES_TUPLES_OK

Result message :

number of rows = 1 , fields returned = 1

(721,f)

at each of those 4 places ....

please take some time out ..for this...

Regards

Surabhi Ahuja