Обсуждение: stupid Q regarding "UPDATE waiting"
hello, I'm doing long-running transactions which insert into tables A and B, and when I try to update table C from another session, the update hangs until the first transaction is done; the related postgres process shows as "UPDATE waiting" in ps. Why is this, and is there a way around it? tnx, cm. -- Christian Mock Wiedner Hauptstrasse 15 Senior Security Engineer 1040 Wien CoreTEC IT Security Solutions GmbH +43-1-5037273
On Tue, Aug 27, 2002 at 07:40:09PM +0200, Christian Mock wrote: > hello, > > I'm doing long-running transactions which insert into tables A and B, > and when I try to update table C from another session, the update > hangs until the first transaction is done; the related postgres process > shows as "UPDATE waiting" in ps. Do you have referential integrity on any of these? That's the usual suspect in these cases. A -- ---- Andrew Sullivan 204-4141 Yonge Street Liberty RMS Toronto, Ontario Canada <andrew@libertyrms.info> M2P 2A8 +1 416 646 3304 x110
On Tue, Aug 27, 2002 at 02:08:17PM -0400, Andrew Sullivan wrote: > > hangs until the first transaction is done; the related postgres process > > shows as "UPDATE waiting" in ps. > > Do you have referential integrity on any of these? That's the usual > suspect in these cases. I see. One of the tables that are inserted into in the long-running transaction references the table that gets updated in the "UPDATE waiting" transaction. But the referencing column is always NULL on import. Does that mean that there's a table-level lock? Can I get around this? regards, cm. -- Christian Mock Wiedner Hauptstrasse 15 Senior Security Engineer 1040 Wien CoreTEC IT Security Solutions GmbH +43-1-5037273
On Wed, Aug 28, 2002 at 12:38:21PM +0200, Christian Mock wrote: > On Tue, Aug 27, 2002 at 02:08:17PM -0400, Andrew Sullivan wrote: > > > > hangs until the first transaction is done; the related postgres process > > > shows as "UPDATE waiting" in ps. > > > > Do you have referential integrity on any of these? That's the usual > > suspect in these cases. > > I see. One of the tables that are inserted into in the long-running > transaction references the table that gets updated in the "UPDATE waiting" > transaction. But the referencing column is always NULL on import. > > Does that mean that there's a table-level lock? Can I get around this? Sort of. The problem is that UPDATE take an agressive lock in order to make sure nobody changes the data while it's being updated. The RI triggers enforce a similar lock on the referenced tables. If something _else_ tries to do a similar update (or an update on, say, the referenced table), you get these near-deadlock and actual deadlock conditions. I generally find that it is simply impossible to keep high concurrency on a database using RI constraints if you have to update a large number of tuples at once. One answer seems to be to write external scripts to handle these sorts of cases. You can do your updates in (say) batches of 1000, committing after each. That's not much use to you, though, if you're hoping for real RI and real transactional integrity. Someone is, I think, working on adding another kind of lock to the system to get around this; but that's no help to you now, and it won't see the light of day in the next release, either. Someone else has offered some (somewhat nasty) patches that lower the lock level taken by the RI triggers; it works, but the RI triggers aren't as strong as they ought to be. There was some discussion of this in the past two weeks, so you should be able to check the archives of (I believe) -general for something like "patch" and get the code you need, if you decide to go that route. But beware. The problem is, certainly, a real big one for anyone using a large system with Postgres. Sorry I can't be more help. A -- ---- Andrew Sullivan 204-4141 Yonge Street Liberty RMS Toronto, Ontario Canada <andrew@libertyrms.info> M2P 2A8 +1 416 646 3304 x110
On Wed, 28 Aug 2002, Christian Mock wrote: > On Tue, Aug 27, 2002 at 02:08:17PM -0400, Andrew Sullivan wrote: > > > > hangs until the first transaction is done; the related postgres process > > > shows as "UPDATE waiting" in ps. > > > > Do you have referential integrity on any of these? That's the usual > > suspect in these cases. > > I see. One of the tables that are inserted into in the long-running > transaction references the table that gets updated in the "UPDATE waiting" > transaction. But the referencing column is always NULL on import. > > Does that mean that there's a table-level lock? Can I get around this? Hmm, I may have missed it, but can you give an example of the actual statments and schema? The only workarounds I know about are hacking the trigger functions and making the constraints deferred (which is only a partial solution, but often helps in practice).