On 2005-03-01, Don Drake <[EMAIL PROTECTED]> wrote: > I don't think it would be easy to duplicate since our code base is > quite extensive. > > Basically, what was happening was a script would first open a database > connection (AutoCommit turned off by default), create a few objects > (that also opened independent db connections), the objects would run > queries so they have data populated, an insert is done and committed, > then we call a generic function that will create a new table (using > inherits, part of our partitioning) as well as adding indexes and > constraints to this new table. It would get to a point in the > function where it was adding a FK constraint and every query against > the table would "hang" which appeared to be some exclusive lock not > being released. Activity on the DB would be 100% idle during this > period, the alter table never came back so we killed it each time. I > commented out the code doing the FK constraint add and everything > worked just fine.
This sounds as though your application deadlocked against itself - by using multiple connections without autocommit, you can easily get into situations where you are waiting for completion on one connection, which is blocked waiting for a lock held by another connection - the lock remains until the second connection commits, which never happens since the app is waiting on the first. The DB can't detect this as a deadlock because it does not know that one session is waiting on another on the client side; deadlock detection considers only sessions waiting _inside the server_. This situation isn't specific to DDL, but is easier to produce that way since most DDL operations acquire very high level locks (often AccessExclusive, which blocks queries). > As a test I moved the partition function call to the beginning of the > script (before the objects were created) and it worked just fine. I > then changed the object declarations passing in the single DB handle, > and every now works just fine. This is consistent with it being a client-side deadlock. -- Andrew, Supernews http://www.supernews.com - individual and corporate NNTP services ---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
