Thread: regarding isolation between threads
void *connect(void* threadid)
{
char command[100];
int *id_ptr, taskid;
id_ptr = (int *) threadid;
taskid = *id_ptr;
{
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)");
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 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;
{
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);
}
}
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];
}
{
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
Surabhi Ahuja wrote: > 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)"); ... > 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. That's because that's what you've asked them to do (see code fragment). Oh, and personally, I think "insert" is a bad name for a function - it just took me 5 minutes to figure out what you meant. -- Richard Huxton Archonet Ltd
# 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
On Thu, 11 Aug 2005, Surabhi Ahuja wrote: > 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. I don't think serializable can help you with this. If we did the full serializability checks, I believe the above could easily fail with a serializability failure and rollback since both selects could (and probably will) still run before either insert.