Обсуждение: 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)