Re: Procedures, triggers and transactions... - Mailing list pgsql-admin

su, 2004-01-25 kello 23:08, Tom Lane kirjoitti:
> Mauri Sahlberg <Mauri.Sahlberg@claymountain.com> writes:
> > 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.
>
> Why do you have "huge problems with concurrency"?  Under MVCC the only
> reason for such things to conflict is if different transactions try to
> update the same rows.  If you do have such updates, it would seem that
> breaking the transactions into smaller ones would be likely to introduce
> correctness issues ...

Our transactions have such updates.

We either have no concurrency or everything will fail. As the chain of
the procedures will lock and update several tables and those tables
remain locked until the transaction is finished we either have no
concurrency or run into conflicts when several transactions try to run
concurrently.

The coder who wrote the sql-procedures is different one than the one
that wrote the c++ application and has different kind of deal with the
department that ordered the application. All hours he spends are
considered non cost but every hour spent in coding c++ cost extra and
required permission from a manager who just didn't understand anything
but costs. Naturally it was a lot easier for the sql-coder to call
another procedure within procedure to extend the functionality of the
application than ask that if the c++-application could call another
procedure after the first one. And as this was his first assignment as a
sql-coder ever he had never heard about transactions or locks. He just
happily redesigned and re-engineered the Cobol code he and his
predecessors had written to sql-procedures. As this project has been
going on more than a year there is a really lot of code and lot of
updates and calculations behind the initial procedure call (the worst
run so far has taken 12 hours to complete). Of course issues with
concurrency didn't come up  in the sql-coder's test environment but blew
the whole thing as soon as it was tried in shared test environment with
several concurrent users.

Now we have about a month to fix both issues the concurrency problem and
the runtime problem. So far I have no other idea to tackle this than to
make hin to write yet another sql-procedure that writes the necessary
procedure calls with parameters to a table and modify the
c++-application to go through this table of procedure calls one by one.
At the same we'll go through the individual procedures one by one to see
if they do something really stupid that would explain the very long
running time.

(I kind of hoped that nested transactions would be just around the
corner and we could just magically insert begin and commit to every
procedure and be saved...)

Regards,
--
Mauri "mos" Sahlberg    Pretax Systems Oy    +358 207 44 2228
Technology Evangelist    Pääskylänrinne 8    +358 207 44 2201
Bsc Computer Science    FIN-00500 Helsinki    www.pretax.net
Development Manager    Finland