Thread: regarding threads and transactions - problem 2

regarding threads and transactions - problem 2

From
"Surabhi Ahuja "
Date:
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:
 
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;
        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;
 
The output that i am getting (on executing it on a dual processor machine) is as follows:
 
Connection Made
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
********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_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
 
Observations:
 
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
 
Please Note: I am using PostgreSQL 8.0.0
 and the transaction level is read_committed.
 
Thank You
Regards
Surabhi Ahuja
Attachment

Re: regarding threads and transactions - problem 2

From
Martijn van Oosterhout
Date:
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

Re: regarding threads and transactions - problem 2

From
Richard Huxton
Date:
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

Re: regarding threads and transactions - problem 2

From
"Surabhi Ahuja "
Date:
patient_key is the unique key
and the primary key is patient_id, which is a bigserial.
 
actually this is what the stored procedure does:
 
a patient comes and it is associated with patient_key ...if is not present in the table, then insert it into the table.
when this patient gets inserted , the Stored procedure will return the id (bigserial) of this patient to the user.
 
and if the patient is alread in the table, then the user should get the id of this patient(which is alread present in the table).
 
....is not the stored procedure correctly coded according to the above scenario?
 
 
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;
        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;


From: pgsql-general-owner@postgresql.org on behalf of Richard Huxton
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