Re: SELECT ... FOR UPDATE and ResultSet

Tom Lane wrote:

Jeffrey Tenny <jeffrey.tenny@comcast.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