Re: regarding isolation between threads

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.