Re: regarding isolation between threads

# surabhi(dot)ahuja(at)iiitb(dot)ac(dot)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