Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

AUTONOMOUS TRANSACTION

9 posts in Product Futures Discussion Last posting was on 2002-08-13 20:07:33.0Z
M. Searer Posted on 2002-08-12 20:32:03.0Z
From: "M. Searer" <nospam@nospam.com>
Subject: AUTONOMOUS TRANSACTION
Date: Mon, 12 Aug 2002 16:32:03 -0400
Lines: 19
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.50.4807.1700
X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4807.1700
Message-ID: <Fpp42DkQCHA.298@forums.sybase.com>
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: 65-84-11-167.client.dsl.net 65.84.11.167
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:329
Article PK: 93501

Oracle has the AUTONOMOUS TRANSACTION
which creates a new separate transaction within existing transactions.
It is usable within stored procedures, functions, packages, and triggers.

What is great about it is that if you rollback the 'outer' transaction, it
does not affect the separate AUTONOMOUS TRANSACTION.
The autonomous transaction is exactly that - separate and standalone.

For generating incrementing numbers using an incrementing table, this
provides a very fast processing as it does not hold the locks during the
outer transactions, just the during autonomous transaction.

Also useful for logging to tables even when the outer transaction fails.

Anyway, it would be a great enhancement to ASE (ASA as well).


Bret Halford Posted on 2002-08-12 20:38:14.0Z
Message-ID: <3D581CB6.206E2B09@sybase.com>
Date: Mon, 12 Aug 2002 14:38:14 -0600
From: Bret Halford <bret@sybase.com>
Organization: Sybase, Inc.
X-Mailer: Mozilla 4.76 [en] (Windows NT 5.0; U)
X-Accept-Language: en
MIME-Version: 1.0
Subject: Re: AUTONOMOUS TRANSACTION
References: <Fpp42DkQCHA.298@forums.sybase.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.ase.product_futures_discussion
Lines: 26
NNTP-Posting-Host: 157.133.80.136
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:328
Article PK: 93497

There is an existing open feature request for this, CR 270817.

Traditional rpcs can be used to some degree to accomplish this behavior
(but rpcs handled by the new CIS rpc handler are fully transactional).

-bret

"M. Searer" wrote:
>
> Oracle has the AUTONOMOUS TRANSACTION
> which creates a new separate transaction within existing transactions.
> It is usable within stored procedures, functions, packages, and triggers.
>
> What is great about it is that if you rollback the 'outer' transaction, it
> does not affect the separate AUTONOMOUS TRANSACTION.
> The autonomous transaction is exactly that - separate and standalone.
>
> For generating incrementing numbers using an incrementing table, this
> provides a very fast processing as it does not hold the locks during the
> outer transactions, just the during autonomous transaction.
>
> Also useful for logging to tables even when the outer transaction fails.
>
> Anyway, it would be a great enhancement to ASE (ASA as well).


Pablo Sanchez Posted on 2002-08-13 03:44:22.0Z
From: "Pablo Sanchez" <pablo@dev.null>
References: <Fpp42DkQCHA.298@forums.sybase.com>
Subject: Re: AUTONOMOUS TRANSACTION
Date: Mon, 12 Aug 2002 21:44:22 -0600
Lines: 41
Organization: High-Performance Database Engineering
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2600.0000
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000
Message-ID: <nIFl61nQCHA.298@forums.sybase.com>
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: host-1-221.ionsky.com 12.151.58.221
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:327
Article PK: 93500


"M. Searer" <nospam@nospam.com> wrote in message
news:Fpp42DkQCHA.298@forums.sybase.com...
> Oracle has the AUTONOMOUS TRANSACTION
> which creates a new separate transaction within existing
transactions.
> It is usable within stored procedures, functions, packages, and
triggers.
>
> What is great about it is that if you rollback the 'outer'
transaction, it
> does not affect the separate AUTONOMOUS TRANSACTION.
> The autonomous transaction is exactly that - separate and
standalone.
>
> For generating incrementing numbers using an incrementing table,
this
> provides a very fast processing as it does not hold the locks
during the
> outer transactions, just the during autonomous transaction.

