[ic] Database Connection Concurrency/Exclusivity

Mike Heins mike at perusion.com
Tue Oct 28 16:53:39 EST 2003

Quoting Todd L. Cawthron (todd at lunsfordgroup.com):
> > > If I went the multiple query route, I would begin a transaction, run a
> > > delete query, then a number of insert queries, and finally commit the
> > > transaction. If I do this with a number of calls to the query tag (or by
> > > using the underlying database handle directly), will I have any problems
> > > running in pre-fork mode? In other words, will the interchange process
> that
> > > is running this subroutine always use the same database handle for each
> call
> > > and will it have exclusive use of this handle? Or, will other
> interchange
> > > processes potentially issue database commands using the same database
> > > connection, which might result in queries being unintentionally included
> in
> > > my transaction?
> >
> > This should not be a problem. Unless you specify that the table is
> > HOT, which is not the default, the same handle will not be used.
> >
> > You *will* have to specify
> >
> > Database foo  HAS_TRANSACTIONS  1
> >
> > as we don't do that for Sybase.
> >
> > The rest should be just like in the other ones:
> >
> > [flag type=transactions table=foo]
> > [query sql="insert into foo values (...)"]
> > [query sql="update foo set ..."]
> > [flag type=commit table=foo]
> >
> > The flag for transactions should always be in ITL, not in embedded
> > perl. The rest can be in either.
> Mike,
> Thanks for your response. I just want to make sure I'm clear on a couple of
> things.
> This is what I understand from your reply: First, defining a database/table
> with HAS_TRANSACTIONS 1 only alerts ic to the fact that the db is capable of
> using transactions and doesn't require that I explicitly use transactions
> every time I want to insert/update/delete. I can select to use transactions
> by using the flag tag as above when I need to use them.
> If I'm using multiple tables in the same database (multiple tables defined
> with identical DSNs), does this flag only affect the database handle
> associated with the specified table, or does it affect all handles to all
> tables within the same database?

Only the one. We actually open separate handles, one transaction-based
and one non-transaction-based.

> Finally, what does HOT do in the database sense? I read about hot for the
> Document object, but am not sure what it does for databases.

Theoretically it is supposed to open one handle and re-use it. It used
to work quite well for MySQL, but I am not sure it does anymore. I hve
not heard of any experimentation with it recently. 

It was designed for a site which was very busy, and the number of
handles was exceeding the number MySQL allowed.

Mike Heins
Perusion -- Expert Interchange Consulting    http://www.perusion.com/
phone +1.513.523.7621      <mike at perusion.com>

Friends don't let friends use Outlook. -- Bob Blaylock

More information about the interchange-users mailing list