You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@cocoon.apache.org by Daniel Fagerstrom <da...@nada.kth.se> on 2002/12/13 15:06:39 UTC
SQLTransformer and Transactions
I'd like to use transactions in the SQLTransformer and tried something like:
...
<execute-query>
<query>
BEGIN;
</query>
</execute-query>
<execute-query>
<query>
Do something
</query>
</execute-query>
<execute-query>
<query>
Do something that is based on the previous query
</query>
</execute-query>
<execute-query>
<query>
COMMIT;
</query>
</execute-query>
...
This does not work in the current implementation of the SQLTransformer
as it gets a new connection from the pool for each execute-query and all
statements within one transactions must be run from the same connection.
Even worse, as the pooled connections can be reused from another
pipeline, the same transaction can continue in a completely unexpected
place for another user. So it might work as expected for a single user
but in unexpected ways for multiple users.
One way to solve this would be to use the same connection for all
execute-query in an input xml document. It would still be necessary to
open new connections for embedded queries, using the ancestor functionality.
Another solution would be to introduce a transaction tag and letting all
queries within it use the same connection and let the start tag execute
"connection.setAutoCommit(false)" and the end tag execute
"connection.commit()". Embedded queries must of course still have own
connections.
Booth solutions requires some rewriting of the connection handling code
in the SQLTransformer.
As I need transactions in the application I currently work on, I have to
modify the SQLTransformer to handle it. Is there any problems with the
above proposed methods? Which one do you prefer? Are there better methods?
/Daniel Fagerstrom
---------------------------------------------------------------------
To unsubscribe, e-mail: cocoon-dev-unsubscribe@xml.apache.org
For additional commands, email: cocoon-dev-help@xml.apache.org
Re: SQLTransformer and Transactions
Posted by Sylvain Wallez <sy...@anyware-tech.com>.
Antonio Gallardo wrote:
>Can you explain what you have in mind Sylvain? I dont understand what you
>have in mind.
>
I guess it's the "load on the pool" that is unclear.
What I'm saying is that I consider a waste of CPU to get a connection
from the same pool for each <sql:execute-query> encountered in the
document. The transformer can be made quicker by getting a connection
either at the start of the document or at the first <sql:execute-query>,
and close it in endDocument() or recycle() (this last one is better
since endDocument() may not be called if an exception occurs in the
pipeline).
>Sylvain Wallez dijo:
>
>
>
>>I consider that getting a connection from the pool for each
>><execute-query> creates some unnececessary load on the pool. So I would
>>go for the first solution (use the same connection).
>>
>>Other thoughts from the database specialists out there ?
>>
Sylvain
--
Sylvain Wallez Anyware Technologies
http://www.apache.org/~sylvain http://www.anyware-tech.com
{ XML, Java, Cocoon, OpenSource }*{ Training, Consulting, Projects }
---------------------------------------------------------------------
To unsubscribe, e-mail: cocoon-dev-unsubscribe@xml.apache.org
For additional commands, email: cocoon-dev-help@xml.apache.org
Re: SQLTransformer and Transactions
Posted by Antonio Gallardo <ag...@agsoftware.dnsalias.com>.
Can you explain what you have in mind Sylvain? I dont understand what you
have in mind.
Antonio Gallardo
Sylvain Wallez dijo:
>
> I consider that getting a connection from the pool for each
> <execute-query> creates some unnececessary load on the pool. So I would
> go for the first solution (use the same connection).
>
> Other thoughts from the database specialists out there ?
>
> Sylvain
>
> --
> Sylvain Wallez Anyware Technologies
> http://www.apache.org/~sylvain http://www.anyware-tech.com {
> XML, Java, Cocoon, OpenSource }*{ Training, Consulting, Projects }
---------------------------------------------------------------------
To unsubscribe, e-mail: cocoon-dev-unsubscribe@xml.apache.org
For additional commands, email: cocoon-dev-help@xml.apache.org
Re: SQLTransformer and Transactions
Posted by Daniel Fagerstrom <da...@nada.kth.se>.
Sylvain Wallez wrote:
<snip/>
> I consider that getting a connection from the pool for each
> <execute-query> creates some unnececessary load on the pool. So I would
> go for the first solution (use the same connection).
I will try that.
There will still be a need for new connections for sub queries, to reuse
the same connection for sub queries as well one would need to cache the
result from the embedding query. As I haven't used subqueries yet and
don't know what typical use cases are, I can't evaluate the consequnces
of such cashing, any ideas?
/Daniel Fagerstrom
---------------------------------------------------------------------
To unsubscribe, e-mail: cocoon-dev-unsubscribe@xml.apache.org
For additional commands, email: cocoon-dev-help@xml.apache.org
Re: SQLTransformer and Transactions
Posted by Daniel Fagerstrom <da...@nada.kth.se>.
Luca,
I would prefer to use stored procedures, but they are not implemented
yet in MySQL and we use MySQL in all our applications. MySQL doesn't
suport the begin-end; construction you used below either AFAIK.
/Daniel Fagerstrom
Luca Morandini wrote:
> Sylvian,
>
> I'm not completely convinced a rewriting of SQLTransformer is needed to handle transactions... I tried the following:
>
> <sql:query isstoredprocedure="false">
> name="test">
> begin
> DELETE FROM CRRegion
> WHERE Id > 80;
> COMMIT;
> INSERT INTO CRRegion
> (Id, Name)
> VALUES
> ('90', 'Foo');
> INSERT INTO CRRegion
> (Id, Name)
> VALUES
> ('91', 'Bar');
> ROLLBACK;
> end;
> </sql:query>
>
> And it worked just fine (both the commit and the rollback parts). I even tried
> some variations on this theme and they worked just as well.
>
> There is a catch though: I tried on Oracle only.
> I don't know whether the begin/end statement could be used with other DBMSes.
>
> Anyway, I agree the use of a single connection to process the complete input document would increase performance and reduce
> side-effects.
>
> Best regards,
>
> P.S.
> As you may easily imagine, I consider Stored Procedures the only proper place for handling transactions; but, if people cannot/want
> not go for SPs... let's give them some alternatives.
>
> ---------------------------------------------
> Luca Morandini
> GIS Consultant
> lmorandini@ieee.org
> http://utenti.tripod.it/lmorandini/index.html
> ---------------------------------------------
---------------------------------------------------------------------
To unsubscribe, e-mail: cocoon-dev-unsubscribe@xml.apache.org
For additional commands, email: cocoon-dev-help@xml.apache.org
RE: SQLTransformer and Transactions
Posted by Luca Morandini <lu...@tin.it>.
> -----Original Message-----
> From: Sylvain Wallez [mailto:sylvain.wallez@anyware-tech.com]
> Sent: Sunday, December 15, 2002 1:48 PM
> To: cocoon-dev@xml.apache.org
> Subject: Re: SQLTransformer and Transactions
<snip>
> In the above example, the transaction is contained in a _single_
> <sql:query> element. What Daniel pointed out, IIUC, is that it's not
> possible to have a transaction across several <sql:query>.
</snip>
Sure, but why do that when there is an alternative ?
Best regards,
---------------------------------------------
Luca Morandini
GIS Consultant
lmorandini@ieee.org
http://utenti.tripod.it/lmorandini/index.html
---------------------------------------------
---------------------------------------------------------------------
To unsubscribe, e-mail: cocoon-dev-unsubscribe@xml.apache.org
For additional commands, email: cocoon-dev-help@xml.apache.org
Re: SQLTransformer and Transactions
Posted by Sylvain Wallez <sy...@anyware-tech.com>.
Luca Morandini wrote:
>Sylvian,
>
>I'm not completely convinced a rewriting of SQLTransformer is needed to handle transactions... I tried the following:
>
><sql:query isstoredprocedure="false">
> name="test">
> begin
> DELETE FROM CRRegion
> WHERE Id > 80;
> COMMIT;
> INSERT INTO CRRegion
> (Id, Name)
> VALUES
> ('90', 'Foo');
> INSERT INTO CRRegion
> (Id, Name)
> VALUES
> ('91', 'Bar');
> ROLLBACK;
> end;
></sql:query>
>
>And it worked just fine (both the commit and the rollback parts). I even tried
>some variations on this theme and they worked just as well.
>
>
In the above example, the transaction is contained in a _single_
<sql:query> element. What Daniel pointed out, IIUC, is that it's not
possible to have a transaction across several <sql:query>.
Sylvain
--
Sylvain Wallez Anyware Technologies
http://www.apache.org/~sylvain http://www.anyware-tech.com
{ XML, Java, Cocoon, OpenSource }*{ Training, Consulting, Projects }
---------------------------------------------------------------------
To unsubscribe, e-mail: cocoon-dev-unsubscribe@xml.apache.org
For additional commands, email: cocoon-dev-help@xml.apache.org
RE: SQLTransformer and Transactions
Posted by Luca Morandini <lu...@tin.it>.
Sylvian,
I'm not completely convinced a rewriting of SQLTransformer is needed to handle transactions... I tried the following:
<sql:query isstoredprocedure="false">
name="test">
begin
DELETE FROM CRRegion
WHERE Id > 80;
COMMIT;
INSERT INTO CRRegion
(Id, Name)
VALUES
('90', 'Foo');
INSERT INTO CRRegion
(Id, Name)
VALUES
('91', 'Bar');
ROLLBACK;
end;
</sql:query>
And it worked just fine (both the commit and the rollback parts). I even tried
some variations on this theme and they worked just as well.
There is a catch though: I tried on Oracle only.
I don't know whether the begin/end statement could be used with other DBMSes.
Anyway, I agree the use of a single connection to process the complete input document would increase performance and reduce
side-effects.
Best regards,
P.S.
As you may easily imagine, I consider Stored Procedures the only proper place for handling transactions; but, if people cannot/want
not go for SPs... let's give them some alternatives.
---------------------------------------------
Luca Morandini
GIS Consultant
lmorandini@ieee.org
http://utenti.tripod.it/lmorandini/index.html
---------------------------------------------
> -----Original Message-----
> From: Sylvain Wallez [mailto:sylvain.wallez@anyware-tech.com]
> Sent: Saturday, December 14, 2002 11:24 PM
> To: cocoon-dev@xml.apache.org
> Subject: Re: SQLTransformer and Transactions
>
>
> Daniel Fagerstrom wrote:
>
> > I'd like to use transactions in the SQLTransformer and tried something
> > like:
> >
> > ...
> > <execute-query>
> > <query>
> > BEGIN;
> > </query>
> > </execute-query>
> > <execute-query>
> > <query>
> > Do something
> > </query>
> > </execute-query>
> > <execute-query>
> > <query>
> > Do something that is based on the previous query
> > </query>
> > </execute-query>
> > <execute-query>
> > <query>
> > COMMIT;
> > </query>
> > </execute-query>
> > ...
> >
> > This does not work in the current implementation of the SQLTransformer
> > as it gets a new connection from the pool for each execute-query and
> > all statements within one transactions must be run from the same
> > connection. Even worse, as the pooled connections can be reused from
> > another pipeline, the same transaction can continue in a completely
> > unexpected place for another user. So it might work as expected for a
> > single user but in unexpected ways for multiple users.
> >
> > One way to solve this would be to use the same connection for all
> > execute-query in an input xml document. It would still be necessary to
> > open new connections for embedded queries, using the ancestor
> > functionality.
> >
> > Another solution would be to introduce a transaction tag and letting
> > all queries within it use the same connection and let the start tag
> > execute "connection.setAutoCommit(false)" and the end tag execute
> > "connection.commit()". Embedded queries must of course still have own
> > connections.
> >
> > Booth solutions requires some rewriting of the connection handling
> > code in the SQLTransformer.
> >
> > As I need transactions in the application I currently work on, I have
> > to modify the SQLTransformer to handle it. Is there any problems with
> > the above proposed methods? Which one do you prefer? Are there better
> > methods?
>
>
> I consider that getting a connection from the pool for each
> <execute-query> creates some unnececessary load on the pool. So I would
> go for the first solution (use the same connection).
>
> Other thoughts from the database specialists out there ?
>
> Sylvain
>
> --
> Sylvain Wallez Anyware Technologies
> http://www.apache.org/~sylvain http://www.anyware-tech.com
> { XML, Java, Cocoon, OpenSource }*{ Training, Consulting, Projects }
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: cocoon-dev-unsubscribe@xml.apache.org
> For additional commands, email: cocoon-dev-help@xml.apache.org
>
---------------------------------------------------------------------
To unsubscribe, e-mail: cocoon-dev-unsubscribe@xml.apache.org
For additional commands, email: cocoon-dev-help@xml.apache.org
Re: SQLTransformer and Transactions
Posted by Sylvain Wallez <sy...@anyware-tech.com>.
Daniel Fagerstrom wrote:
> I'd like to use transactions in the SQLTransformer and tried something
> like:
>
> ...
> <execute-query>
> <query>
> BEGIN;
> </query>
> </execute-query>
> <execute-query>
> <query>
> Do something
> </query>
> </execute-query>
> <execute-query>
> <query>
> Do something that is based on the previous query
> </query>
> </execute-query>
> <execute-query>
> <query>
> COMMIT;
> </query>
> </execute-query>
> ...
>
> This does not work in the current implementation of the SQLTransformer
> as it gets a new connection from the pool for each execute-query and
> all statements within one transactions must be run from the same
> connection. Even worse, as the pooled connections can be reused from
> another pipeline, the same transaction can continue in a completely
> unexpected place for another user. So it might work as expected for a
> single user but in unexpected ways for multiple users.
>
> One way to solve this would be to use the same connection for all
> execute-query in an input xml document. It would still be necessary to
> open new connections for embedded queries, using the ancestor
> functionality.
>
> Another solution would be to introduce a transaction tag and letting
> all queries within it use the same connection and let the start tag
> execute "connection.setAutoCommit(false)" and the end tag execute
> "connection.commit()". Embedded queries must of course still have own
> connections.
>
> Booth solutions requires some rewriting of the connection handling
> code in the SQLTransformer.
>
> As I need transactions in the application I currently work on, I have
> to modify the SQLTransformer to handle it. Is there any problems with
> the above proposed methods? Which one do you prefer? Are there better
> methods?
I consider that getting a connection from the pool for each
<execute-query> creates some unnececessary load on the pool. So I would
go for the first solution (use the same connection).
Other thoughts from the database specialists out there ?
Sylvain
--
Sylvain Wallez Anyware Technologies
http://www.apache.org/~sylvain http://www.anyware-tech.com
{ XML, Java, Cocoon, OpenSource }*{ Training, Consulting, Projects }
---------------------------------------------------------------------
To unsubscribe, e-mail: cocoon-dev-unsubscribe@xml.apache.org
For additional commands, email: cocoon-dev-help@xml.apache.org