Why wouldn't you use a SEQUENCE table instead? That would be
tremendously faster than an incrementing table.

> Also useful for logging to tables even when the outer transaction
fails.
>
> Anyway, it would be a great enhancement to ASE (ASA as well).

FWIW, you can mimic this behavior within T-SQL by using flags. I
don't mean to take away from your request in anyway, just wanted to
let ya know.

Thx!
--
Pablo Sanchez, High-Performance Database Engineering
mailto:pablo@hpdbe.com
http://www.hpdbe.com
Available for short-term and long-term contracts


M. Searer Posted on 2002-08-13 13:55:45.0Z
From: "M. Searer" <nospam@nospam.com>
References: <Fpp42DkQCHA.298@forums.sybase.com> <nIFl61nQCHA.298@forums.sybase.com>
Subject: Re: AUTONOMOUS TRANSACTION
Date: Tue, 13 Aug 2002 09:55:45 -0400
Lines: 62
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.50.4807.1700
X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4807.1700
Message-ID: <4AJJELtQCHA.252@forums.sybase.com>
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: 65-84-11-167.client.dsl.net 65.84.11.167
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:326
Article PK: 93496

I agree that for applications that are written to a single database, then
taking advantage of things like sequences/identity is the best approach.
For applications that support multiple database back ends, then using your
own incrementing tables is often the only approach to having a standard
number incrementing method.

For existing applications that were written before sequences and identities,
it is sometimes not feasible to rewrite to take advantage of identities and
sequences. Simply adding the autonomous transaction is very easy as the
incrementing logic typically is centralized in an object/function. Upgrade
the database, and implement a fairly simple code change, and BOOM instant
performance increase.

Plus, the autonomous transaction still provides additional capabilities in
things like trigger based logging even on main transactional rollbacks.

"Pablo Sanchez" <pablo@dev.null> wrote in message
news:nIFl61nQCHA.298@forums.sybase.com...
>
> "M. Searer" <nospam@nospam.com> wrote in message
> news:Fpp42DkQCHA.298@forums.sybase.com...
> > Oracle has the AUTONOMOUS TRANSACTION
> > which creates a new separate transaction within existing
> transactions.
> > It is usable within stored procedures, functions, packages, and
> triggers.
> >
> > What is great about it is that if you rollback the 'outer'
> transaction, it
> > does not affect the separate AUTONOMOUS TRANSACTION.
> > The autonomous transaction is exactly that - separate and
> standalone.
> >
> > For generating incrementing numbers using an incrementing table,
> this
> > provides a very fast processing as it does not hold the locks
> during the
> > outer transactions, just the during autonomous transaction.
>
> Why wouldn't you use a SEQUENCE table instead? That would be
> tremendously faster than an incrementing table.
>
> > Also useful for logging to tables even when the outer transaction
> fails.
> >
> > Anyway, it would be a great enhancement to ASE (ASA as well).
>
> FWIW, you can mimic this behavior within T-SQL by using flags. I
> don't mean to take away from your request in anyway, just wanted to
> let ya know.
>
> Thx!
> --
> Pablo Sanchez, High-Performance Database Engineering
> mailto:pablo@hpdbe.com
> http://www.hpdbe.com
> Available for short-term and long-term contracts
>
>


Pablo Sanchez Posted on 2002-08-13 20:05:46.0Z
From: "Pablo Sanchez" <pablo@dev.null>
References: <Fpp42DkQCHA.298@forums.sybase.com> <nIFl61nQCHA.298@forums.sybase.com> <4AJJELtQCHA.252@forums.sybase.com>
Subject: Re: AUTONOMOUS TRANSACTION
Date: Tue, 13 Aug 2002 14:05:46 -0600
Lines: 54
Organization: High-Performance Database Engineering
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2600.0000
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000
Message-ID: <Muw1UawQCHA.198@forums.sybase.com>
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: host-1-221.ionsky.com 12.151.58.221
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:322
Article PK: 93489


