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.

What is a practical use of a savepoint?

14 posts in General Discussion Last posting was on 2009-07-22 01:52:03.0Z
John Flynn Posted on 2009-07-13 19:38:28.0Z
From: "John Flynn" <jflynn@miqs.com>
Newsgroups: sybase.public.ase.general
Subject: What is a practical use of a savepoint?
Lines: 30
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5512
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
X-RFC2646: Format=Flowed; Original
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4a5b8d34@forums-3-dub.sybase.com>
Date: 13 Jul 2009 12:38:28 -0700
X-Trace: forums-3-dub.sybase.com 1247513908 10.22.241.152 (13 Jul 2009 12:38:28 -0700)
X-Original-Trace: 13 Jul 2009 12:38:28 -0700, vip152.sybase.com
Path: forums-1-dub!forums-master!forums-3-dub.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:27990
Article PK: 77237

Hi.

I'm pretty sure I understand mechanically how savepoints work, i.e. it lets
you rollback a transaction to a "save transaction" point without rolling
back the whole transaction. But I am having difficulty imagining a scenario
where this capability would be practically useful.

Some discussions on the Internet describe savepoints as a kind of
human-driven "partial undo" capability. E.g. before committing the whole
transaction, stop and give the human a chance to rollback to a particular
point, like I guess if he has changed his mind. But of course you shouldn't
allow user interaction while inside a transaction, so that idea seems like a
non-starter.

Savepoints seem inherently wasteful. You have to suffer the cost of the
original update followed by the cost of rolling back the update. And it
makes your transaction take longer which extends the time that your tables
stay locked. Why not do the "decision" work PRIOR to the transaction, and
then let the transaction do the necessary updates with no more decisions?
This idea of starting a transaction, doing some updates, then saying "oops"
and rolling back part of what you did, just seems half-baked to me.

Then again, I don't have much of an imagination. Are savepoints widely used?
Is there a type of scenario where people find them useful? BTW I'm on ASE15
if that matters.

Thanks.
- John.


Rob V [Sybase] Posted on 2009-07-13 19:47:12.0Z
Reply-To: "Rob V [Sybase]" <robv@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY>
From: "Rob V [Sybase]" <robv@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY>
Newsgroups: sybase.public.ase.general
References: <4a5b8d34@forums-3-dub.sybase.com>
Subject: Re: What is a practical use of a savepoint?
Lines: 60
Organization: Sypron BV / TeamSybase / Sybase Inc
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3350
X-RFC2646: Format=Flowed; Response
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4a5b8f40$1@forums-3-dub.sybase.com>
Date: 13 Jul 2009 12:47:12 -0700
X-Trace: forums-3-dub.sybase.com 1247514432 10.22.241.152 (13 Jul 2009 12:47:12 -0700)
X-Original-Trace: 13 Jul 2009 12:47:12 -0700, vip152.sybase.com
Path: forums-1-dub!forums-master!forums-3-dub.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:27991
Article PK: 77238

First, savepoints are part of the transactional semantics as defined by the
ANSI SQL standard.
There may be transactional scenarios where this type of behaviour could be
desired, but I have hardly ever seen it being used.
I agree that the practical value is very limited.

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"

mailto:rob@YOUR.SPAM.sypron.nl.NOT.FOR.ME
http://www.sypron.nl
Sypron B.V., Amersfoort, The Netherlands
Chamber of Commerce 27138666
-----------------------------------------------------------------

"John Flynn" <jflynn@miqs.com> wrote in message
news:4a5b8d34@forums-3-dub.sybase.com...
> Hi.
>
> I'm pretty sure I understand mechanically how savepoints work, i.e. it
> lets you rollback a transaction to a "save transaction" point without
> rolling back the whole transaction. But I am having difficulty imagining a
> scenario where this capability would be practically useful.
>
> Some discussions on the Internet describe savepoints as a kind of
> human-driven "partial undo" capability. E.g. before committing the whole
> transaction, stop and give the human a chance to rollback to a particular
> point, like I guess if he has changed his mind. But of course you
> shouldn't allow user interaction while inside a transaction, so that idea
> seems like a non-starter.
>
> Savepoints seem inherently wasteful. You have to suffer the cost of the
> original update followed by the cost of rolling back the update. And it
> makes your transaction take longer which extends the time that your tables
> stay locked. Why not do the "decision" work PRIOR to the transaction, and
> then let the transaction do the necessary updates with no more decisions?
> This idea of starting a transaction, doing some updates, then saying
> "oops" and rolling back part of what you did, just seems half-baked to me.
>
> Then again, I don't have much of an imagination. Are savepoints widely
> used? Is there a type of scenario where people find them useful? BTW I'm
> on ASE15 if that matters.
>
> Thanks.
> - John.
>
>


"Mark A. Parsons" <iron_horse Posted on 2009-07-13 20:43:04.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Thunderbird 1.5.0.10 (Windows/20070221)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: What is a practical use of a savepoint?
References: <4a5b8d34@forums-3-dub.sybase.com>
In-Reply-To: <4a5b8d34@forums-3-dub.sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Antivirus: avast! (VPS 090710-0, 07/10/2009), Outbound message
X-Antivirus-Status: Clean
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4a5b9c58@forums-3-dub.sybase.com>
Date: 13 Jul 2009 13:43:04 -0700
X-Trace: forums-3-dub.sybase.com 1247517784 10.22.241.152 (13 Jul 2009 13:43:04 -0700)
X-Original-Trace: 13 Jul 2009 13:43:04 -0700, vip152.sybase.com
Lines: 106
Path: forums-1-dub!forums-master!forums-3-dub.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:27992
Article PK: 77239

I used savepoints on a regular basis, but not in a 'common' way ...

------------------

When designing triggers and stored procs I like to have the code designed so that it can stand alone, and so that it has
minimal effects on the transaction management of parent/child coding.

