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.

Changes in Lock and Commit Behavior from V9 > V11

4 posts in General Discussion Last posting was on 2010-02-23 22:22:15.0Z
Glenn Barber Posted on 2010-02-21 20:04:00.0Z
Sender: 7443.4b818f63.1804289383@sybase.com
From: Glenn Barber
Newsgroups: ianywhere.public.general
Subject: Changes in Lock and Commit Behavior from V9 > V11
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4b8191b0.749a.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 21 Feb 2010 12:04:00 -0800
X-Trace: forums-1-dub 1266782640 10.22.241.41 (21 Feb 2010 12:04:00 -0800)
X-Original-Trace: 21 Feb 2010 12:04:00 -0800, 10.22.241.41
Lines: 12
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:7910
Article PK: 6941

Our application has been running successfully for many years
on a V9 database.

Recently we upgraded the database to V11 and we are
beginning to see issues where we have had rollbacks which
seemed to remove what should have been committed
transactions.

Are there differences in locking, commit and rollback
between v9 and V11 that require special attention and
configuration - or should the upgraded database just work as
expected?


Glenn Paulley [Sybase iAnywhere] Posted on 2010-02-23 13:58:08.0Z
From: "Glenn Paulley [Sybase iAnywhere]" <paulley@ianywhere.com>
Reply-To: paulley@ianywhere.com
Organization: Sybase iAnywhere
User-Agent: Thunderbird 2.0.0.23 (Windows/20090812)
MIME-Version: 1.0
Newsgroups: ianywhere.public.general
Subject: Re: Changes in Lock and Commit Behavior from V9 > V11
References: <4b8191b0.749a.1681692777@sybase.com>
In-Reply-To: <4b8191b0.749a.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: <4b83def0$1@forums-1-dub>
Date: 23 Feb 2010 05:58:08 -0800
X-Trace: forums-1-dub 1266933488 10.22.241.152 (23 Feb 2010 05:58:08 -0800)
X-Original-Trace: 23 Feb 2010 05:58:08 -0800, vip152.sybase.com
Lines: 48
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:7923
Article PK: 6951

There are many changes to locking between V9 and V11 - these are
documented in the v11 help. Version 10 introduced intent locks and
snapshot isolation. In addition, both V10 and V11 acquire row locks at
slightly different points during query execution, which can affect the
precise timing of locking phenomena.

That said, however, there are no changes with isolation level 0 (no read
locks are acquired, reading of dirty rows is still permitted) and COMMIT
and ROLLBACK have not changed in terms of their semantics. If you have a
reproducible scenario where you believe a ROLLBACK undoes a committed
transaction, then I would urge you to open a case with technical support
so it can be investigated. At present we are unaware of any issues with
respect to incorrect COMMIT/ROLLBACK behaviour.

Glenn (Paulley)

Glenn Barber wrote:
> Our application has been running successfully for many years
> on a V9 database.
>
> Recently we upgraded the database to V11 and we are
> beginning to see issues where we have had rollbacks which
> seemed to remove what should have been committed
> transactions.
>
> Are there differences in locking, commit and rollback
> between v9 and V11 that require special attention and
> configuration - or should the upgraded database just work as
> expected?

--
Glenn Paulley
Director, Engineering (Query Processing)
Sybase iAnywhere

Blog: http://iablog.sybase.com/paulley

EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all

To Submit Bug Reports: http://case-express.sybase.com

SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288

Whitepapers, TechDocs, and bug fixes are all available through the
Sybase iAnywhere pages at
http://www.sybase.com/products/databasemanagement/sqlanywhere/technicalsupport


Glenn Barber Posted on 2010-02-23 21:14:02.0Z
Sender: 5f8f.4b8442c3.1804289383@sybase.com
From: Glenn Barber
Newsgroups: ianywhere.public.general
Subject: Re: Changes in Lock and Commit Behavior from V9 > V11
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4b84451a.6045.1681692777@sybase.com>
References: <4b83def0$1@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 23 Feb 2010 13:14:02 -0800
X-Trace: forums-1-dub 1266959642 10.22.241.41 (23 Feb 2010 13:14:02 -0800)
X-Original-Trace: 23 Feb 2010 13:14:02 -0800, 10.22.241.41
Lines: 60
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:7933
Article PK: 6961

Thanks Glenn

We will be investigating this to see if we can track it down
- right now the behaviour is rare.

What tools would I use to help diagnose the situation when
it occurs again?