"M. Searer" <nospam@nospam.com> wrote in message
news:4AJJELtQCHA.252@forums.sybase.com...
> I agree that for applications that are written to a single database,
then
> taking advantage of things like sequences/identity is the best
approach.
> For applications that support multiple database back ends, then
using your
> own incrementing tables is often the only approach to having a
standard
> number incrementing method.

I see your point. I might elect to have one DB that controls the
sequence/identities.

> For existing applications that were written before sequences and
identities,
> it is sometimes not feasible to rewrite to take advantage of
identities and
> sequences. Simply adding the autonomous transaction is very easy as
the
> incrementing logic typically is centralized in an object/function.
Upgrade
> the database, and implement a fairly simple code change, and BOOM
instant
> performance increase.

The problem is that you generate additional log so you push the
problem to the log -- sequences are minimally logged.

I do agree that legacy systems have the issue. For legacy
applications, what I've done is create a single sequence table with
blocks of available ID's that map to a SPID. I use a sentinel value
in the table (spid value 0) to store the next block of ID's. I use
row level locking on the sequence table and each SPID grabs the next
ID without blocking other SPID's. <g>

> Plus, the autonomous transaction still provides additional
capabilities in
> things like trigger based logging even on main transactional
rollbacks.

This is true... albeit in practice, is it ever used? I would think
it'd be super slow to add this overhead in a trigger.

Thx!
--
Pablo Sanchez, High-Performance Database Engineering
mailto:pablo@hpdbe.com
http://www.hpdbe.com
Available for short-term and long-term contracts


Anthony Mandic Posted on 2002-08-13 14:39:55.0Z
Message-ID: <3D591A3B.A58EF7E4@start.com.au>
Date: Wed, 14 Aug 2002 00:39:55 +1000
From: Anthony Mandic <am_is_not@start.com.au>
Organization: Mandic Consulting Pty. Ltd.
X-Mailer: Mozilla 4.61 [en] (WinNT; I)
MIME-Version: 1.0
Subject: Re: AUTONOMOUS TRANSACTION
References: <Fpp42DkQCHA.298@forums.sybase.com> <nIFl61nQCHA.298@forums.sybase.com> <4AJJELtQCHA.252@forums.sybase.com>
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit
Newsgroups: sybase.public.ase.product_futures_discussion
Lines: 37
NNTP-Posting-Host: 203.3.176.10
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:325
Article PK: 93495


"M. Searer" wrote:
>
> I agree that for applications that are written to a single database, then
> taking advantage of things like sequences/identity is the best approach.
> For applications that support multiple database back ends, then using your
> own incrementing tables is often the only approach to having a standard
> number incrementing method.

Given the numerous and incompatible SQL dialects there's not
much point using this level of abstraction since you can't do
it for everything. You may as well use what's available and
maintain different sources with version control.

> For existing applications that were written before sequences and identities,
> it is sometimes not feasible to rewrite to take advantage of identities and
> sequences. Simply adding the autonomous transaction is very easy as the
> incrementing logic typically is centralized in an object/function.

You could easily argue this the other way around too.

> Upgrade the database, and implement a fairly simple code change, and BOOM
> instant performance increase.

That's debatable too.

> Plus, the autonomous transaction still provides additional capabilities in
> things like trigger based logging even on main transactional rollbacks.

I suppose at this point, we should discuss transactional consistency.
It seems to me that you are bending over and aiming at your foot. Is
the safety on or off? Only one way to find out.

-am © 2002


M. Searer Posted on 2002-08-13 15:39:10.0Z
From: "M. Searer" <nospam@nospam.com>
References: <Fpp42DkQCHA.298@forums.sybase.com> <nIFl61nQCHA.298@forums.sybase.com> <4AJJELtQCHA.252@forums.sybase.com> <3D591A3B.A58EF7E4@start.com.au>
Subject: Re: AUTONOMOUS TRANSACTION
Date: Tue, 13 Aug 2002 11:39:10 -0400
Lines: 89
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.50.4807.1700
X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4807.1700
Message-ID: <q3mV3EuQCHA.298@forums.sybase.com>
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: 65-84-11-167.client.dsl.net 65.84.11.167
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:324
Article PK: 93493


