Re: AutoCommit and DDL

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.

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.

I ran DBI traces and everything looked just fine. This was a strange
problem, I'm just happy everything is working.

-Don

On Sun, 27 Feb 2005 20:33:55 -0700, Michael Fuhr <mike(at)fuhr(dot)org> wrote:
> On Sun, Feb 27, 2005 at 07:55:35PM -0600, Don Drake wrote:
>
> > The problem has to do with multiple concurrent connections to the
> > server causing problems. I've removed the concurrent connections and
> > now this works. Strange.
>
> Can you elaborate? Can you describe the scenario with enough detail
> that somebody else could attempt to duplicate it? It's certainly
> possible to issue DDL statements concurrent with other connections,
> so we still haven't identified what's really causing the problem.
> Investigation shouldn't end until "strange" becomes "aha!"
>
> --
> Michael Fuhr
> http://www.fuhr.org/~mfuhr/
>

--
Donald Drake
President
Drake Consulting
http://www.drakeconsult.com/
http://www.MailLaunder.com/
312-560-1574