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 faster, COMMIT or ROLLBACK?

9 posts in General Discussion (old) Last posting was on 2008-08-15 12:29:13.0Z
Markus KARG Posted on 2008-08-13 14:37:04.0Z
From: "Markus KARG" <karg@quipsy.de>
Newsgroups: sybase.public.sqlanywhere
Subject: What is faster, COMMIT or ROLLBACK?
Lines: 9
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5512
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5512
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <48a2f190$1@forums-1-dub>
Date: 13 Aug 2008 07:37:04 -0700
X-Trace: forums-1-dub 1218638224 10.22.241.152 (13 Aug 2008 07:37:04 -0700)
X-Original-Trace: 13 Aug 2008 07:37:04 -0700, vip152.sybase.com
X-Authenticated-User: panorama
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.sqlanywhere:107
Article PK: 866553

I have lots of transactions that on do SELECT but never do INSERT, UPDATE or
DELETE.
So I have free choice of sending COMMIT or ROLLBACK, it will make no
difference in the result.
But maybe there is a difference in the performance of that command?
What is the fastest way of finishing a read-only transaction: COMMIT or
ROLLBACK?


Jeff Albion (Sybase iAnywhere) Posted on 2008-08-13 16:07:16.0Z
From: "Jeff Albion (Sybase iAnywhere)" <firstname.lastname@ianywhere.com>
User-Agent: Thunderbird 2.0.0.16 (Windows/20080708)
MIME-Version: 1.0
Newsgroups: sybase.public.sqlanywhere
Subject: Re: What is faster, COMMIT or ROLLBACK?
References: <48a2f190$1@forums-1-dub>
In-Reply-To: <48a2f190$1@forums-1-dub>
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: <48a306b4$1@forums-1-dub>
Date: 13 Aug 2008 09:07:16 -0700
X-Trace: forums-1-dub 1218643636 10.22.241.152 (13 Aug 2008 09:07:16 -0700)
X-Original-Trace: 13 Aug 2008 09:07:16 -0700, vip152.sybase.com
Lines: 30
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.sqlanywhere:111
Article PK: 866557

Markus,

If you haven't changed any data (e.g. via INSERT/UPDATE/DELETE), then
there is no requirement to do this in a transactional context, so there
is no need to send either statement. Transactions are used to group data
manipulations together in an ACID fashion.

Could you perhaps explain your requirements a little more so that we can
understand why you have a transaction open to perform "read-only" SELECTs?

Regards,

Markus KARG wrote:
> I have lots of transactions that on do SELECT but never do INSERT, UPDATE or
> DELETE.
> So I have free choice of sending COMMIT or ROLLBACK, it will make no
> difference in the result.
> But maybe there is a difference in the performance of that command?
> What is the fastest way of finishing a read-only transaction: COMMIT or
> ROLLBACK?

--
Jeff Albion, Product Support Analyst
Sybase iAnywhere

iAnywhere Developer Community :
http://www.sybase.com/developer/library/sql-anywhere-techcorner
iAnywhere Documentation : http://www.ianywhere.com/developer/product_manuals
SQL Anywhere Patches and EBFs :
http://downloads.sybase.com/swd/summary.do?baseprod=144&client=ianywhere&timeframe=0


Markus KARG Posted on 2008-08-14 06:37:59.0Z
From: "Markus KARG" <karg@quipsy.de>
Newsgroups: sybase.public.sqlanywhere
References: <48a2f190$1@forums-1-dub> <48a306b4$1@forums-1-dub>
Subject: Re: What is faster, COMMIT or ROLLBACK?
Lines: 56
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5512
X-RFC2646: Format=Flowed; Response
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5512
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <48a3d2c7$1@forums-1-dub>
Date: 13 Aug 2008 23:37:59 -0700
X-Trace: forums-1-dub 1218695879 10.22.241.152 (13 Aug 2008 23:37:59 -0700)
X-Original-Trace: 13 Aug 2008 23:37:59 -0700, vip152.sybase.com
X-Authenticated-User: panorama
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.sqlanywhere:121
Article PK: 866565

