At 01:45 PM 10/2/2001, Barry Lind wrote:
>First off, are you running with autocommit turned off in JDBC?  By default 
>autocommit is on, and thus your lock is removed as soon as it is aquired.

I've tried it with auto-commit ON and OFF. With it off, I've tried it with 
READ_COMMITTED and SERIALIZABLE. All produce the same result.

However, my understanding is that each JDBC statement is executed within a 
single transaction when auto-commit is ON. I'm executing only one statement:

     select next_id_block(?, ?)

While the function does indeed execute multiple statements itself, aren't 
they all done inside a single transaction? If not, I must rethink our 
strategy as I had assumed that the PLpgSQL functions I wrote would be 

>Secondly, you don't need a table lock, you just need to lock the row 
>between the select and the update.  You should use 'select for update' to 
>do this.  That way when you issue the select to get the current value, it 
>will lock the row, preventing other select for update requests from 
>completing until the lock is released.  That way the select and the update 
>can be assured that no one else is changing the data.

THANK YOU! That's what I thought, but the documentation was a bit light on 
the subject of SELECT ... FOR UPDATE. So to mirror it back to you, if I do

     next_id_block ( count )
     (1)   read idfactory row FOR UPDATE

     (2)   update idfactory row
               increment next_id by count
               increment change_num by 1
           where change_num is equal to that read in (1)

     (3)   return next_id read in (1)

is it safe to assume that the update in (2) will ALWAYS succeed since it 
would be impossible for any other transaction to read or update the row 
once it was selected for update?

Thanks for your help.


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Reply via email to