"Anthony Mandic" <am_is_not@start.com.au> wrote in message
news:3D591A3B.A58EF7E4@start.com.au...
> "M. Searer" wrote:
> >
> > I agree that for applications that are written to a single database,
then
> > taking advantage of things like sequences/identity is the best approach.
> > For applications that support multiple database back ends, then using
your
> > own incrementing tables is often the only approach to having a standard
> > number incrementing method.
>
> Given the numerous and incompatible SQL dialects there's not
> much point using this level of abstraction since you can't do
> it for everything. You may as well use what's available and
> maintain different sources with version control.

eh? Most incrementing table logic that I have seen is handled with a single
table with a row per incrementor and a stored procedure/function to handle
incrementing and returning the value. Each database (sybase, oracle, ....)
does have its own separate stored procedure, but that is one stored
procedure per database to handle all incrementing chores.

So, in these cases, the 'difference' is maintained in stored
procedures/functions and is simply whether the transaction is autonomous or
not.
The increment logic is identical in all cases: x = x+ increment_value



>
> > For existing applications that were written before sequences and
identities,
> > it is sometimes not feasible to rewrite to take advantage of identities
and
> > sequences. Simply adding the autonomous transaction is very easy as the
> > incrementing logic typically is centralized in an object/function.
>
> You could easily argue this the other way around too.

It took me 1/2 an hour to change the stored function in oracle to take
advantage of this.
However, if you don't have your incrementing logic and incrementing table
structure setup very well, then I agree the difference in making the changes
might not be worth it. Unless you are dealing with more than one
database...

For pure performance, the identity/sequence method should be faster in any
case. For in-house only applications, that alone may be reason as to why to
change over to a 'native' sequence/identity generator.

>
> > Upgrade the database, and implement a fairly simple code change, and
BOOM
> > instant performance increase.
>
> That's debatable too.
>
> > Plus, the autonomous transaction still provides additional capabilities
in
> > things like trigger based logging even on main transactional rollbacks.
>
> I suppose at this point, we should discuss transactional consistency.
> It seems to me that you are bending over and aiming at your foot. Is
> the safety on or off? Only one way to find out.
>
> -am © 2002

Transactional consistency? The point is that the requirement is for the
change to be logged whether or not it committed. I'm not saying that
every application that does logging would need this, but I have seen
requirements for this type of stuff.

It seems you are intent on having reasons why not to have this type of
feature in Sybase. If you don't need it then you wouldn't use it.

I have started using it in Oracle, and in my situation with the applications
I deal with, I have no good alternative in Sybase. If my customers need
these capabilities, I have to recommend Oracle. All things equal, I would
rather recommend Sybase.


Anthony Mandic Posted on 2002-08-13 16:40:32.0Z
Message-ID: <3D593680.EF9E53EC@start.com.au>
Date: Wed, 14 Aug 2002 02:40:32 +1000
From: Anthony Mandic <am_is_not@start.com.au>
Organization: Mandic Consulting Pty. Ltd.
X-Mailer: Mozilla 4.61 [en] (WinNT; I)
MIME-Version: 1.0
Subject: Re: AUTONOMOUS TRANSACTION
References: <Fpp42DkQCHA.298@forums.sybase.com> <nIFl61nQCHA.298@forums.sybase.com> <4AJJELtQCHA.252@forums.sybase.com> <3D591A3B.A58EF7E4@start.com.au> <q3mV3EuQCHA.298@forums.sybase.com>
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit
Newsgroups: sybase.public.ase.product_futures_discussion
Lines: 95
NNTP-Posting-Host: 203.3.176.10
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:323
Article PK: 93490


"M. Searer" wrote:

> > Given the numerous and incompatible SQL dialects there's not
> > much point using this level of abstraction since you can't do
> > it for everything. You may as well use what's available and
> > maintain different sources with version control.
>
> eh? Most incrementing table logic that I have seen is handled with a single
> table with a row per incrementor and a stored procedure/function to handle
> incrementing and returning the value. Each database (sybase, oracle, ....)
> does have its own separate stored procedure, but that is one stored
> procedure per database to handle all incrementing chores.