Jeff,

thanks for answering.

I am using a transaction since lots of interdependent SELECTs are running,
e. g. first SELECT provides some information and depending on that the
client's algorithm decides what next SELECT to run. For the second SELECT to
find the correct data, we need to hold read locks on the result of the first
SELECT. So we need a transaction, don't we? Also, we are using JDBC, so we
have a transaction anyways, since JDBC cannot execute SQL without a
surrounding transaction (the transaction starts automatically when the
connection starts and you can just decide to either have commit/begin
transaction executed automatically after each command, or send
commit/rollback manually at the end). Since different steps proceeded by the
client must not keep locks of former steps, we must either terminate the
connection (using commit or rollback) or terminate the connection (what I
suppose beeing a performance drawback).

Regards
Markus

"Jeff Albion (Sybase iAnywhere)" <firstname.lastname@ianywhere.com> schrieb
im Newsbeitrag news:48a306b4$1@forums-1-dub...

> Markus,
>
> If you haven't changed any data (e.g. via INSERT/UPDATE/DELETE), then
> there is no requirement to do this in a transactional context, so there is
> no need to send either statement. Transactions are used to group data
> manipulations together in an ACID fashion.
>
> Could you perhaps explain your requirements a little more so that we can
> understand why you have a transaction open to perform "read-only" SELECTs?
>
> Regards,
>
> Markus KARG wrote:
>> I have lots of transactions that on do SELECT but never do INSERT, UPDATE
>> or DELETE.
>> So I have free choice of sending COMMIT or ROLLBACK, it will make no
>> difference in the result.
>> But maybe there is a difference in the performance of that command?
>> What is the fastest way of finishing a read-only transaction: COMMIT or
>> ROLLBACK?
>
> --
> Jeff Albion, Product Support Analyst
> Sybase iAnywhere
>
> iAnywhere Developer Community :
> http://www.sybase.com/developer/library/sql-anywhere-techcorner
> iAnywhere Documentation :
> http://www.ianywhere.com/developer/product_manuals
> SQL Anywhere Patches and EBFs :
> http://downloads.sybase.com/swd/summary.do?baseprod=144&client=ianywhere&timeframe=0


Breck Carter [sqlanywhere.blogspot.com] Posted on 2008-08-15 10:52:35.0Z
From: "Breck Carter [sqlanywhere.blogspot.com]" <NOSPAM__breck.carter@gmail.com>
Newsgroups: sybase.public.sqlanywhere
Subject: Re: What is faster, COMMIT or ROLLBACK?
Organization: RisingRoad Professional Services
Reply-To: NOSPAM__breck.carter@gmail.com
Message-ID: <jrnaa49ncapa4596m30fre13olfgt2ob0i@4ax.com>
References: <48a2f190$1@forums-1-dub> <48a306b4$1@forums-1-dub>
X-Newsreader: Forte Agent 2.0/32.640
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 15 Aug 2008 03:52:35 -0700
X-Trace: forums-1-dub 1218797555 10.22.241.152 (15 Aug 2008 03:52:35 -0700)
X-Original-Trace: 15 Aug 2008 03:52:35 -0700, vip152.sybase.com
Lines: 38
X-Authenticated-User: TeamSybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.sqlanywhere:125
Article PK: 866571

FWIW in chained mode (the default) a transaction is started by an
insert, update, delete, or... a *select* that acquires locks.
Frequently doing a commit or rollback on a read-only connection that
is doing a lot of queries at isolation level 2 or 3 is a really good
idea... since those connections tend to lock down the world :)

Breck


On 13 Aug 2008 09:07:16 -0700, "Jeff Albion (Sybase iAnywhere)"

<firstname.lastname@ianywhere.com> wrote:

