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.