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
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.
Dave Harkness wrote:
> 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
> 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
> 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.
In response to
pgsql-hackers by date
|Next:||From: Tatsuo Ishii||Date: 2001-10-03 01:11:03|
|Subject: Re: Unicode combining characters|
|Previous:||From: Tatsuo Ishii||Date: 2001-10-03 01:01:22|
|Subject: Re: Unicode combining characters |
pgsql-jdbc by date
|Next:||From: Dave Cramer||Date: 2001-10-03 01:11:49|
|Subject: Re: driver fails to handle strings in query statements properly |
|Previous:||From: Joe Shevland||Date: 2001-10-03 00:05:35|
|Subject: Re: jdbc download jar is corrupted|
pgsql-general by date
|Next:||From: Barry Lind||Date: 2001-10-03 01:13:14|
|Subject: Re: LOCK TABLE oddness in PLpgSQL function called via JDBC|
|Previous:||From: Tatsuo Ishii||Date: 2001-10-03 01:01:16|
|Subject: Re: [GENERAL] Problem with the accents|