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.

Transactions

13 posts in General Discussion Last posting was on 2011-08-18 21:34:13.0Z
RGS Posted on 2011-07-28 16:46:32.0Z
Sender: 1e9e.4e3188aa.1804289383@sybase.com
From: RGS
Newsgroups: sybase.public.ase.general
Subject: Transactions
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4e319268.2456.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 28 Jul 2011 09:46:32 -0700
X-Trace: forums-1-dub 1311871592 10.22.241.41 (28 Jul 2011 09:46:32 -0700)
X-Original-Trace: 28 Jul 2011 09:46:32 -0700, 10.22.241.41
Lines: 21
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30388
Article PK: 72567

Hi!

I need help with this commands:

Is the command:

while (@@trancount > 0) rollback tran

Equal to:

rollback tran ?
----------------------------
The command:

while (@@trancount > 0) commit tran

Will be incorrect in general, because it close transactions
opened by external programs?


Thanks!


Rob V [ Sybase ] Posted on 2011-07-28 17:11:11.0Z
From: "Rob V [ Sybase ]" <rob@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY>
Reply-To: rob@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY
Organization: Sypron BV / TeamSybase / Sybase
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.18) Gecko/20110616 Lightning/1.0b2 Thunderbird/3.1.11
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Transactions
References: <4e319268.2456.1681692777@sybase.com>
In-Reply-To: <4e319268.2456.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4e31982f$1@forums-1-dub>
Date: 28 Jul 2011 10:11:11 -0700
X-Trace: forums-1-dub 1311873071 10.22.241.152 (28 Jul 2011 10:11:11 -0700)
X-Original-Trace: 28 Jul 2011 10:11:11 -0700, vip152.sybase.com
Lines: 44
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30389
Article PK: 72568


On 28-Jul-2011 18:46, RGS wrote:
> Hi!
>
> I need help with this commands:
>
> Is the command:
>
> while (@@trancount> 0) rollback tran
>
> Equal to:
>
> rollback tran ?
> ----------------------------
> The command:
>
> while (@@trancount> 0) commit tran
>
> Will be incorrect in general, because it close transactions
> opened by external programs?
>
>
> Thanks!

"while (@@trancount> 0) rollback tran" will perform a rollback. And indeed you don't need the 'while(...)': one individual rollback is sufficient.

"while (@@trancount> 0) commit tran" reduces the transaction nesting level until the outermost level is reached and the transaction really commits. But this is a somewhat strange coding style and it begs the question what the underlying idea was (I can think of some).

HTH,

Rob V.
-----------------------------------------------------------------
Rob Verschoor

Certified Sybase Professional DBA for ASE 15.0/12.5/12.0/11.5/11.0
and Replication Server 15.0.1/12.5 // TeamSybase

Author of Sybase books (order online at www.sypron.nl/shop):
"Tips, Tricks& Recipes for Sybase ASE" (ASE 15 edition)
"The Complete Sybase ASE Quick Reference Guide"
"The Complete Sybase Replication Server Quick Reference Guide"

rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter: @rob_verschoor
Sypron B.V., The Netherlands | Chamber of Commerce 27138666
-----------------------------------------------------------------


jobless Posted on 2011-07-28 17:24:59.0Z
Sender: 7c02.4e2eda29.1804289383@sybase.com
From: jobless
Newsgroups: sybase.public.ase.general
Subject: Re: Transactions
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4e319b6b.27f0.1681692777@sybase.com>
References: <4e319268.2456.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 28 Jul 2011 10:24:59 -0700
X-Trace: forums-1-dub 1311873899 10.22.241.41 (28 Jul 2011 10:24:59 -0700)
X-Original-Trace: 28 Jul 2011 10:24:59 -0700, 10.22.241.41
Lines: 34
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30390
Article PK: 72574


> Hi!
>
> I need help with this commands:
>
> Is the command:
>
> while (@@trancount > 0) rollback tran
>
> Equal to:
>
> rollback tran ?

as i recall, rollback tran rolls back to the first begin
tran so the above cmd is right.


> ----------------------------
> The command:
>
> while (@@trancount > 0) commit tran
>
> Will be incorrect in general, because it close
> transactions opened by external programs?
>

