Dave, I don't know why you are seeing these problems with the lock table. But the select for update should work for you. (In my product I have done exactly the same thing you are trying to do using select for update with success).
I would add one minor comment on your description of the behavior of using select for update: The select for update will block other 'select for updates' or 'updates'. It does not block other simple selects. But that is fine for the purposes here. thanks, --Barry Dave Harkness wrote: > 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 > ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
