regarding isolation between threads

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.