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.

Enhance #tables to operate at isolation 0 for all statements

4 posts in Product Futures Discussion Last posting was on 2002-07-17 04:07:21.0Z
Frank_Hamersley Posted on 2002-07-03 07:53:11.0Z
From: "Frank_Hamersley" <terabite@bigpond.com>
Subject: Enhance #tables to operate at isolation 0 for all statements
Date: Wed, 3 Jul 2002 17:53:11 +1000
Lines: 23
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.50.4807.1700
X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
Message-ID: <cIZg8fmICHA.654@forums.sybase.com>
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: BPH-D2-p-235-80.tmns.net.au 144.134.235.80
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:402
Article PK: 93570

If not already delivered in ASE 12.5 (currently using 12.0.0.4) is it
possible for ASE to assume/enforce isolation 0 for all #tables?

The following statements would therefore not place any read locks on
db..table

set transaction isolation read uncommitted
select * into #table from db..table where idx = @criteria

The current ASE behaviour for this statement causes read locks (isolation 1
or more) on db..table because the insert part appears to override the
isolation 0 request.

I presume that this would not present a consistency problem as the scope of
#table is limited to my single threaded session, although as I understand
it, tempdb is used as the physical repository and may be a bit spooked by
such a relaxation.

Regards,

Frank Hamersley


Sethu M Posted on 2002-07-17 04:07:21.0Z
From: "Sethu M" <sethu@sybase.com>
References: <cIZg8fmICHA.654@forums.sybase.com>
Subject: Re: Enhance #tables to operate at isolation 0 for all statements
Date: Tue, 16 Jul 2002 21:07:21 -0700
Lines: 36
MIME-Version: 1.0
Content-Type: text/plain; charset="Windows-1252"
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2600.0000
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000
Message-ID: <F5oQVkULCHA.1004@forums.sybase.com>
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: 10.22.120.60
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:393
Article PK: 93564

Normally isolation levels are applied to conflicting lock requests
and/or isolating transactions.

What you are requesting is to apply the isolation level semantics
to the read cursors.

Will research this.

Sethu

"Frank_Hamersley" <terabite@bigpond.com> wrote in message
news:cIZg8fmICHA.654@forums.sybase.com...
If not already delivered in ASE 12.5 (currently using 12.0.0.4) is it
possible for ASE to assume/enforce isolation 0 for all #tables?

The following statements would therefore not place any read locks on
db..table

set transaction isolation read uncommitted
select * into #table from db..table where idx = @criteria

The current ASE behaviour for this statement causes read locks (isolation 1
or more) on db..table because the insert part appears to override the
isolation 0 request.

I presume that this would not present a consistency problem as the scope of
#table is limited to my single threaded session, although as I understand
it, tempdb is used as the physical repository and may be a bit spooked by
such a relaxation.

Regards,

Frank Hamersley


Rob Verschoor Posted on 2002-07-03 09:25:55.0Z
Reply-To: "Rob Verschoor" <rob@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY>
From: "Rob Verschoor" <rob@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY>
References: <cIZg8fmICHA.654@forums.sybase.com>
Subject: Re: Enhance #tables to operate at isolation 0 for all statements
Date: Wed, 3 Jul 2002 11:25:55 +0200
Lines: 51
Organization: Sypron B.V.
MIME-Version: 1.0
Content-Type: text/plain; charset="Windows-1252"
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.00.2919.6600
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2919.6600
Message-ID: <#tYmGVnICHA.1020@forums.sybase.com>
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: a74246.upc-a.chello.nl 62.163.74.246
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:400
Article PK: 93571


"Frank_Hamersley" <terabite@bigpond.com> wrote in message
news:cIZg8fmICHA.654@forums.sybase.com...
> If not already delivered in ASE 12.5 (currently using 12.0.0.4) is
it
> possible for ASE to assume/enforce isolation 0 for all #tables?
>
> The following statements would therefore not place any read locks on
> db..table
>
> set transaction isolation read uncommitted
> select * into #table from db..table where idx = @criteria
>
> The current ASE behaviour for this statement causes read locks
(isolation 1
> or more) on db..table because the insert part appears to override
the
> isolation 0 request.
>
> I presume that this would not present a consistency problem as the
scope of
> #table is limited to my single threaded session, although as I
understand
> it, tempdb is used as the physical repository and may be a bit
spooked by
> such a relaxation.
>
> Regards,
>
> Frank Hamersley
>

I suppose the only advantage is that no resources are spent on lock
management. Indeed no locks would seem to be required as there can
only be one user anyway. I think it would be a very small advantage,
BTW.

Rob
------------------------------------------------------------
Rob Verschoor

Certified Sybase Professional DBA for ASE 12.0/11.5/11.0

Author of "The Complete Sybase ASE Quick Reference Guide"
Online orders accepted at http://www.sypron.nl/qr

mailto:rob@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY
http://www.sypron.nl
Sypron B.V., P.O.Box 10695, 2501HR Den Haag, The Netherlands
------------------------------------------------------------


Frank_Hamersley Posted on 2002-07-04 00:50:53.0Z
From: "Frank_Hamersley" <terabite@bigpond.com>
References: <cIZg8fmICHA.654@forums.sybase.com> <#tYmGVnICHA.1020@forums.sybase.com>
Subject: Re: Enhance #tables to operate at isolation 0 for all statements
Date: Thu, 4 Jul 2002 10:50:53 +1000
Lines: 31
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.50.4807.1700
X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
Message-ID: <C7OZuYvICHA.195@forums.sybase.com>
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: BPH-D2-p-235-157.tmns.net.au 144.134.235.157
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:396
Article PK: 93568


"Rob Verschoor" wrote
> I suppose the only advantage is that no resources are spent on lock
> management. Indeed no locks would seem to be required as there can
> only be one user anyway. I think it would be a very small advantage,

Not so - the main goal is to avoid lock collisions on the source tables (and
likely mayhem for other system users)!

It appears ASE currently assesses any particular statements overall
isolation level as the maximum imposed by the various sub-statements it
contains. For example this batch...

set transaction isolation read uncommitted
select * into #table from db..table where key = @critera

... creates shared read locks on db..table and therefore interferes with
other users insert/update/delete activities...and it gets worse if a weak
"where" clause leads to a large number of rows being considered and the
establishment of a huge lock footprint on db..table.

In summary the "into" clause leads to an "insert" sub-statement which
naturally demands an isolation > 0 execution and the "select" supplying the
data seems to cop this increased isolation level too!

Well worth the investment by Sybase I would have thought.

Cheers

Frank.