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.

Two-phase commit in Sybase ASE 15.0.3 T-SQL

3 posts in General Discussion Last posting was on 2012-07-30 13:22:23.0Z
MAK Posted on 2012-07-30 09:28:43.0Z
Sender: 56ed.5016523f.1804289383@sybase.com
From: MAK
Newsgroups: sybase.public.ase.general
Subject: Two-phase commit in Sybase ASE 15.0.3 T-SQL
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <501653cb.5710.1681692777@sybase.com>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 30 Jul 2012 02:28:43 -0700
X-Trace: forums-1-dub 1343640523 172.20.134.41 (30 Jul 2012 02:28:43 -0700)
X-Original-Trace: 30 Jul 2012 02:28:43 -0700, 172.20.134.41
Lines: 15
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31279
Article PK: 74168

Hi,

I have the below sample code:

begin tran
insert into tab1(c1) values ('a')
update tab2 set c2='b'
rollback tran
commit tran

I want both the above dml statements to either fully fail or
pass. Hence how do I implement the Two-phase commit in
Sybase ASE 15.0.3 in Transact SQL?

Thanks for your help.


Rob V Posted on 2012-07-30 13:11:02.0Z
From: Rob V <rob@sypron.nl>
Reply-To: rob@sypron.nl
User-Agent: Mozilla/5.0 (Windows NT 5.1; rv:14.0) Gecko/20120713 Thunderbird/14.0
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Two-phase commit in Sybase ASE 15.0.3 T-SQL
References: <501653cb.5710.1681692777@sybase.com>
In-Reply-To: <501653cb.5710.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: <501687e6$1@forums-1-dub>
Date: 30 Jul 2012 06:11:02 -0700
X-Trace: forums-1-dub 1343653862 172.20.134.152 (30 Jul 2012 06:11:02 -0700)
X-Original-Trace: 30 Jul 2012 06:11:02 -0700, vip152.sybase.com
Lines: 41
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31280
Article PK: 74169


On 30-Jul-2012 11:28, MAK wrote:
> Hi,
>
> I have the below sample code:
>
> begin tran
> insert into tab1(c1) values ('a')
> update tab2 set c2='b'
> rollback tran
> commit tran
>
> I want both the above dml statements to either fully fail or
> pass. Hence how do I implement the Two-phase commit in
> Sybase ASE 15.0.3 in Transact SQL?
>
> Thanks for your help.
>

You can achieve that by wrapping them in a transaction, pretty much as
shown in your example (although crucial error handling is missing to
decide whether to rollback or commit).
You don't need two-phase commit for this; the regular transaction
mechanism provides what you describe.

HTH,

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

Certified Professional DBA for Sybase ASE, IQ, Replication Server

Author of Sybase books (order online at www.sypron.nl/shop):
"Tips, Tricks & Recipes for Sybase ASE"
"The Complete Sybase IQ Quick Reference Guide"
"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
-----------------------------------------------------------------


"Mark A. Parsons" <iron_horse Posted on 2012-07-30 13:22:23.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 6.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: Two-phase commit in Sybase ASE 15.0.3 T-SQL
References: <501653cb.5710.1681692777@sybase.com>
In-Reply-To: <501653cb.5710.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Antivirus: avast! (VPS 120603-1, 06/03/2012), Outbound message
X-Antivirus-Status: Clean
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <50168a8f$1@forums-1-dub>
Date: 30 Jul 2012 06:22:23 -0700
X-Trace: forums-1-dub 1343654543 172.20.134.152 (30 Jul 2012 06:22:23 -0700)
X-Original-Trace: 30 Jul 2012 06:22:23 -0700, vip152.sybase.com
Lines: 103
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31281
Article PK: 74171

'two-phase commit' is the method used to coordinate transactional consistency across *multiple* dataservers.

Is tab1 or tab2 a proxy to a table on another dataserver?

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

Assuming tab1 and tab2 are local (ie, not proxies):

- this is not a two-phase commit scenario
- after each DML you need to test for the occurrence of an error
- if there was an error then rollback
- if you complete the DML statements w/out an error then commit

So the general concept would be:

===============
begin tran
insert ...
if @@error != 0 rollback tran
update ...
if @@error !=0 rollback tran
if @@trancount > 0 commit tran
===============

If your transaction may be part of a larger transaction (ie, nested txns) then you'll probably want to use a save point
and manage your transaction locally (hint: 'rollback tran' will rollback all parent/open transactions, too).

If your DML statements fire triggers, and said triggers may (inappropriately?) issue a 'rollback tran' then you may want
to include a test for @@trancount before you decide on committing/rolling-back your tran.

If you call subordinate procs within your transaction, and said procs may (inappropriately?) issue a 'rollback tran'
then you may want to include the test for @@trancount.

While it may seem like a bit of overkill, something like the following may address most scenarios:

=======================
declare @trancount int, @rs int, @error int, @msg varchar(1000)

-- start tran
begin tran

-- provide point to rollback to
save tran my_tran

-- get current count of open txns
select @trancount=@@trancount

insert/update/delete ...

-- if DML error then rollback
if @@error != 0
begin
-- pick unique error number and descriptive message
select @error = <pick_uniq_#>, @msg = "problems with DML statement; table=???; operation=???"
goto rollback_now
end

exec @rs = some_proc_call

-- if error/issue with proc call then rollback
if @@error != 0 or @rs != 0
begin
-- pick unique error number and descriptive message
select @error = <pick_uniq_#>, @msg = "problems with proc call; proc=???; operation=???; rs=???"
goto rollback_now
end

-- if count of open txns has not changed then commit
if @trancount = @@trancount goto commit_now
else
begin
-- let parent process know there is an issue with improper (?) txn management
select @error = <pick_uniq_#>, @msg = "invalid @@trancount; rolling back local tran"
end

rollback_now:
-- generate our own error to allow us to track issue back to the source code
raiserror @error @msg

-- rollback to our save point
rollback my_tran

commit_now:
-- close out our tran
commit tran
======================

On 07/30/2012 03:28, MAK wrote:
> Hi,
>
> I have the below sample code:
>
> begin tran
> insert into tab1(c1) values ('a')
> update tab2 set c2='b'
> rollback tran
> commit tran
>
> I want both the above dml statements to either fully fail or
> pass. Hence how do I implement the Two-phase commit in
> Sybase ASE 15.0.3 in Transact SQL?
>
> Thanks for your help.