On Apr 16, 2013, at 7:50 AM, Philipp Kraus <philipp.kraus@xxxxxxxxxxxx> wrote: > Hello, > > I use a PG database on a HPC system (cluster). My processes get a dataset from the database and change the row, each process is independend. > My table shows something like: id, status, data > > id = PK a unqiue number > status a enum value which "open", "waiting", "working", "done" > > So each process calls a SQL statement select * from where status = "waiting", so the process should get the next waiting task, after the process > gets the task, the status should be changed to "working", so no other process shouldn't get the task. My processes are independed, so it can > be, that 2 (or more) processes call the select statement at the same time and get in this case equal tasks, so I need some locking. How can > I do this with Postgres, that each row / task in my table is read / write by one process. On threads I would create a mutex eg: > > lock() > row = select * from table where status = waiting > update status = working from table where id = row.id > unlock() > > do something with row > > Which is the best solution with postgres? should I create a procedure which takes the next job, change it and returns the id, so each process > calls "select getNextJob()" ? "select for update" might be the answer to what you're asking for - it'll lock the rows matched until the end of the transaction, blocking any other select for update on the same rows. If performance is important then you might want to look at some of the off the shelf queuing systems instead - PgQ or queue_classic, for instance. Cheers, Steve -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
