Thread: row locking question

row locking question

From
Darren Govoni
Date:
Hi,
  I have experimented with PostgreSQL's table locking and FOR UPDATE
capabilities. But what I'm looking for is a row level lock specific to
only a set of rows, not the entire table.

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.

Does PostgreSQL have a mechanism for this?

thank you.
Darren


Re: row locking question

From
Tom Lane
Date:
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

Re: row locking question

From
Darren Govoni
Date:
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

On Mon, 2008-11-17 at 19:37 -0500, Tom Lane wrote:
> 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
>