regarding isolation between threads

["Surabhi Ahuja " <surabhi.ahuja@xxxxxxxxxxx>]

void *connect(void* threadid)
{
    char command[100];
    int *id_ptr, taskid;
    id_ptr = (int *) threadid;
    taskid = *id_ptr;

    if(taskid == 0)
        strcpy(command, "select insert (1)");
    else if(taskid == 1)
        strcpy(command, "select insert (1)");
    else if(taskid == 2)
        strcpy(command, "select insert (3)");
    else if(taskid == 3)
        strcpy(command, "select insert (4)");
    else if(taskid == 4)
        strcpy(command, "select insert (5)");

 

   PGconn *conn = connect("dbname=x host=y user=z");

   pgresult res;

   res = pqexec (conn, "begin transaction");

   res = pqexec (conn, command);

   res = pqexec (conn, "commit");

   pqfinish (conn);

 

   pthread_exit(NULL);
}
int main()
{
    pthread_t threads[NUM_THREADS];
    int rc;

    int *taskids[NUM_THREADS];

    for(int t=0; t<NUM_THREADS; t++)
    {
        taskids[t] = (int *) malloc(sizeof(int));
        *taskids[t] = t;

        rc = pthread_create(&threads[t], NULL, connect, (void *) taskids[t]);
        if (rc)
        {
            printf("ERROR; return code from pthread_create() is %d\n", rc);
            exit(-1);
        }
    }

    for(int t=0; t<NUM_THREADS; t++)
    {
        delete taskids[t];
    }

    pthread_exit(NULL);
}

 

 

the stored procedure (just the pseudo code)

 table x has a primary key k

insert(integer)

{

     select from table if k =  $1

     if not found

        insert into x ($1);

    else

       insert into some_other_table($1);

    end if

}

 

the kind of output i am expecting is:

 

table x: 1 3 4 5

table a: 1

and no error message

 

but the output is something like

 

table x : 1 3 4 5

table some_other_table :

it has nothing

and error message is displayed : "error in stored proc "insert(..... primary key violation .."

this error is because

 

two threads are simultaneoulsy trying to insert the values "1" each and thats where they interfere with each other.

 

thanks

surabhi ahuja