> There are many changes to locking between V9 and V11 -
> these are documented in the v11 help. Version 10
> introduced intent locks and snapshot isolation. In
> addition, both V10 and V11 acquire row locks at slightly
> different points during query execution, which can affect
> the precise timing of locking phenomena.
>
> That said, however, there are no changes with isolation
> level 0 (no read locks are acquired, reading of dirty
> rows is still permitted) and COMMIT and ROLLBACK have not
> changed in terms of their semantics. If you have a
> reproducible scenario where you believe a ROLLBACK undoes
> a committed transaction, then I would urge you to open a
> case with technical support so it can be investigated. At
> present we are unaware of any issues with respect to
> incorrect COMMIT/ROLLBACK behaviour.
>
> Glenn (Paulley)
>
> Glenn Barber wrote:
> > Our application has been running successfully for many
> > years on a V9 database.
> >
> > Recently we upgraded the database to V11 and we are
> > beginning to see issues where we have had rollbacks
> > which seemed to remove what should have been committed
> > transactions.
> >
> > Are there differences in locking, commit and rollback
> > between v9 and V11 that require special attention and
> > configuration - or should the upgraded database just
> > work as expected?
>
> --
> Glenn Paulley
> Director, Engineering (Query Processing)
> Sybase iAnywhere
>
> Blog: http://iablog.sybase.com/paulley
>
> EBF's and Patches: http://downloads.sybase.com
> choose SQL Anywhere Studio >> change 'time frame' to all
>
> To Submit Bug Reports: http://case-express.sybase.com
>
> SQL Anywhere Studio Supported Platforms and Support Status
> http://my.sybase.com/detail?id=1002288
>
> Whitepapers, TechDocs, and bug fixes are all available
> through the Sybase iAnywhere pages at
>

http://www.sybase.com/products/databasemanagement/sqlanywhere/technicalsupport


Glenn Paulley [Sybase iAnywhere] Posted on 2010-02-23 22:22:15.0Z
From: "Glenn Paulley [Sybase iAnywhere]" <paulley@ianywhere.com>
Reply-To: paulley@ianywhere.com
Organization: Sybase iAnywhere
User-Agent: Thunderbird 2.0.0.23 (Windows/20090812)
MIME-Version: 1.0
Newsgroups: ianywhere.public.general
Subject: Re: Changes in Lock and Commit Behavior from V9 > V11
References: <4b83def0$1@forums-1-dub> <4b84451a.6045.1681692777@sybase.com>
In-Reply-To: <4b84451a.6045.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: <4b845517$1@forums-1-dub>
Date: 23 Feb 2010 14:22:15 -0800
X-Trace: forums-1-dub 1266963735 10.22.241.152 (23 Feb 2010 14:22:15 -0800)
X-Original-Trace: 23 Feb 2010 14:22:15 -0800, vip152.sybase.com
Lines: 99
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:7934
Article PK: 6963

You need to create a reproducible; since it's correctness that you're
investigating, it's going to be much harder to do than (say) finding a
poor access plan.

The first thing you'll need to do is to modify something (your
application, a trigger perhaps, an event, or something else) that
detects that you're getting a result set that you don't expect. From
that, you will want to establish the context during which the result set
was executed. You can use Application Profiling to trace the SQL
statements executed by the server for any (or a targeted) connection,
and then try to correlate the erroneous result with the outstanding
transactions that occurred at that time. Alternatively, you could also
DBTRAN the transaction log to discover all of the updates (and COMMITs)
but I think you'll find App Profiling easier, since it will also log
queries in the same chronological order as other statements.

HTH

Glenn

Glenn Barber wrote:
> Thanks Glenn
>
> We will be investigating this to see if we can track it down
> - right now the behaviour is rare.
>
> What tools would I use to help diagnose the situation when
> it occurs again?
>
>> There are many changes to locking between V9 and V11 -
>> these are documented in the v11 help. Version 10
>> introduced intent locks and snapshot isolation. In
>> addition, both V10 and V11 acquire row locks at slightly
>> different points during query execution, which can affect
>> the precise timing of locking phenomena.
>>
>> That said, however, there are no changes with isolation
>> level 0 (no read locks are acquired, reading of dirty
>> rows is still permitted) and COMMIT and ROLLBACK have not
>> changed in terms of their semantics. If you have a
>> reproducible scenario where you believe a ROLLBACK undoes
>> a committed transaction, then I would urge you to open a
>> case with technical support so it can be investigated. At
>> present we are unaware of any issues with respect to
>> incorrect COMMIT/ROLLBACK behaviour.
>>
>> Glenn (Paulley)
>>
>> Glenn Barber wrote:
>>> Our application has been running successfully for many
>>> years on a V9 database.
>>>
>>> Recently we upgraded the database to V11 and we are
>>> beginning to see issues where we have had rollbacks
>>> which seemed to remove what should have been committed
>>> transactions.
>>>
>>> Are there differences in locking, commit and rollback
>>> between v9 and V11 that require special attention and
>>> configuration - or should the upgraded database just
>>> work as expected?
>> --
>> Glenn Paulley
>> Director, Engineering (Query Processing)
>> Sybase iAnywhere
>>
>> Blog: http://iablog.sybase.com/paulley
>>
>> EBF's and Patches: http://downloads.sybase.com
>> choose SQL Anywhere Studio >> change 'time frame' to all
>>
>> To Submit Bug Reports: http://case-express.sybase.com
>>
>> SQL Anywhere Studio Supported Platforms and Support Status
>> http://my.sybase.com/detail?id=1002288
>>
>> Whitepapers, TechDocs, and bug fixes are all available
>> through the Sybase iAnywhere pages at
>>
> http://www.sybase.com/products/databasemanagement/sqlanywhere/technicalsupport

--
Glenn Paulley
Director, Engineering (Query Processing)
Sybase iAnywhere

Blog: http://iablog.sybase.com/paulley

EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all

To Submit Bug Reports: http://case-express.sybase.com

SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288

Whitepapers, TechDocs, and bug fixes are all available through the
Sybase iAnywhere pages at
http://www.sybase.com/products/databasemanagement/sqlanywhere/technicalsupport