>Markus,
>
>If you haven't changed any data (e.g. via INSERT/UPDATE/DELETE), then
>there is no requirement to do this in a transactional context, so there
>is no need to send either statement. Transactions are used to group data
>manipulations together in an ACID fashion.
>
>Could you perhaps explain your requirements a little more so that we can
>understand why you have a transaction open to perform "read-only" SELECTs?
>
>Regards,
>
>Markus KARG wrote:
>> I have lots of transactions that on do SELECT but never do INSERT, UPDATE or
>> DELETE.
>> So I have free choice of sending COMMIT or ROLLBACK, it will make no
>> difference in the result.
>> But maybe there is a difference in the performance of that command?
>> What is the fastest way of finishing a read-only transaction: COMMIT or
>> ROLLBACK?

--
Breck Carter http://sqlanywhere.blogspot.com/

RisingRoad SQL Anywhere and MobiLink Professional Services
breck.carter@risingroad.com


Markus KARG Posted on 2008-08-14 13:48:47.0Z
From: "Markus KARG" <karg@quipsy.de>
Newsgroups: sybase.public.sqlanywhere
References: <48a2f190$1@forums-1-dub>
Subject: Re: What is faster, COMMIT or ROLLBACK?
Lines: 25
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: <48a437bf@forums-1-dub>
Date: 14 Aug 2008 06:48:47 -0700
X-Trace: forums-1-dub 1218721727 10.22.241.152 (14 Aug 2008 06:48:47 -0700)
X-Original-Trace: 14 Aug 2008 06:48:47 -0700, vip152.sybase.com
X-Authenticated-User: panorama
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.sqlanywhere:122
Article PK: 866574

I couldn't wait for the theoretical result, so I just tried it out. Please
comment if you like. :-)

5000 transactions terminated by COMMIT needed 20 seconds in my environment.
5000 transactions terminated by ROLLBACK needed 20 seconds in my
environment.

So in reality it seems there are absolutely no difference as long as only
SELECT is used. :-)

Have Fun
Markus

"Markus KARG" <karg@quipsy.de> schrieb im Newsbeitrag
news:48a2f190$1@forums-1-dub...

>I have lots of transactions that on do SELECT but never do INSERT, UPDATE
>or DELETE.
> So I have free choice of sending COMMIT or ROLLBACK, it will make no
> difference in the result.
> But maybe there is a difference in the performance of that command?
> What is the fastest way of finishing a read-only transaction: COMMIT or
> ROLLBACK?
>


ps <ps Posted on 2008-08-14 14:43:04.0Z
From: ps <ps@.>
User-Agent: Thunderbird 2.0.0.14 (Windows/20080421)
MIME-Version: 1.0
Newsgroups: sybase.public.sqlanywhere
Subject: Re: What is faster, COMMIT or ROLLBACK?
References: <48a2f190$1@forums-1-dub> <48a437bf@forums-1-dub>
In-Reply-To: <48a437bf@forums-1-dub>
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: <48a44478$1@forums-1-dub>
Date: 14 Aug 2008 07:43:04 -0700
X-Trace: forums-1-dub 1218724984 10.22.241.152 (14 Aug 2008 07:43:04 -0700)
X-Original-Trace: 14 Aug 2008 07:43:04 -0700, vip152.sybase.com
Lines: 30
X-Authenticated-User: powerbuilder
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.sqlanywhere:123
Article PK: 866573

The question would be : "why is sybase spending 20 seconds on a
commit/rollback when the transaction was constituted from select
statements only?"

Markus KARG wrote:
> I couldn't wait for the theoretical result, so I just tried it out. Please
> comment if you like. :-)
>
> 5000 transactions terminated by COMMIT needed 20 seconds in my environment.
> 5000 transactions terminated by ROLLBACK needed 20 seconds in my
> environment.
>
> So in reality it seems there are absolutely no difference as long as only
> SELECT is used. :-)
>
> Have Fun
> Markus
>
> "Markus KARG" <karg@quipsy.de> schrieb im Newsbeitrag
> news:48a2f190$1@forums-1-dub...
>> I have lots of transactions that on do SELECT but never do INSERT, UPDATE
>> or DELETE.
>> So I have free choice of sending COMMIT or ROLLBACK, it will make no
>> difference in the result.
>> But maybe there is a difference in the performance of that command?
>> What is the fastest way of finishing a read-only transaction: COMMIT or
>> ROLLBACK?
>>
>
>


