Обсуждение: SELECT...FOR UPDATE

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

The script then runs through these rows and assigns the different rows
to users, using a simple "UPDATE table1 SET USER=?" statement. And here
the fun begins. My idea is that the SELECT statement would give me
exclusive locks to these rows (while letting the rest of the rows in the
table being unlocked), but this script keeps running into deadlocks,
even though I should keep this lock until I commit, which doesn't happen
until the script is done.
Does anybody have an explanation for this phenomenon?

--
Andreas Plesner Jacobsen | Adults die young.

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.

On Thu, Jul 11, 2002 at 01:16:53PM -0700, Stephan Szabo wrote:
> >
> > 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.

You're quite right, I missed the where clause there:

WHERE table1.user is null and table1.fk_table2=table2.id

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

I know the exact query, so I don't see, where that would help?

--
Andreas Plesner Jacobsen | He who laughs has not yet heard the bad news.
                         |             -- Bertolt Brecht

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

> On Thu, Jul 11, 2002 at 01:16:53PM -0700, Stephan Szabo wrote:
> > >
> > > 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.
>
> You're quite right, I missed the where clause there:
>
> WHERE table1.user is null and table1.fk_table2=table2.id
>
> > I'd suggest running with query printing turned on to see in more
> > detail what's going on.
>
> I know the exact query, so I don't see, where that would help?

It was more to see what other transactions were doing other than
the one running the above immediately before the deadlock.

On Thu, Jul 11, 2002 at 02:38:16PM -0700, Stephan Szabo wrote:
>
> > > I'd suggest running with query printing turned on to see in more
> > > detail what's going on.
> >
> > I know the exact query, so I don't see, where that would help?
>
> It was more to see what other transactions were doing other than
> the one running the above immediately before the deadlock.

I've tried turning up the debug-level, but the amount of transactions is
too big for that to be usable :/

--
Andreas Plesner Jacobsen | I always wake up at the crack of ice.
                         |             -- Joe E. Lewis

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

> On Thu, Jul 11, 2002 at 02:38:16PM -0700, Stephan Szabo wrote:
> >
> > > > I'd suggest running with query printing turned on to see in more
> > > > detail what's going on.
> > >
> > > I know the exact query, so I don't see, where that would help?
> >
> > It was more to see what other transactions were doing other than
> > the one running the above immediately before the deadlock.
>
> I've tried turning up the debug-level, but the amount of transactions is
> too big for that to be usable :/

Hmm, without being able to see what's going on, I'm not sure what to
suggest (I assume you've tried only turning on the query printing but not
raising the normal debug level).

Maybe you'll get something useful if you define LOCK_DEBUG and
recompile and then use some of the GUC settings that defines (
see src/backend/storage/lmgr/lock.c for a little more info)