Procedures, triggers and transactions...

Hi,

After a long period of developing we have managed to conceive several
huge procedures that take forever to run and make up absolutely too
large transactions.

The framework is that a c++ application transforms xml-input data to
sql-procedure calls with appropriate parameters who in turn call another
procedures who in turn call another procedures... And as all procedures
are run in the transaction of the outermost select (in this case the c++
application's select that called the initial procedure) we have huge
problems with concurrency. There of course can be any number of
instances of that c++-application running.

We tried another approach that propagates calculation and invocations of
other procedures through triggers but this probably will not help us
either as all procedures are still in the same transaction and no locks
will be released until the outermost select that caused the chain has
ended?

Our current working hypothesis is to build a procedure that writes
procedure calls to a table and then let the c++-application to go
through that table and call the necessary procedures one by one - each
procedure in it's own transaction.

Any comments or suggestions?

Regards,
--
Mauri Sahlberg <Mauri(dot)Sahlberg(at)claymountain(dot)com>
Claymountain Solutions Oy