Jason Hinsperger Posted on 2008-08-14 15:21:47.0Z
From: "Jason Hinsperger" <NO_jason_hinsperger_SPAM@hotmail.com>
Newsgroups: sybase.public.sqlanywhere
References: <48a2f190$1@forums-1-dub> <48a437bf@forums-1-dub> <48a44478$1@forums-1-dub>
Subject: Re: What is faster, COMMIT or ROLLBACK?
Lines: 51
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
X-RFC2646: Format=Flowed; Response
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3198
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <48a44d8b$1@forums-1-dub>
Date: 14 Aug 2008 08:21:47 -0700
X-Trace: forums-1-dub 1218727307 10.22.241.152 (14 Aug 2008 08:21:47 -0700)
X-Original-Trace: 14 Aug 2008 08:21:47 -0700, vip152.sybase.com
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.sqlanywhere:124
Article PK: 866572

I *think* (at least I hope) Markus was referring to the total execution time
for the transactions, not just the commit/rollback.

If you are only reading data, the only job commit/rollback has to do is
(optionally) close cursors and release locks. This should take the same
amount of time regardless of whether you are doing a commit or rollback.

--
Jason Hinsperger
Product Manager
Sybase iAnywhere
http://iablog.sybase.com/hinsperg

SQL Anywhere Developer Community
http://www.ianywhere.com/developer

"ps" <ps@.> wrote in message news:48a44478$1@forums-1-dub...
> The question would be : "why is sybase spending 20 seconds on a
> commit/rollback when the transaction was constituted from select
> statements only?"
>
> Markus KARG wrote:
>> I couldn't wait for the theoretical result, so I just tried it out.
>> Please comment if you like. :-)
>>
>> 5000 transactions terminated by COMMIT needed 20 seconds in my
>> environment.
>> 5000 transactions terminated by ROLLBACK needed 20 seconds in my
>> environment.
>>
>> So in reality it seems there are absolutely no difference as long as only
>> SELECT is used. :-)
>>
>> Have Fun
>> Markus
>>
>> "Markus KARG" <karg@quipsy.de> schrieb im Newsbeitrag
>> news:48a2f190$1@forums-1-dub...
>>> I have lots of transactions that on do SELECT but never do INSERT,
>>> UPDATE or DELETE.
>>> So I have free choice of sending COMMIT or ROLLBACK, it will make no
>>> difference in the result.
>>> But maybe there is a difference in the performance of that command?
>>> What is the fastest way of finishing a read-only transaction: COMMIT or
>>> ROLLBACK?
>>>
>>


Markus KARG Posted on 2008-08-15 12:29:13.0Z
From: "Markus KARG" <karg@quipsy.de>
Newsgroups: sybase.public.sqlanywhere
References: <48a2f190$1@forums-1-dub> <48a437bf@forums-1-dub> <48a44478$1@forums-1-dub> <48a44d8b$1@forums-1-dub>
Subject: Re: What is faster, COMMIT or ROLLBACK?
Lines: 68
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: <48a57699$1@forums-1-dub>
Date: 15 Aug 2008 05:29:13 -0700
X-Trace: forums-1-dub 1218803353 10.22.241.152 (15 Aug 2008 05:29:13 -0700)
X-Original-Trace: 15 Aug 2008 05:29:13 -0700, vip152.sybase.com
X-Authenticated-User: panorama
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.sqlanywhere:128
Article PK: 866568

Jason,

the time I mention was the total execution time of this loop:

for(int i = 0; i < 5000; i++) {
StartTransaction;
RunSelectStatement;
CommitTransaction;
}

Regards
Markus

"Jason Hinsperger" <NO_jason_hinsperger_SPAM@hotmail.com> schrieb im
Newsbeitrag news:48a44d8b$1@forums-1-dub...