My point was not about the generation of surrogate primary keys,
per se, but about the overall differences of SQL dialects. Since
you are maintaining separate stored procedures you may as well hide
dialectic differences there rather than try and maintain some
semblance of consistency there as well.

> So, in these cases, the 'difference' is maintained in stored
> procedures/functions and is simply whether the transaction is autonomous or
> not.
> The increment logic is identical in all cases: x = x+ increment_value

I would be curious to see if you are doing this correctly
within ASE.

> > You could easily argue this the other way around too.
>
> It took me 1/2 an hour to change the stored function in oracle to take
> advantage of this.

It would have taken less time to not write an sproc and use an
identity field or sequence or whatever.

> However, if you don't have your incrementing logic and incrementing table
> structure setup very well, then I agree the difference in making the changes
> might not be worth it. Unless you are dealing with more than one
> database...

Of all the third party products I've had to deal with that support
multiple backends, the quality is always the same. The schema and
code is geared to the lowest common denominator. Some (like ones
using Uniface) are even worse. You need to draw a line somewhere.

> For pure performance, the identity/sequence method should be faster in any
> case. For in-house only applications, that alone may be reason as to why to
> change over to a 'native' sequence/identity generator.

Yes, the chief issue is the trade-off between performance and
consistency. You can't really have both.

> Transactional consistency? The point is that the requirement is for the
> change to be logged whether or not it committed. I'm not saying that
> every application that does logging would need this, but I have seen
> requirements for this type of stuff.

Yes, I understand this but the issues may be more wide spread
than you may be aware of.

> It seems you are intent on having reasons why not to have this type of
> feature in Sybase. If you don't need it then you wouldn't use it.

That's correct. If there is an alternative open to you, you
should also consider using it. For every useless feature or
seldom used feature that gets added, the size of the codeline
grows and resources are taken away from the areas where they
are really needed. I would prefer that Sybase's engeneers
stear the car in the same direction and at a consistent
speed rather than turning it into a bus and stopping at
every point and driving all over the place only to arrive
at the same destination.

> I have started using it in Oracle, and in my situation with the applications
> I deal with, I have no good alternative in Sybase. If my customers need
> these capabilities, I have to recommend Oracle. All things equal, I would
> rather recommend Sybase.

So would I but I'd like to see the code you've developed now
to confirm some suspicions.

-am © 2002


Pablo Sanchez Posted on 2002-08-13 20:07:33.0Z
From: "Pablo Sanchez" <pablo@dev.null>
References: <Fpp42DkQCHA.298@forums.sybase.com> <nIFl61nQCHA.298@forums.sybase.com> <4AJJELtQCHA.252@forums.sybase.com> <3D591A3B.A58EF7E4@start.com.au> <q3mV3EuQCHA.298@forums.sybase.com>
Subject: Re: AUTONOMOUS TRANSACTION
Date: Tue, 13 Aug 2002 14:07:33 -0600
Lines: 25
Organization: High-Performance Database Engineering
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2600.0000
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000
Message-ID: <teawUbwQCHA.259@forums.sybase.com>
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: host-1-221.ionsky.com 12.151.58.221
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:321
Article PK: 93491


"M. Searer" <nospam@nospam.com> wrote in message
news:q3mV3EuQCHA.298@forums.sybase.com...
>
> I have started using it in Oracle, and in my situation with the
applications
> I deal with, I have no good alternative in Sybase. If my customers
need
> these capabilities, I have to recommend Oracle. All things equal, I
would
> rather recommend Sybase.

Would you provide an example of what your intent is and we as a group
can give you a hand on how we'd implement it. I've seen the request
before soooooooooooo if we could provide a work-around until it's
implemented, it'll help ya out. Right?

Thx!
--
Pablo Sanchez, High-Performance Database Engineering
mailto:pablo@hpdbe.com
http://www.hpdbe.com
Available for short-term and long-term contracts