Re: SELECT...FOR UPDATE

On Thu, 11 Jul 2002, Andreas Plesner Jacobsen wrote:

>
> I'm involved in a project using a postgresql database for it's backend.
> Currently we see a lot of deadlocks. I'll try to illustrate our problem:
>
> We have a table, table1, in which a lot of transactions takes place,
> both select, update, insert and delete transactions.
>
> The table can be illustrated like this:
>
> +----+------+-------+-------+-------+
> | ID | USER | DATA1 | DATA2 | DATA3 |
> +----+------+-------+-------+-------+
>
> We have B-tree indexes on ID and USER.
>
> Whenever a row is unused, USER is NULL. These rows are NOT touched by
> the transactions mentioned above.
>
> We have a separate script running to assign these rows to users, using a
> statement like this:
>
> SELECT table1.Id, table1.User, table1.Data1, table2.data1
> FROM table1, table2
> FOR UPDATE OF table1
> LIMIT 1000

I don't think I understand the specification here. It sounds to me
you want to get the rows where User is null, but the above query
doesn't reflect that, so I must be misunderstanding.

I'd suggest running with query printing turned on to see in more
detail what's going on.