>I *think* (at least I hope) Markus was referring to the total execution
>time for the transactions, not just the commit/rollback.
>
> If you are only reading data, the only job commit/rollback has to do is
> (optionally) close cursors and release locks. This should take the same
> amount of time regardless of whether you are doing a commit or rollback.
>
> --
> Jason Hinsperger
> Product Manager
> Sybase iAnywhere
> http://iablog.sybase.com/hinsperg
>
> SQL Anywhere Developer Community
> http://www.ianywhere.com/developer
>
>
>
>
> "ps" <ps@.> wrote in message news:48a44478$1@forums-1-dub...
>> The question would be : "why is sybase spending 20 seconds on a
>> commit/rollback when the transaction was constituted from select
>> statements only?"
>>
>> Markus KARG wrote:
>>> I couldn't wait for the theoretical result, so I just tried it out.
>>> Please comment if you like. :-)
>>>
>>> 5000 transactions terminated by COMMIT needed 20 seconds in my
>>> environment.
>>> 5000 transactions terminated by ROLLBACK needed 20 seconds in my
>>> environment.
>>>
>>> So in reality it seems there are absolutely no difference as long as
>>> only SELECT is used. :-)
>>>
>>> Have Fun
>>> Markus
>>>
>>> "Markus KARG" <karg@quipsy.de> schrieb im Newsbeitrag
>>> news:48a2f190$1@forums-1-dub...
>>>> I have lots of transactions that on do SELECT but never do INSERT,
>>>> UPDATE or DELETE.
>>>> So I have free choice of sending COMMIT or ROLLBACK, it will make no
>>>> difference in the result.
>>>> But maybe there is a difference in the performance of that command?
>>>> What is the fastest way of finishing a read-only transaction: COMMIT or
>>>> ROLLBACK?
>>>>
>>>
>


Markus KARG Posted on 2008-08-15 12:27:24.0Z
From: "Markus KARG" <karg@quipsy.de>
Newsgroups: sybase.public.sqlanywhere
References: <48a2f190$1@forums-1-dub> <48a437bf@forums-1-dub> <48a44478$1@forums-1-dub>
Subject: Re: What is faster, COMMIT or ROLLBACK?
Lines: 38
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: <48a5762c$1@forums-1-dub>
Date: 15 Aug 2008 05:27:24 -0700
X-Trace: forums-1-dub 1218803244 10.22.241.152 (15 Aug 2008 05:27:24 -0700)
X-Original-Trace: 15 Aug 2008 05:27:24 -0700, vip152.sybase.com
X-Authenticated-User: panorama
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.sqlanywhere:127
Article PK: 866577

You haven't correctly read my test results. Sybase is spending 20 seconds
for 5000 (!) commit/rollback.

Regards
Markus

"ps" <ps@.> schrieb im Newsbeitrag news:48a44478$1@forums-1-dub...

> The question would be : "why is sybase spending 20 seconds on a
> commit/rollback when the transaction was constituted from select
> statements only?"
>
> Markus KARG wrote:
>> I couldn't wait for the theoretical result, so I just tried it out.
>> Please comment if you like. :-)
>>
>> 5000 transactions terminated by COMMIT needed 20 seconds in my
>> environment.
>> 5000 transactions terminated by ROLLBACK needed 20 seconds in my
>> environment.
>>
>> So in reality it seems there are absolutely no difference as long as only
>> SELECT is used. :-)
>>
>> Have Fun
>> Markus
>>
>> "Markus KARG" <karg@quipsy.de> schrieb im Newsbeitrag
>> news:48a2f190$1@forums-1-dub...
>>> I have lots of transactions that on do SELECT but never do INSERT,
>>> UPDATE or DELETE.
>>> So I have free choice of sending COMMIT or ROLLBACK, it will make no
>>> difference in the result.
>>> But maybe there is a difference in the performance of that command?
>>> What is the fastest way of finishing a read-only transaction: COMMIT or
>>> ROLLBACK?
>>>
>>