Re: LOCK TABLE oddness in PLpgSQL function called via JDBC

At 01:45 PM 10/2/2001, Barry Lind wrote:
>Dave,
>
>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
transactional.

>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.

Peace,
Dave