row locking question

Darren Govoni <darren@ontrenet.com> writes:

For example, there is a table with many rows. Threads are doing
"SELECTS" to read some rows. They should be allowed to get results on
rows that are not currently locked by another thread's SELECT,FOR UPDATE
or equivalent read lock for those rows only.

Make those threads use SELECT FOR SHARE.

regards, tom lane

Thank you for the suggestion.

It seems that still blocks. Here is what I'm trying to achieve.

I have 10 rows each with an id 1,2,3,4 etc.

In one thread I want to update rows with id < 4 (1-3) in my_table
inside a transaction (i.e. begin work; update where; commit work;)

In another thread I do a select * from my_table (while the first thread
is inside the transaction). I want to receive rows with id 4-10 because
rows 1-3 are currently locked and therefore not "seen".

For all I know, this isn't possible in current SQL semantics, but
would be very useful for distributed scheduling. So nodes competing to
update rows with the same query don't collide and shouldn't have to wait
on another threads updates to get some results.

I'll keep experimenting!

Darren