@@trancount is session variable, begin tran, commit tran are
session specific cmds, why would a commit tran close
external programs?
the syntax "while (@@trancount > 0) commit tran" look ok and
will work and commit everything for the session it is
running from, i dont think it will play with begin tran from
any other session and start terminating other external
programs(or i may have not understood your query :D quite
possible)


"Mark A. Parsons" <iron_horse Posted on 2011-07-28 22:09:41.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.13) Gecko/20101207 Lightning/1.0b2 Thunderbird/3.1.7
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Transactions
References: <4e319b6b.27f0.1681692777@sybase.com> <4e31ad1f.30e5.1681692777@sybase.com>
In-Reply-To: <4e31ad1f.30e5.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4e31de25$1@forums-1-dub>
Date: 28 Jul 2011 15:09:41 -0700
X-Trace: forums-1-dub 1311890981 10.22.241.152 (28 Jul 2011 15:09:41 -0700)
X-Original-Trace: 28 Jul 2011 15:09:41 -0700, vip152.sybase.com
Lines: 92
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30403
Article PK: 72589

While I understand what you're getting at, you may want to use some different terminology to keep from confusing the
newsgroup readers ...

I'm assuming by 'my program' you mean a stored proc. And inside this stored proc you have (or are proposing to include)
the while/@@trancount looping constructs.

By 'external program' I'm assuming you mean any parent process (ie, SQL batch, parent stored proc proc, parent trigger)
that calls your stored proc (that contains the while/@@trancount loop).

--------

@@trancount is a session level variable. Its value spans/crosses proc and trigger boundaries, ie, transactions
span/cross proc and trigger boundaries.

Regardless of your nesting level, @@trancount gets incremented each time you issue a 'begin tran'.

@@trancount will also get incremented for each INSERT/UPDATE/DELETE that fires and which causes a trigger to fire (ie,
the trigger will see @@trancount increased by 1; @@trancount will decrease by 1 when the trigger exits).

Regardless of your nesting level, 'commit tran' will decrement @@trancount by 1.

Regardless of your nesting level, 'rollback tran' will rollback *all* open transactions, ie, when the rollback is
complete you'll find @@trancount = 0.

-------

In your stored proc you've proposed adding some while/@@trancount loops, the 'commit' code should commit until
@@trancount = 0, while the 'rollback' code (only need 1 'rollback tran' command) will rollback everything until
@@trancount = 0.

If the stored proc is called while inside a transaction (ie, @@trancount > 0 at the beginning of the stored proc), then
you're while/commit (or rollback) logic will insure @@trancount = 0 when you leave the stored proc ... and the parent
process will have no way to control the transaction it started before your stored proc was called (ie, there will be no
transactions for the parent process to commit/rollback).

------

All of the above is easy enough to figure out if you just try it ("When in doubt, try it out!").

On 07/28/2011 14:40, RGS wrote:
> Thanks a lot!
>
> When I said "close external programs" I mean that if a
> external program execute my program in the way:
>
> begin tran
>
> exec my program
>
> ...
>
> commit tran
>
> Then, this external program loose the control if it need to
> do a rollback tran after exec my program
>
>
>>> Hi!
>>>
>>> I need help with this commands:
>>>
>>> Is the command:
>>>
>>> while (@@trancount> 0) rollback tran
>>>
>>> Equal to:
>>>
>>> rollback tran ?
>>
>> as i recall, rollback tran rolls back to the first begin
>> tran so the above cmd is right.
>>
>>
>>> ----------------------------
>>> The command:
>>>
>>> while (@@trancount> 0) commit tran
>>>
>>> Will be incorrect in general, because it close
>>> transactions opened by external programs?
>>>
>>
>> @@trancount is session variable, begin tran, commit tran
>> are session specific cmds, why would a commit tran close
>> external programs?
>> the syntax "while (@@trancount> 0) commit tran" look ok
>> and will work and commit everything for the session it is
>> running from, i dont think it will play with begin tran
>> from any other session and start terminating other
>> external programs(or i may have not understood your query
>> :D quite possible)