You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@cayenne.apache.org by Evgeny Ryabitskiy <ev...@gmail.com> on 2009/10/27 16:54:41 UTC
Run query NOT in transaction
Hello 2 everyone!
I have one problem with transaction policy in Cayenne.
I need to run some query without transaction. Why? I am using
Temporary Tables in T-SQL which can't be created in Transaction.
But Cayenne using transaction for every query.
Here is example:
If I run this Query:
Create Table #MYTempTable (someid int)
Insert Into #MYTempTable (someid) Select 0
I got:
java.sql.SQLException: There was a transaction active when exiting
the stored procedure '****'. The temporary table '#MYTempTable' was
dropped in this transaction either explicitly or implicitly. This
transaction has been aborted to prevent database corruption.
After some research I didn't find any ability to turn off transaction
for query. Maybe it will be nice to add some API parameter to change
this behavior? )
Evgeny Ryabitskiy.
Re: Run query NOT in transaction
Posted by Robert Zeigler <ro...@roxanemy.com>.
I've used explicit transactions 1x, for a very specific use-case that
required raw sql updates and selects for performance reasons.
So I would say for me, that makes it about 99.9999% that I use
implicit transactions.
Robert
On Oct 28, 2009, at 10/288:45 AM , Mike Kienenberger wrote:
> I've never used anything but implicit transaction management. I never
> knew there was such a thing until the last couple of years :)
>
> On Wed, Oct 28, 2009 at 9:43 AM, Michael Gentry
> <mg...@masslight.net> wrote:
>> For me, and probably every other developer here, it would be > 99%
>> of the
>> time...
>>
>> On Tue, Oct 27, 2009 at 12:29 PM, Andrus Adamchik <andrus@objectstyle.org
>> >wrote:
>>
>>> I disagree with the general statement. Implicit transaction
>>> mechanism is
>>> actually the best part of Cayenne 99% of the time compared to
>>> alternatives.
>>> I.e. you don't have to manage transaction at all.
>>>
>>
Re: Run query NOT in transaction
Posted by Mike Kienenberger <mk...@gmail.com>.
I've never used anything but implicit transaction management. I never
knew there was such a thing until the last couple of years :)
On Wed, Oct 28, 2009 at 9:43 AM, Michael Gentry <mg...@masslight.net> wrote:
> For me, and probably every other developer here, it would be > 99% of the
> time...
>
> On Tue, Oct 27, 2009 at 12:29 PM, Andrus Adamchik <an...@objectstyle.org>wrote:
>
>> I disagree with the general statement. Implicit transaction mechanism is
>> actually the best part of Cayenne 99% of the time compared to alternatives.
>> I.e. you don't have to manage transaction at all.
>>
>
Re: Run query NOT in transaction
Posted by Michael Gentry <mg...@masslight.net>.
For me, and probably every other developer here, it would be > 99% of the
time...
On Tue, Oct 27, 2009 at 12:29 PM, Andrus Adamchik <an...@objectstyle.org>wrote:
> I disagree with the general statement. Implicit transaction mechanism is
> actually the best part of Cayenne 99% of the time compared to alternatives.
> I.e. you don't have to manage transaction at all.
>
Re: Run query NOT in transaction
Posted by Andrus Adamchik <an...@objectstyle.org>.
On Oct 27, 2009, at 6:15 PM, Evgeny Ryabitskiy wrote:
> The problem that as usual I need to turn it off only in some
> "special cases".
> Usual I am using InternalTransaction API.
> Using of ProcedureQueries is not a solution in my case, we prefer
> common RowQuery. :)
Sure, this is applicable to any type of query though.
> It' a solution to replace InternalTransaction by ExternalTransaction
> (which actually do nothing) in such "special cases", so it' helps.
> I just got this solution in mind. But it's not so intuitive... and
> really ugly...
Good to know that the workaround helps you.
> I will add issue on 3.1 Transaction mechanism is not a best part of
> Cayenne .... :(
I both agree and disagree.
I disagree with the general statement. Implicit transaction mechanism
is actually the best part of Cayenne 99% of the time compared to
alternatives. I.e. you don't have to manage transaction at all.
I agree that in such edge cases it can and should do better.
Andrus
Re: Run query NOT in transaction
Posted by Evgeny Ryabitskiy <ev...@gmail.com>.
The problem that as usual I need to turn it off only in some "special cases".
Usual I am using InternalTransaction API.
Using of ProcedureQueries is not a solution in my case, we prefer
common RowQuery. :)
It' a solution to replace InternalTransaction by ExternalTransaction
(which actually do nothing) in such "special cases", so it' helps.
I just got this solution in mind. But it's not so intuitive... and
really ugly...
I will add issue on 3.1 Transaction mechanism is not a best part of
Cayenne .... :(
Evgeny.
> 2009/10/27 Andrus Adamchik <an...@objectstyle.org>:
>> Yeah, quite annoying. Here is a workaround:
>>
>> http://cayenne.apache.org/doc20/stored-procedures-and-transactions.html
>>
>> Andrus
>>
>> On Oct 27, 2009, at 5:54 PM, Evgeny Ryabitskiy wrote:
>>
>>> Hello 2 everyone!
>>>
>>> I have one problem with transaction policy in Cayenne.
>>> I need to run some query without transaction. Why? I am using
>>> Temporary Tables in T-SQL which can't be created in Transaction.
>>> But Cayenne using transaction for every query.
>>>
>>> Here is example:
>>>
>>> If I run this Query:
>>>
>>> Create Table #MYTempTable (someid int)
>>> Insert Into #MYTempTable (someid) Select 0
>>>
>>> I got:
>>> java.sql.SQLException: There was a transaction active when exiting
>>> the stored procedure '****'. The temporary table '#MYTempTable' was
>>> dropped in this transaction either explicitly or implicitly. This
>>> transaction has been aborted to prevent database corruption.
>>>
>>>
>>> After some research I didn't find any ability to turn off transaction
>>> for query. Maybe it will be nice to add some API parameter to change
>>> this behavior? )
>>>
>>> Evgeny Ryabitskiy.
>>>
>>
>>
>
Re: Run query NOT in transaction
Posted by "Eugene R." <ap...@gmail.com>.
The problem that as usual I need to turn it off only in some "special cases".
Usual I am using InternalTransaction API.
Using of ProcedureQueries is not a solution in my case, we prefer
common RowQuery. :)
It' a solution to replace InternalTransaction by ExternalTransaction
(which actually do nothing) in such "special cases", so it' helps.
I just got this solution in mind. But it's not so intuitive... and
really ugly...
I will add issue on 3.1 Transaction mechanism is not a best part of
Cayenne .... :(
Evgeny.
2009/10/27 Andrus Adamchik <an...@objectstyle.org>:
> Yeah, quite annoying. Here is a workaround:
>
> http://cayenne.apache.org/doc20/stored-procedures-and-transactions.html
>
> Andrus
>
> On Oct 27, 2009, at 5:54 PM, Evgeny Ryabitskiy wrote:
>
>> Hello 2 everyone!
>>
>> I have one problem with transaction policy in Cayenne.
>> I need to run some query without transaction. Why? I am using
>> Temporary Tables in T-SQL which can't be created in Transaction.
>> But Cayenne using transaction for every query.
>>
>> Here is example:
>>
>> If I run this Query:
>>
>> Create Table #MYTempTable (someid int)
>> Insert Into #MYTempTable (someid) Select 0
>>
>> I got:
>> java.sql.SQLException: There was a transaction active when exiting
>> the stored procedure '****'. The temporary table '#MYTempTable' was
>> dropped in this transaction either explicitly or implicitly. This
>> transaction has been aborted to prevent database corruption.
>>
>>
>> After some research I didn't find any ability to turn off transaction
>> for query. Maybe it will be nice to add some API parameter to change
>> this behavior? )
>>
>> Evgeny Ryabitskiy.
>>
>
>
Re: Run query NOT in transaction
Posted by Andrus Adamchik <an...@objectstyle.org>.
Yeah, quite annoying. Here is a workaround:
http://cayenne.apache.org/doc20/stored-procedures-and-transactions.html
Andrus
On Oct 27, 2009, at 5:54 PM, Evgeny Ryabitskiy wrote:
> Hello 2 everyone!
>
> I have one problem with transaction policy in Cayenne.
> I need to run some query without transaction. Why? I am using
> Temporary Tables in T-SQL which can't be created in Transaction.
> But Cayenne using transaction for every query.
>
> Here is example:
>
> If I run this Query:
>
> Create Table #MYTempTable (someid int)
> Insert Into #MYTempTable (someid) Select 0
>
> I got:
> java.sql.SQLException: There was a transaction active when exiting
> the stored procedure '****'. The temporary table '#MYTempTable' was
> dropped in this transaction either explicitly or implicitly. This
> transaction has been aborted to prevent database corruption.
>
>
> After some research I didn't find any ability to turn off transaction
> for query. Maybe it will be nice to add some API parameter to change
> this behavior? )
>
> Evgeny Ryabitskiy.
>