Re: dataset lock

On 2013-04-17 09:18:13 +0200, Albe Laurenz said:

> Philipp Kraus wrote:
>> My PG database is connected to differend cluster nodes (MPI). Each
>> programm / process on each node are independed and run the SQL
>> select * from table where status = waiting
>> after that I update the row with the update statement (set status = working)
>>
>> so in this case one process can run the select, than comes another
>> process and runs also the select, but both processes get an equal row.
>> But this does not allowed. The second process need not see the row,
>> which is taken by the first process. So can I suppress, that a select
>> call
>> sees a row, which is locked by a transaction? So I would like to do
>> this with a store procedure, that runs the select and the update and
>> after that
>> it returns the PK of the selected dataset. If two (or more) processes
>> run the SP at the same time, but the update can create an error, so the
>> stored
>> procedure is stopped and must called again.
>> I need a solution, that a row, which is taken by one process not shown
>> by all other processes
>
> Do you want to implement something like a queue?

Yes

>
> I can think of two techniques:
>
> 1) Locking
> ----------
> In a transaction, you get a few rows for processing by
> SELECT * FROM table WHERE status = waiting
>    ORDER BY id LIMIT 5 FOR UPDATE;
> ("id" is the primary key here).
> Then you process and update the rows and commit.
>   This will cause concurrent SELECT FOR UPDATE operations
> to block until the transaction is committed, effectively
> serializing the processing.
>
> 2) Set a marker
> ---------------
> You get a few rows by
> UPDATE table SET status = processing WHERE id IN
>    (SELECT id FROM table WHERE status = waiting
>        ORDER BY id LIMIT 5) RETURNING *;
> Then process and update the rows.
>
> This won't block concurrent processes for the whole
> time it takes to process the rows, so it's probably
> closer to what you want.

The marker solution seems to be the correct idea, I must think about it.
Would you create the call within a stored procedure (and call the SP
from the client) or would you use the
statement from a client direct?

Thx

Phil