Re: regarding isolation between threads

# surabhi.ahuja@iiitb.ac.in / 2005-08-11 16:04:00 +0530:
> 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.

    It's doing exactly what you told it to. Thread 1 goes
    SELECTing WHERE k = $1, but sees nothing because that row hasn't
    been committed yet by thread 0, then thread 0 COMMITs, and thread1
    blows up. Or they swap roles, but that doesn't matter.

--
How many Vietnam vets does it take to screw in a light bulb?
You don't know, man.  You don't KNOW.
Cause you weren't THERE.             http://bash.org/?255991