Re: SELECT ... FOR UPDATE and ResultSet

Tom Lane wrote:

>Jeffrey Tenny <jeffrey(dot)tenny(at)comcast(dot)net> writes:
>
>
>>Can I count on this behavior for PostgreSQL? Or will it fail to lock
>>all rows if
>>I have a sufficiently large ResultSet and Connection.setFetchSize() does
>>it's magic?
>>
>>
>
>In the current implementation, the backend will only lock those rows
>actually returned to the client. If setFetchSize() causes not all the
>rows to be fetched, you lose ...
>
Any idea if there's a JDBC or other standards requirement here that is
considered the desired goal
for row retrieval with respect to FOR UPDATE?

Meanwhile, that's good to know. Is there any faster way to lock these rows
than by fetching id's?

i.e. something faster than:

SELECT id FROM table WHERE id IN (<list>)

If I wanted to lock 100 rows?

Sounds like I can't just do a SELECT COUNT(id) FROM table... either,
much less the documented SELECT id FROM table ... LIMIT 1 FOR UPDATE

Note that in my case most of my locks are nothing more than concurrency
synchronizations.
I don't even update the rows in question. I'm using them as high level
database mutexs to control
updates of rows in other tables related to the ones I locked. In the
case of multiple rows to be locked as in the IN list example above,
I'm typically locking 100 out of 10,000+ rows.

Is there some better way of doing this "database mutexing"?
It has to be at the database level, I have multiple app servers
operating on the same database,
so they can't just synchronize this behavior in app server memory.
Given that the documentation says row locks cause disk writes, I'm
certainly concerned about the performance
of this technique of database synchronization.

Thanks,

Dave