If I need to rollback a transaction I will only rollback my local code (by rolling back to the save point), pass an
error message and/or non-zero return status to the parent process, and assume that the parent process is handling it's
own transaction management. (For example, if my proc has to rollback a transaction ... do I really know if/how the
parent process wants to handle the rollback? do I really want to rollback a parent process's higher-level transaction?)

This means that I typically code with a framework like such:

[NOTE: This is a greatly watered-down framework.]

===========================
create proc/trigger ...

begin tran
save tran my_tran

-- begin DML processing

-- for each DML statement, if any errors or invalid
-- change in @@trancount then ...

goto ROLLBACK_NOW

-- for all DML and subordinate proc calls, check
-- transaction level, @@error, @@trancount, and
-- return status (for procs); if any issues then ...

goto ROLLBACK NOW

-- end DML processing

-- if we got here then no errors so jump forward to the commit

goto COMMIT_NOW

ROLLBACK_NOW:

-- raise an error message?
-- set a return status? (@rs)

-- rollback to our local save point

rollback tran my_tran

COMMIT_NOW:

-- now close out our local transaction

commit tran

return @rs
===========================

By using a save point I can rollback my local transaction without affecting a parent process's transaction.

If I need to rollback my local transaction then I also pass as much info (non-zero error, non-zero return status) to the
parent process as possible. It's then up to the parent process to handle it's transaction accordingly.

Obviously (?) ...

- the parent (and child) process has to be coded with good transaction management/testing

- if a child process (DML trigger, stored proc) returns to my proc with an invalid @@trancount then I have to decide
what to do in my code ... I usually raise an error message (including a HIGH PRIORITY message about invalid transaction
management by a particular subordinate piece of code) and perform a 'rollback tran' (ie, rollback everything up to this
point, including any previous transactional processing by a parent process).

John Flynn wrote:
> Hi.
>
> I'm pretty sure I understand mechanically how savepoints work, i.e. it lets
> you rollback a transaction to a "save transaction" point without rolling
> back the whole transaction. But I am having difficulty imagining a scenario
> where this capability would be practically useful.
>
> Some discussions on the Internet describe savepoints as a kind of
> human-driven "partial undo" capability. E.g. before committing the whole
> transaction, stop and give the human a chance to rollback to a particular
> point, like I guess if he has changed his mind. But of course you shouldn't
> allow user interaction while inside a transaction, so that idea seems like a
> non-starter.
>
> Savepoints seem inherently wasteful. You have to suffer the cost of the
> original update followed by the cost of rolling back the update. And it
> makes your transaction take longer which extends the time that your tables
> stay locked. Why not do the "decision" work PRIOR to the transaction, and
> then let the transaction do the necessary updates with no more decisions?
> This idea of starting a transaction, doing some updates, then saying "oops"
> and rolling back part of what you did, just seems half-baked to me.
>
> Then again, I don't have much of an imagination. Are savepoints widely used?
> Is there a type of scenario where people find them useful? BTW I'm on ASE15
> if that matters.
>
> Thanks.
> - John.
>
>


Cesar Posted on 2009-07-14 12:57:52.0Z
From: Cesar <cesar.buzzo@gmail.com>
Newsgroups: sybase.public.ase.general
Subject: Re: What is a practical use of a savepoint?
Date: Tue, 14 Jul 2009 05:57:52 -0700 (PDT)
Organization: http://groups.google.com
Lines: 144
Message-ID: <e0edb047-9386-4887-b1eb-9371a5c1bc75@o13g2000vbl.googlegroups.com>
References: <4a5b8d34@forums-3-dub.sybase.com> <4a5b9c58@forums-3-dub.sybase.com>
NNTP-Posting-Host: 161.190.1.216
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
X-Trace: posting.google.com 1247576272 21787 127.0.0.1 (14 Jul 2009 12:57:52 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Tue, 14 Jul 2009 12:57:52 +0000 (UTC)
Complaints-To: groups-abuse@google.com
Injection-Info: o13g2000vbl.googlegroups.com; posting-host=161.190.1.216; posting-account=3ouCWAoAAABsWjhXhTgShvwPVmBZX1HF
User-Agent: G2/1.0
X-HTTP-Via: 1.1 PROXY01A
X-HTTP-UserAgent: Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; IECorpBG; GTB6; .NET CLR 1.1.4322; .NET CLR 2.0.50727; IECorpBG; IECorpBG),gzip(gfe),gzip(gfe)
Path: forums-1-dub!forums-master!newssvr.sybase.com!news-sj-1.sprintlink.net!news-peer1.sprintlink.net!newsfeed.yul.equant.net!novia!news-out.readnews.com!news-xxxfer.readnews.com!postnews.google.com!o13g2000vbl.googlegroups.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:27997
Article PK: 77244

On Jul 13, 5:43 pm, "Mark A. Parsons"

<iron_horse@no_spamola.compuserve.com> wrote:
> I used savepoints on a regular basis, but not in a 'common' way ...
>
> ------------------
>
> When designing triggers and stored procs I like to have the code designed so that it can stand alone, and so that it has
> minimal effects on the transaction management of parent/child coding.
>
> If I need to rollback a transaction I will only rollback my local code (by rolling back to the save point), pass an
> error message and/or non-zero return status to the parent process, and assume that the parent process is handling it's
> own transaction management.  (For example, if my proc has to rollback a transaction ... do I really know if/how the
> parent process wants to handle the rollback?  do I really want to rollback a parent process's higher-level transaction?)
>
> This means that I typically code with a framework like such:
>
> [NOTE:  This is a greatly watered-down framework.]
>
> ===========================
> create proc/trigger ...
>
> begin tran
> save tran my_tran
>
> -- begin DML processing
>
> -- for each DML statement, if any errors or invalid
> -- change in @@trancount then ...
>
>         goto ROLLBACK_NOW
>
> -- for all DML and subordinate proc calls, check
> -- transaction level, @@error, @@trancount, and
> -- return status (for procs); if any issues then ...
>
>         goto ROLLBACK NOW
>
> -- end DML processing
>
> -- if we got here then no errors so jump forward to the commit
>
>         goto COMMIT_NOW
>
> ROLLBACK_NOW:
>
> -- raise an error message?
> -- set a return status? (@rs)
>
> -- rollback to our local save point
>
> rollback tran my_tran
>
> COMMIT_NOW:
>
> -- now close out our local transaction
>
> commit tran
>
> return @rs
> ===========================
>
> By using a save point I can rollback my local transaction without affecting a parent process's transaction.
>
> If I need to rollback my local transaction then I also pass as much info (non-zero error, non-zero return status) to the
> parent process as possible.  It's then up to the parent process to handle it's transaction accordingly.
>
> Obviously (?) ...
>
> - the parent (and child) process has to be coded with good transaction management/testing
>
> - if a child process (DML trigger, stored proc) returns to my proc with an invalid @@trancount then I have to decide
> what to do in my code ... I usually raise an error message (including a HIGH PRIORITY message about invalid transaction
> management by a particular subordinate piece of code) and perform a 'rollback tran' (ie, rollback everything up to this
> point, including any previous transactional processing by a parent process).
>
>
>
> John Flynn wrote:
> > Hi.
>
> > I'm pretty sure I understand mechanically how savepoints work, i.e. it lets
> > you rollback a transaction to a "save transaction" point without rolling
> > back the whole transaction. But I am having difficulty imagining a scenario
> > where this capability would be practically useful.
>
> > Some discussions on the Internet describe savepoints as a kind of
> > human-driven "partial undo" capability. E.g. before committing the whole
> > transaction, stop and give the human a chance to rollback to a particular
> > point, like I guess if he has changed his mind. But of course you shouldn't
> > allow user interaction while inside a transaction, so that idea seems like a
> > non-starter.
>
> > Savepoints seem inherently wasteful. You have to suffer the cost of the
> > original update followed by the cost of rolling back the update. And it
> > makes your transaction take longer which extends the time that your tables
> > stay locked. Why not do the "decision" work PRIOR to the transaction, and
> > then let the transaction do the necessary updates with no more decisions?
> > This idea of starting a transaction, doing some updates, then saying "oops"
> > and rolling back part of what you did, just seems half-baked to me.
>
> > Then again, I don't have much of an imagination. Are savepoints widely used?
> > Is there a type of scenario where people find them useful? BTW I'm on ASE15
> > if that matters.
>
> > Thanks.
> > - John.- Hide quoted text -
>
> - Show quoted text -

Mark,

yours should be taken as a best practice for sql programming. Very
useful indeed!

Thanks
César Buzzo


Jeff Garbus Posted on 2009-07-14 13:29:57.0Z
From: Jeff Garbus <jeff@soaringeagle.biz>
Newsgroups: sybase.public.ase.general
Subject: Re: What is a practical use of a savepoint?
Date: Tue, 14 Jul 2009 06:29:57 -0700 (PDT)
Organization: http://groups.google.com
Lines: 180
Message-ID: <3e77d71b-e82b-448d-a9f7-4906757f0846@j19g2000vbp.googlegroups.com>
References: <4a5b8d34@forums-3-dub.sybase.com> <4a5b9c58@forums-3-dub.sybase.com> <e0edb047-9386-4887-b1eb-9371a5c1bc75@o13g2000vbl.googlegroups.com>
NNTP-Posting-Host: 71.100.19.226
Mime-Version: 1.0
Content-Type: text/plain; charset=windows-1252
Content-Transfer-Encoding: quoted-printable
X-Trace: posting.google.com 1247578198 10584 127.0.0.1 (14 Jul 2009 13:29:58 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Tue, 14 Jul 2009 13:29:58 +0000 (UTC)
Complaints-To: groups-abuse@google.com
Injection-Info: j19g2000vbp.googlegroups.com; posting-host=71.100.19.226; posting-account=_JvdkgoAAABJg-zfXaOxIqNr4fweMvdh
User-Agent: G2/1.0
X-HTTP-UserAgent: Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 6.0; GTB6; SLCC1; .NET CLR 2.0.50727; Media Center PC 5.0; InfoPath.2; .NET CLR 3.0.30618; OfficeLiveConnector.1.3; OfficeLivePatch.0.0; .NET CLR 3.5.30729),gzip(gfe),gzip(gfe)
Path: forums-1-dub!forums-master!newssvr.sybase.com!news-sj-1.sprintlink.net!news-peer1.sprintlink.net!newsfeed.yul.equant.net!novia!news-out.readnews.com!news-xxxfer.readnews.com!postnews.google.com!j19g2000vbp.googlegroups.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:27998
Article PK: 77245


On Jul 14, 8:57 am, Cesar <cesar.bu...@gmail.com> wrote:
> On Jul 13, 5:43 pm, "Mark A. Parsons"
>
>
>
>
>
> <iron_horse@no_spamola.compuserve.com> wrote:
> > I used savepoints on a regular basis, but not in a 'common' way ...
>
> > ------------------
>
> > When designing triggers and stored procs I like to have the code designed so that it can stand alone, and so that it has
> > minimal effects on the transaction management of parent/child coding.
>
> > If I need to rollback a transaction I will only rollback my local code (by rolling back to the save point), pass an
> > error message and/or non-zero return status to the parent process, and assume that the parent process is handling it's
> > own transaction management.  (For example, if my proc has to rollback a transaction ... do I really know if/how the
> > parent process wants to handle the rollback?  do I really want to rollback a parent process's higher-level transaction?)
>
> > This means that I typically code with a framework like such:
>
> > [NOTE:  This is a greatly watered-down framework.]
>
> > ===========================
> > create proc/trigger ...
>
> > begin tran
> > save tran my_tran
>
> > -- begin DML processing
>
> > -- for each DML statement, if any errors or invalid
> > -- change in @@trancount then ...
>
> >         goto ROLLBACK_NOW
>
> > -- for all DML and subordinate proc calls, check
> > -- transaction level, @@error, @@trancount, and
> > -- return status (for procs); if any issues then ...
>
> >         goto ROLLBACK NOW
>
> > -- end DML processing
>
> > -- if we got here then no errors so jump forward to the commit
>
> >         goto COMMIT_NOW
>
> > ROLLBACK_NOW:
>
> > -- raise an error message?
> > -- set a return status? (@rs)
>
> > -- rollback to our local save point
>
> > rollback tran my_tran
>
> > COMMIT_NOW:
>
> > -- now close out our local transaction
>
> > commit tran
>
> > return @rs
> > ===========================
>
> > By using a save point I can rollback my local transaction without affecting a parent process's transaction.
>
> > If I need to rollback my local transaction then I also pass as much info (non-zero error, non-zero return status) to the
> > parent process as possible.  It's then up to the parent process to handle it's transaction accordingly.
>
> > Obviously (?) ...
>
> > - the parent (and child) process has to be coded with good transaction management/testing
>
> > - if a child process (DML trigger, stored proc) returns to my proc with an invalid @@trancount then I have to decide
> > what to do in my code ... I usually raise an error message (including a HIGH PRIORITY message about invalid transaction
> > management by a particular subordinate piece of code) and perform a 'rollback tran' (ie, rollback everything up to this
> > point, including any previous transactional processing by a parent process).
>
> > John Flynn wrote:
> > > Hi.
>
> > > I'm pretty sure I understand mechanically how savepoints work, i.e. it lets
> > > you rollback a transaction to a "save transaction" point without rolling
> > > back the whole transaction. But I am having difficulty imagining a scenario
> > > where this capability would be practically useful.
>
> > > Some discussions on the Internet describe savepoints as a kind of
> > > human-driven "partial undo" capability. E.g. before committing the whole
> > > transaction, stop and give the human a chance to rollback to a particular
> > > point, like I guess if he has changed his mind. But of course you shouldn't
> > > allow user interaction while inside a transaction, so that idea seems like a
> > > non-starter.
>
> > > Savepoints seem inherently wasteful. You have to suffer the cost of the
> > > original update followed by the cost of rolling back the update. And it
> > > makes your transaction take longer which extends the time that your tables
> > > stay locked. Why not do the "decision" work PRIOR to the transaction, and
> > > then let the transaction do the necessary updates with no more decisions?
> > > This idea of starting a transaction, doing some updates, then saying "oops"
> > > and rolling back part of what you did, just seems half-baked to me.
>
> > > Then again, I don't have much of an imagination. Are savepoints widely used?
> > > Is there a type of scenario where people find them useful? BTW I'm on ASE15
> > > if that matters.
>
> > > Thanks.
> > > - John.- Hide quoted text -
>
> > - Show quoted text -
>
> Mark,
>
> yours should be taken as a best practice for sql programming. Very
> useful indeed!
>
> Thanks
> César Buzzo- Hide quoted text -
>
> - Show quoted text -

Mark has hit the nail right on the head (as he usually does). Here's a
template we use in our Advanced SQL class:

/* proc to demonstrate no net change to @@trancount ** but rolls back
changes within the proc ** VERY IMPORTANT: return an error code **
to tell the calling procedure rollback occurred */
create proc p1
as declare @trncnt int

select @trncnt = @@trancount -- save @@trancount value

if @trncnt = 0 -- transaction has not begun
begin tran p1 -- begin tran increments nest level to 1

else -- already in a transaction
save tran p1 -- save tran doesn’t increment nest level
/* do some processing */
if (@@transtate = 2) -- or other error condition
begin rollback tran p1 -- rollback to savepoint, or
-- to the begin tran return 25 -- return error code
indicating -- rollback end

/* more processing if required */
if @trncnt = 0 -- this proc issued begin tran
commit tran p1 -- commit tran, decrement @@trancount to 0
-- commit not required with save tran
return 0 /* successful return */


John Flynn Posted on 2009-07-14 14:53:19.0Z
From: "John Flynn" <jflynn@miqs.com>
Newsgroups: sybase.public.ase.general
References: <4a5b8d34@forums-3-dub.sybase.com> <4a5b9c58@forums-3-dub.sybase.com>
Subject: Re: What is a practical use of a savepoint?
Lines: 30
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5512
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
X-RFC2646: Format=Flowed; Response
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4a5c9bdf$3@forums-3-dub.sybase.com>
Date: 14 Jul 2009 07:53:19 -0700
X-Trace: forums-3-dub.sybase.com 1247583199 10.22.241.152 (14 Jul 2009 07:53:19 -0700)
X-Original-Trace: 14 Jul 2009 07:53:19 -0700, vip152.sybase.com
Path: forums-1-dub!forums-master!forums-3-dub.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:27999
Article PK: 77246


Mark A. Parsons wrote:
> I used savepoints on a regular basis, but not in a 'common' way ...

Thanks very much for your description of your framework. It is not like
anything I have ever attempted, but it sounds great.

But there is still something about this that I don't quite get. In my mind,
when I start a transaction I'm saying, "until the time when I commit, if
anything goes wrong, I'll want to rollback the whole transaction."
Conceptually, I can't think of a practical reason why I'd ever want to
rollback just a portion of the transaction. Granted, if I ever did, your
framework looks like an elegant way to manage it. But why would anyone ever
want to do that? That's really the gist of my question. In my mind, anyone
who starts a transaction intends to either complete the whole thing or abort
the whole thing. I always thought that was a prime raison d'etre for the
transaction concept.

You said:
> (For example, if my proc has to rollback a transaction ... do
> I really know if/how the parent process wants to handle the
> rollback? do I really want to rollback a parent process's
> higher-level transaction?)

My answer to that would be yes. I agree with you "logically", but I just
can't picture an occasion where that would be useful.

Thanks.
- John.


"Mark A. Parsons" <iron_horse Posted on 2009-07-14 15:54:50.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Thunderbird 1.5.0.10 (Windows/20070221)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: What is a practical use of a savepoint?
References: <4a5b8d34@forums-3-dub.sybase.com> <4a5b9c58@forums-3-dub.sybase.com> <4a5c9bdf$3@forums-3-dub.sybase.com>
In-Reply-To: <4a5c9bdf$3@forums-3-dub.sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Antivirus: avast! (VPS 090713-0, 07/13/2009), Outbound message
X-Antivirus-Status: Clean
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4a5caa4a$3@forums-3-dub.sybase.com>
Date: 14 Jul 2009 08:54:50 -0700
X-Trace: forums-3-dub.sybase.com 1247586890 10.22.241.152 (14 Jul 2009 08:54:50 -0700)
X-Original-Trace: 14 Jul 2009 08:54:50 -0700, vip152.sybase.com
Lines: 81
Path: forums-1-dub!forums-master!forums-3-dub.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28001
Article PK: 77247

If you're in a shop where you're responsible for all the T-SQL coding (client and server side), and you do a good job of
managing your transaction management then yes you are correct, your probably don't need the savepoints.

However, I've been in many clients where multiple people, usually over months/years, are responsible for the T-SQL
coding. Got a transaction that spans stored procs (and/or triggers)? What if different folks are writing each piece of
code and they aren't aware of how the other person is coding their piece of code? Granted, this is where coding
standards and code reviews come into play ... but I'd rather play it safe and make sure my code can stand alone while
also being able to 'play nice' with other folks' code.

-----------------

I was at a client once where all factory/warehouse/pick-pack/inventory processing was handled through a master C program.

The C program would issue 'begin tran' and then call what amounted to 10-levels of stored procs and triggers. Half
dozen different folks were coding the stored procs and triggers ... no coding standards in place ... 'begin tran',
'commit tran', 'rollback tran' strewn throughout the code with no thought given to how the transactions were managed at
the various levels.

09:00 in the morning the system was fired up ... packages were pulled off the assembly line, tagged, and added to the
database ... packages were then assigned to forklifts for placement in the warehouse (including the appropriate updates
to the database as to where each package was located) ... order processing caused pick-packers to pull packages from the
warehouse and move to the shipping line (with appropriate updates to the database as to location of packages) ...
packages were rolled up into shipments and loaded onto trucks, database was updated with package/shipment status and
invoices generated for customer billing.

Due to the mish-mash (ie, lack of) of transaction management in the T-SQL coding, several proc calls returned to the C
program with an extra open transaction ... the C program was not checking @@trancount ... so as the day progressed the C
program continued to hold open a single/master transaction what had been opened just after 09:00.

15:30 in the afternoon a piece of T-SQL code was hit that generated a 'rollback tran' ... the entire computer system
came to a halt for 15 minutes while it waited for the dataserver to rollback all transactional activity created between
09:00 and 15:30.

When the computer system 'came back online' ... lots of data was missing from the database ... with lots of 'unknown'
packages scattered throughout the warehouse (in unknown locations) ... all shipment info was lost ... all invoicing info
was lost ... bunch of trucks were running around with 'unknown' packages/shipments.

OK, OK, OK ... you'd never create such a monster right? But can you guarantee that everyone else in your shop is as
accurate with their transaction management? I'd rather add a transaction management framework to the coding standards,
generate big RED FLAGS when an issue is noticed, and make sure problems are nipped in the bud. YMMV.

----------------

It's not a whole lot unlike the issue of whether or not you use referential integrity (via constraints or triggers).
Sure, it's a lot less coding if you don't worry about and just 'assume' all T-SQL coders know what they're doing. I've
lost track of the number of shops that had the right intentions (ie, no hardcoded referential integrity; trust that all
T-SQL developers were created equal) but still ended up with broken referential integrity.

John Flynn wrote:
> Mark A. Parsons wrote:
>> I used savepoints on a regular basis, but not in a 'common' way ...
>
> Thanks very much for your description of your framework. It is not like
> anything I have ever attempted, but it sounds great.
>
> But there is still something about this that I don't quite get. In my mind,
> when I start a transaction I'm saying, "until the time when I commit, if
> anything goes wrong, I'll want to rollback the whole transaction."
> Conceptually, I can't think of a practical reason why I'd ever want to
> rollback just a portion of the transaction. Granted, if I ever did, your
> framework looks like an elegant way to manage it. But why would anyone ever
> want to do that? That's really the gist of my question. In my mind, anyone
> who starts a transaction intends to either complete the whole thing or abort
> the whole thing. I always thought that was a prime raison d'etre for the
> transaction concept.
>
> You said:
>> (For example, if my proc has to rollback a transaction ... do
>> I really know if/how the parent process wants to handle the
>> rollback? do I really want to rollback a parent process's
>> higher-level transaction?)
>
> My answer to that would be yes. I agree with you "logically", but I just
> can't picture an occasion where that would be useful.
>
> Thanks.
> - John.
>
>


"Mark A. Parsons" <iron_horse Posted on 2009-07-14 16:08:00.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Thunderbird 1.5.0.10 (Windows/20070221)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: What is a practical use of a savepoint?
References: <4a5b8d34@forums-3-dub.sybase.com> <4a5b9c58@forums-3-dub.sybase.com> <4a5c9bdf$3@forums-3-dub.sybase.com> <4a5caa4a$3@forums-3-dub.sybase.com>
In-Reply-To: <4a5caa4a$3@forums-3-dub.sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Antivirus: avast! (VPS 090713-0, 07/13/2009), Outbound message
X-Antivirus-Status: Clean
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4a5cad60$1@forums-3-dub.sybase.com>
Date: 14 Jul 2009 09:08:00 -0700
X-Trace: forums-3-dub.sybase.com 1247587680 10.22.241.152 (14 Jul 2009 09:08:00 -0700)
X-Original-Trace: 14 Jul 2009 09:08:00 -0700, vip152.sybase.com
Lines: 117
Path: forums-1-dub!forums-master!forums-3-dub.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28002
Article PK: 77249

And then there's the question of modular code design ... perhaps more of a concept than something actually used in the
real world ... where you should be able to plug together various modules (eg, procs, triggers) to build higher-level
processing components.

If you code each module (proc, trigger) as a truly stand-alone module, then the person building the higher-level
component needs only worry about the top-level transaction management (ie, no need to eyeball each piece of subordinate
code to see if/how said code is (not) managing transactions (or RI)).

Granted, all of this still requires all coders to adhere to a coding standard ... but you're usually much better off in
the end when you limit the ability of various coders to come up with their own/mismatched 'standards'.

Mark A. Parsons wrote:
> If you're in a shop where you're responsible for all the T-SQL coding
> (client and server side), and you do a good job of managing your
> transaction management then yes you are correct, your probably don't
> need the savepoints.
>
> However, I've been in many clients where multiple people, usually over
> months/years, are responsible for the T-SQL coding. Got a transaction
> that spans stored procs (and/or triggers)? What if different folks are
> writing each piece of code and they aren't aware of how the other person
> is coding their piece of code? Granted, this is where coding standards
> and code reviews come into play ... but I'd rather play it safe and make
> sure my code can stand alone while also being able to 'play nice' with
> other folks' code.
>
> -----------------
>
> I was at a client once where all factory/warehouse/pick-pack/inventory
> processing was handled through a master C program.
>
> The C program would issue 'begin tran' and then call what amounted to
> 10-levels of stored procs and triggers. Half dozen different folks were
> coding the stored procs and triggers ... no coding standards in place
> ... 'begin tran', 'commit tran', 'rollback tran' strewn throughout the
> code with no thought given to how the transactions were managed at the
> various levels.
>
> 09:00 in the morning the system was fired up ... packages were pulled
> off the assembly line, tagged, and added to the database ... packages
> were then assigned to forklifts for placement in the warehouse
> (including the appropriate updates to the database as to where each
> package was located) ... order processing caused pick-packers to pull
> packages from the warehouse and move to the shipping line (with
> appropriate updates to the database as to location of packages) ...
> packages were rolled up into shipments and loaded onto trucks, database
> was updated with package/shipment status and invoices generated for
> customer billing.
>
> Due to the mish-mash (ie, lack of) of transaction management in the
> T-SQL coding, several proc calls returned to the C program with an extra
> open transaction ... the C program was not checking @@trancount ... so
> as the day progressed the C program continued to hold open a
> single/master transaction what had been opened just after 09:00.
>
> 15:30 in the afternoon a piece of T-SQL code was hit that generated a
> 'rollback tran' ... the entire computer system came to a halt for 15
> minutes while it waited for the dataserver to rollback all transactional
> activity created between 09:00 and 15:30.
>
> When the computer system 'came back online' ... lots of data was missing
> from the database ... with lots of 'unknown' packages scattered
> throughout the warehouse (in unknown locations) ... all shipment info
> was lost ... all invoicing info was lost ... bunch of trucks were
> running around with 'unknown' packages/shipments.
>
> OK, OK, OK ... you'd never create such a monster right? But can you
> guarantee that everyone else in your shop is as accurate with their
> transaction management? I'd rather add a transaction management
> framework to the coding standards, generate big RED FLAGS when an issue
> is noticed, and make sure problems are nipped in the bud. YMMV.
>
> ----------------
>
> It's not a whole lot unlike the issue of whether or not you use
> referential integrity (via constraints or triggers). Sure, it's a lot
> less coding if you don't worry about and just 'assume' all T-SQL coders
> know what they're doing. I've lost track of the number of shops that
> had the right intentions (ie, no hardcoded referential integrity; trust
> that all T-SQL developers were created equal) but still ended up with
> broken referential integrity.
>
>
>
> John Flynn wrote:
>> Mark A. Parsons wrote:
>>> I used savepoints on a regular basis, but not in a 'common' way ...
>>
>> Thanks very much for your description of your framework. It is not
>> like anything I have ever attempted, but it sounds great.
>>
>> But there is still something about this that I don't quite get. In my
>> mind, when I start a transaction I'm saying, "until the time when I
>> commit, if anything goes wrong, I'll want to rollback the whole
>> transaction." Conceptually, I can't think of a practical reason why
>> I'd ever want to rollback just a portion of the transaction. Granted,
>> if I ever did, your framework looks like an elegant way to manage it.
>> But why would anyone ever want to do that? That's really the gist of
>> my question. In my mind, anyone who starts a transaction intends to
>> either complete the whole thing or abort the whole thing. I always
>> thought that was a prime raison d'etre for the transaction concept.
>>
>> You said:
>>> (For example, if my proc has to rollback a transaction ... do
>>> I really know if/how the parent process wants to handle the
>>> rollback? do I really want to rollback a parent process's
>>> higher-level transaction?)
>>
>> My answer to that would be yes. I agree with you "logically", but I
>> just can't picture an occasion where that would be useful.
>>
>> Thanks.
>> - John.
>>
>>


Michael Peppler [Team Sybase] Posted on 2009-07-14 18:23:37.0Z
From: "Michael Peppler [Team Sybase]" <mpeppler@peppler.org>
Organization: Peppler Consulting SARL
Subject: Re: What is a practical use of a savepoint?
User-Agent: Pan/0.14.2 (This is not a psychotic episode. It's a cleansing moment of clarity.)
Message-ID: <pan.2009.07.14.18.23.33.40383@peppler.org>
Newsgroups: sybase.public.ase.general
References: <4a5b8d34@forums-3-dub.sybase.com> <4a5b9c58@forums-3-dub.sybase.com> <4a5c9bdf$3@forums-3-dub.sybase.com> <4a5caa4a$3@forums-3-dub.sybase.com> <4a5cad60$1@forums-3-dub.sybase.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 14 Jul 2009 11:23:37 -0700
X-Trace: forums-3-dub.sybase.com 1247595817 10.22.241.152 (14 Jul 2009 11:23:37 -0700)
X-Original-Trace: 14 Jul 2009 11:23:37 -0700, vip152.sybase.com
Lines: 147
Path: forums-1-dub!forums-master!forums-3-dub.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28004
Article PK: 77251

I'll jump in here...

I used to write code the same way as you - i.e. a begin tran, a save tran,
rollback to the savepoint if needed, and finally a commit, with a return
code to indicate failure or success.

I've come to reasses that style, and now prefer to have each proc that
needs to be transactional to have matched begin tran/commit blocks, and
use rollback wherever a failure occurs to roll back the entire transaction.

Essentially the @@trancount should always be the same at the entry point
and exit of the proc, unless there's been a rollback, of course.

We're a T-SQL/stored proc shop, so no transactions are allowed to be
started in the client code, which makes it a little easier.

The transaction nesting (without using save points, or named transactions)
works very well.

Now if I could only get ASE to allow the creation of temp tables inside of
transactions (without using ddl in tran) I'd be really happy!

Michael

On Tue, 14 Jul 2009 09:08:00 -0700, Mark A. Parsons wrote:

> And then there's the question of modular code design ... perhaps more of a concept than something actually used in the
> real world ... where you should be able to plug together various modules (eg, procs, triggers) to build higher-level
> processing components.
>
> If you code each module (proc, trigger) as a truly stand-alone module, then the person building the higher-level
> component needs only worry about the top-level transaction management (ie, no need to eyeball each piece of subordinate
> code to see if/how said code is (not) managing transactions (or RI)).
>
> Granted, all of this still requires all coders to adhere to a coding standard ... but you're usually much better off in
> the end when you limit the ability of various coders to come up with their own/mismatched 'standards'.
>
>
>
> Mark A. Parsons wrote:
>> If you're in a shop where you're responsible for all the T-SQL coding
>> (client and server side), and you do a good job of managing your
>> transaction management then yes you are correct, your probably don't
>> need the savepoints.
>>
>> However, I've been in many clients where multiple people, usually over
>> months/years, are responsible for the T-SQL coding. Got a transaction
>> that spans stored procs (and/or triggers)? What if different folks are
>> writing each piece of code and they aren't aware of how the other person
>> is coding their piece of code? Granted, this is where coding standards
>> and code reviews come into play ... but I'd rather play it safe and make
>> sure my code can stand alone while also being able to 'play nice' with
>> other folks' code.
>>
>> -----------------
>>
>> I was at a client once where all factory/warehouse/pick-pack/inventory
>> processing was handled through a master C program.
>>
>> The C program would issue 'begin tran' and then call what amounted to
>> 10-levels of stored procs and triggers. Half dozen different folks were
>> coding the stored procs and triggers ... no coding standards in place
>> ... 'begin tran', 'commit tran', 'rollback tran' strewn throughout the
>> code with no thought given to how the transactions were managed at the
>> various levels.
>>
>> 09:00 in the morning the system was fired up ... packages were pulled
>> off the assembly line, tagged, and added to the database ... packages
>> were then assigned to forklifts for placement in the warehouse
>> (including the appropriate updates to the database as to where each
>> package was located) ... order processing caused pick-packers to pull
>> packages from the warehouse and move to the shipping line (with
>> appropriate updates to the database as to location of packages) ...
>> packages were rolled up into shipments and loaded onto trucks, database
>> was updated with package/shipment status and invoices generated for
>> customer billing.
>>
>> Due to the mish-mash (ie, lack of) of transaction management in the
>> T-SQL coding, several proc calls returned to the C program with an extra
>> open transaction ... the C program was not checking @@trancount ... so
>> as the day progressed the C program continued to hold open a
>> single/master transaction what had been opened just after 09:00.
>>
>> 15:30 in the afternoon a piece of T-SQL code was hit that generated a
>> 'rollback tran' ... the entire computer system came to a halt for 15
>> minutes while it waited for the dataserver to rollback all transactional
>> activity created between 09:00 and 15:30.
>>
>> When the computer system 'came back online' ... lots of data was missing
>> from the database ... with lots of 'unknown' packages scattered
>> throughout the warehouse (in unknown locations) ... all shipment info
>> was lost ... all invoicing info was lost ... bunch of trucks were
>> running around with 'unknown' packages/shipments.
>>
>> OK, OK, OK ... you'd never create such a monster right? But can you
>> guarantee that everyone else in your shop is as accurate with their
>> transaction management? I'd rather add a transaction management
>> framework to the coding standards, generate big RED FLAGS when an issue
>> is noticed, and make sure problems are nipped in the bud. YMMV.
>>
>> ----------------
>>
>> It's not a whole lot unlike the issue of whether or not you use
>> referential integrity (via constraints or triggers). Sure, it's a lot
>> less coding if you don't worry about and just 'assume' all T-SQL coders
>> know what they're doing. I've lost track of the number of shops that
>> had the right intentions (ie, no hardcoded referential integrity; trust
>> that all T-SQL developers were created equal) but still ended up with
>> broken referential integrity.
>>
>>
>>
>> John Flynn wrote:
>>> Mark A. Parsons wrote:
>>>> I used savepoints on a regular basis, but not in a 'common' way ...
>>>
>>> Thanks very much for your description of your framework. It is not
>>> like anything I have ever attempted, but it sounds great.
>>>
>>> But there is still something about this that I don't quite get. In my
>>> mind, when I start a transaction I'm saying, "until the time when I
>>> commit, if anything goes wrong, I'll want to rollback the whole
>>> transaction." Conceptually, I can't think of a practical reason why
>>> I'd ever want to rollback just a portion of the transaction. Granted,
>>> if I ever did, your framework looks like an elegant way to manage it.
>>> But why would anyone ever want to do that? That's really the gist of
>>> my question. In my mind, anyone who starts a transaction intends to
>>> either complete the whole thing or abort the whole thing. I always
>>> thought that was a prime raison d'etre for the transaction concept.
>>>
>>> You said:
>>>> (For example, if my proc has to rollback a transaction ... do
>>>> I really know if/how the parent process wants to handle the
>>>> rollback? do I really want to rollback a parent process's
>>>> higher-level transaction?)
>>>
>>> My answer to that would be yes. I agree with you "logically", but I
>>> just can't picture an occasion where that would be useful.
>>>
>>> Thanks.
>>> - John.
>>>
>>>


Nishant Rupani Posted on 2009-07-17 08:55:48.0Z
From: Nishant Rupani <nishant.rupani@gmail.com>
Newsgroups: sybase.public.ase.general
Subject: Re: What is a practical use of a savepoint?
Date: Fri, 17 Jul 2009 01:55:48 -0700 (PDT)
Organization: http://groups.google.com
Lines: 8
Message-ID: <1864bb4e-e36f-413a-b028-790240c7fac9@a7g2000yqk.googlegroups.com>
References: <4a5b8d34@forums-3-dub.sybase.com> <4a5b9c58@forums-3-dub.sybase.com> <4a5c9bdf$3@forums-3-dub.sybase.com> <4a5caa4a$3@forums-3-dub.sybase.com> <4a5cad60$1@forums-3-dub.sybase.com> <pan.2009.07.14.18.23.33.40383@peppler.org>
NNTP-Posting-Host: 203.207.62.38
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
X-Trace: posting.google.com 1247820948 1907 127.0.0.1 (17 Jul 2009 08:55:48 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Fri, 17 Jul 2009 08:55:48 +0000 (UTC)
Complaints-To: groups-abuse@google.com
Injection-Info: a7g2000yqk.googlegroups.com; posting-host=203.207.62.38; posting-account=y-Y-8QoAAABzR4Wr8eYwc4ZkHK7jRXT_
User-Agent: G2/1.0
X-HTTP-UserAgent: Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; .NET CLR 1.1.4322; .NET CLR 2.0.50727; InfoPath.2; .NET CLR 3.0.04506.30; .NET CLR 3.0.04506.648; .NET CLR 3.5.21022; .NET CLR 3.0.4506.2152; .NET CLR 3.5.30729),gzip(gfe),gzip(gfe)
Path: forums-1-dub!forums-master!newssvr.sybase.com!news-sj-1.sprintlink.net!news-peer1.sprintlink.net!nntp1.phx1.gblx.net!nntp.gblx.net!nntp.gblx.net!border2.nntp.dca.giganews.com!nntp.giganews.com!postnews.google.com!a7g2000yqk.googlegroups.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28015
Article PK: 77262

Also, could someone please throw some light on the usage of nested
transactions without using save point? I believe we can not take
anything out of the concept of nested transactions without save point
as ROLLBACK will rollback everything and COMMIT won't happen untill
the last commit command.

Thanks,
Nishant


mpeppler@peppler.org [Team Sybase] Posted on 2009-07-17 11:45:16.0Z
From: "mpeppler@peppler.org [Team Sybase]" <michael.peppler@gmail.com>
Newsgroups: sybase.public.ase.general
Subject: Re: What is a practical use of a savepoint?
Date: Fri, 17 Jul 2009 04:45:16 -0700 (PDT)
Organization: http://groups.google.com
Lines: 20
Message-ID: <773b7374-4ef4-45af-aecd-ffaeb5bb61c0@o15g2000yqm.googlegroups.com>
References: <4a5b8d34@forums-3-dub.sybase.com> <4a5b9c58@forums-3-dub.sybase.com> <4a5c9bdf$3@forums-3-dub.sybase.com> <4a5caa4a$3@forums-3-dub.sybase.com> <4a5cad60$1@forums-3-dub.sybase.com> <pan.2009.07.14.18.23.33.40383@peppler.org> <1864bb4e-e36f-413a-b028-790240c7fac9@a7g2000yqk.googlegroups.com>
NNTP-Posting-Host: 170.148.215.157
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
X-Trace: posting.google.com 1247831116 3680 127.0.0.1 (17 Jul 2009 11:45:16 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Fri, 17 Jul 2009 11:45:16 +0000 (UTC)
Complaints-To: groups-abuse@google.com
Injection-Info: o15g2000yqm.googlegroups.com; posting-host=170.148.215.157; posting-account=9rHMzAoAAADtzToS8d2WKVGlkISAvPdk
User-Agent: G2/1.0
X-HTTP-UserAgent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.1.1) Gecko/20090715 Firefox/3.5.1,gzip(gfe),gzip(gfe)
Path: forums-1-dub!forums-master!newssvr.sybase.com!news-sj-1.sprintlink.net!news-peer1.sprintlink.net!nntp1.phx1.gblx.net!nntp.gblx.net!nntp.gblx.net!border2.nntp.dca.giganews.com!nntp.giganews.com!postnews.google.com!o15g2000yqm.googlegroups.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28016
Article PK: 77264


On Jul 17, 10:55 am, Nishant Rupani <nishant.rup...@gmail.com> wrote:
> Also, could someone please throw some light on the usage of nested
> transactions without using save point? I believe we can not take
> anything out of the concept of nested transactions without save point
> as ROLLBACK will rollback everything and COMMIT won't happen untill
> the last commit command.

That's really the point (at least for me).

The individual procs each have their own transactions, and if they
succeed they commit the local changes. If these procs are called in
isolation they will behave transactionally as well, and you don't need
any special test to see if you are in a transaction or not.
As for the rollback - in my opinion in the vast majority of cases if
something fails and requires a rollback in a sub-proc then the entire
transaction really should be rolled back. The case where you could
check for a sub-proc failure and code around it in your execution flow
is I think rather rare.

Michael


ThanksButNo Posted on 2009-07-21 22:36:33.0Z
From: ThanksButNo <no.no.thanks@gmail.com>
Newsgroups: sybase.public.ase.general
Subject: Re: What is a practical use of a savepoint?
Date: Tue, 21 Jul 2009 15:36:33 -0700 (PDT)
Organization: http://groups.google.com
Lines: 23
Message-ID: <b2979e7a-3a93-42cf-a7d0-3003ecf0f285@c14g2000yqm.googlegroups.com>
References: <4a5b8d34@forums-3-dub.sybase.com> <4a5b9c58@forums-3-dub.sybase.com> <4a5c9bdf$3@forums-3-dub.sybase.com> <4a5caa4a$3@forums-3-dub.sybase.com> <4a5cad60$1@forums-3-dub.sybase.com> <pan.2009.07.14.18.23.33.40383@peppler.org> <1864bb4e-e36f-413a-b028-790240c7fac9@a7g2000yqk.googlegroups.com>
NNTP-Posting-Host: 71.165.35.183
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
X-Trace: posting.google.com 1248215793 22437 127.0.0.1 (21 Jul 2009 22:36:33 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Tue, 21 Jul 2009 22:36:33 +0000 (UTC)
Complaints-To: groups-abuse@google.com
Injection-Info: c14g2000yqm.googlegroups.com; posting-host=71.165.35.183; posting-account=wjKAPwoAAABtEbTff5o9OO7GYdigbDts
User-Agent: G2/1.0
X-HTTP-UserAgent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.1) Gecko/20090624 Firefox/3.5 (.NET CLR 3.5.30729),gzip(gfe),gzip(gfe)
Path: forums-1-dub!forums-master!newssvr.sybase.com!news-sj-1.sprintlink.net!news-peer1.sprintlink.net!newsfeed.yul.equant.net!nntp1.roc.gblx.net!nntp.gblx.net!nntp.gblx.net!nlpi057.nbdc.sbc.com!prodigy.net!news.glorb.com!news2.glorb.com!postnews.google.com!c14g2000yqm.googlegroups.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28018
Article PK: 77265


On Jul 17, 1:55 am, Nishant Rupani <nishant.rup...@gmail.com> wrote:
> Also, could someone please throw some light on the usage of nested
> transactions without using save point? I believe we can not take
> anything out of the concept of nested transactions without save point
> as ROLLBACK will rollback everything and COMMIT won't happen untill
> the last commit command.

I can envision a situation where you don't know what's inside
a number of procs, and you don't particularly care, but they
tell you that they will return a given status for success or
failure, and they handle their own sub-transactions.

Your outer code begins an outer transaction, then runs the first
proc. If it fails, it moves on to the second. Then the third,
etc. Eventually, one of the procs will succeed, the outer code
does whatever else it needs, and commits the outer transaction.
Or none of them succeed, and the outer transaction is rolled back.

You can be confident in the transactional model that none of the
procs left the database in some unknown half-updated state.

/:-/


ThanksButNo Posted on 2009-07-22 01:52:03.0Z
From: ThanksButNo <no.no.thanks@gmail.com>
Newsgroups: sybase.public.ase.general
Subject: Re: What is a practical use of a savepoint?
Date: Tue, 21 Jul 2009 18:52:03 -0700 (PDT)
Organization: http://groups.google.com
Lines: 32
Message-ID: <74dde0bb-94d7-4d76-b5fb-c9e677267570@a26g2000yqn.googlegroups.com>
References: <4a5b8d34@forums-3-dub.sybase.com> <4a5b9c58@forums-3-dub.sybase.com> <4a5c9bdf$3@forums-3-dub.sybase.com> <4a5caa4a$3@forums-3-dub.sybase.com> <4a5cad60$1@forums-3-dub.sybase.com> <pan.2009.07.14.18.23.33.40383@peppler.org> <1864bb4e-e36f-413a-b028-790240c7fac9@a7g2000yqk.googlegroups.com> <b2979e7a-3a93-42cf-a7d0-3003ecf0f285@c14g2000yqm.googlegroups.com>
NNTP-Posting-Host: 71.165.35.183
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
X-Trace: posting.google.com 1248227523 28926 127.0.0.1 (22 Jul 2009 01:52:03 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Wed, 22 Jul 2009 01:52:03 +0000 (UTC)
Complaints-To: groups-abuse@google.com
Injection-Info: a26g2000yqn.googlegroups.com; posting-host=71.165.35.183; posting-account=wjKAPwoAAABtEbTff5o9OO7GYdigbDts
User-Agent: G2/1.0
X-HTTP-UserAgent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.1) Gecko/20090624 Firefox/3.5 (.NET CLR 3.5.30729),gzip(gfe),gzip(gfe)
Path: forums-1-dub!forums-master!newssvr.sybase.com!news-sj-1.sprintlink.net!news-peer1.sprintlink.net!nntp1.phx1.gblx.net!nntp.gblx.net!nntp.gblx.net!border2.nntp.dca.giganews.com!nntp.giganews.com!postnews.google.com!a26g2000yqn.googlegroups.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28020
Article PK: 77267


On Jul 21, 3:36 pm, ThanksButNo <no.no.tha...@gmail.com> wrote:
> On Jul 17, 1:55 am, Nishant Rupani <nishant.rup...@gmail.com> wrote:
>
> > Also, could someone please throw some light on the usage of nested
> > transactions without using save point? I believe we can not take
> > anything out of the concept of nested transactions without save point
> > as ROLLBACK will rollback everything and COMMIT won't happen untill
> > the last commit command.
>
> I can envision a situation where you don't know what's inside
> a number of procs, and you don't particularly care, but they
> tell you that they will return a given status for success or
> failure, and they handle their own sub-transactions.
>
> Your outer code begins an outer transaction, then runs the first
> proc.  If it fails, it moves on to the second.  Then the third,
> etc.  Eventually, one of the procs will succeed, the outer code
> does whatever else it needs, and commits the outer transaction.
> Or none of them succeed, and the outer transaction is rolled back.
>
> You can be confident in the transactional model that none of the
> procs left the database in some unknown half-updated state.

I can further envision that, after successfully running whatever
procs needed to be run, then doing a little more work, and running
into an error. So, when the outer caller rolls back the outer
transaction, EACH SUB-TRANSACTION, that was otherwise perfectly
successful, is rolled back as well.

Just something else I thought of.

/:-/


John Flynn Posted on 2009-07-14 16:50:05.0Z
From: "John Flynn" <jflynn@miqs.com>
Newsgroups: sybase.public.ase.general
References: <4a5b8d34@forums-3-dub.sybase.com> <4a5b9c58@forums-3-dub.sybase.com> <4a5c9bdf$3@forums-3-dub.sybase.com> <4a5caa4a$3@forums-3-dub.sybase.com>
Subject: Re: What is a practical use of a savepoint?
Lines: 20
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5512
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
X-RFC2646: Format=Flowed; Response
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4a5cb73d@forums-3-dub.sybase.com>
Date: 14 Jul 2009 09:50:05 -0700
X-Trace: forums-3-dub.sybase.com 1247590205 10.22.241.152 (14 Jul 2009 09:50:05 -0700)
X-Original-Trace: 14 Jul 2009 09:50:05 -0700, vip152.sybase.com
Path: forums-1-dub!forums-master!forums-3-dub.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28003
Article PK: 77250


Mark A. Parsons wrote:
> Due to the mish-mash (ie, lack of) of transaction management in the
> T-SQL coding, several proc calls returned to the C program with an
> extra open transaction ... the C program was not checking @@trancount
> ... so as the day progressed the C program continued to hold open a
> single/master transaction what had been opened just after 09:00.
>
> 15:30 in the afternoon a piece of T-SQL code was hit that generated a
> 'rollback tran' ... the entire computer system came to a halt for 15
> minutes while it waited for the dataserver to rollback all
> transactional activity created between 09:00 and 15:30.

Yeah, that's my worst nightmare, programming-wise. Of course, *I* would
never program anything like that! :-)

Thanks very much for your insights, I greatly appreciate it.

- John.