[ic] Database Connection Concurrency/Exclusivity
Mike Heins
mike at perusion.com
Tue Oct 28 11:11:12 EST 2003
Quoting Todd L. Cawthron (todd at lunsfordgroup.com):
> I'm running Interchange using a SQL database (Sybase to be exact). Recently,
> I ran into a situation where I wanted to use a transaction when deleting and
> then inserting rows into a table.
>
> I could stitch together a batch of SQL statements and send the batch as a
> single query, or I could use multiple queries. The batch would probably be
> the safest route, although it could be a problem if the batch was really
> large.
>
> 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 Heins
Perusion -- Expert Interchange Consulting http://www.perusion.com/
phone +1.513.523.7621 <mike at perusion.com>
For a successful technology, reality must take precedence over public
relations, for Nature cannot be fooled. -- Dick Feynman
More information about the interchange-users
mailing list