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.

per table isolation levels within a select statement

3 posts in Product Futures Discussion Last posting was on 2002-07-31 01:29:07.0Z
Frank_Hamersley Posted on 2002-07-03 08:12:54.0Z
From: "Frank_Hamersley" <terabite@bigpond.com>
Subject: per table isolation levels within a select statement
Date: Wed, 3 Jul 2002 18:12:54 +1000
Lines: 22
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: <d5V4v4mICHA.378@forums.sybase.com>
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: KTPP-p-203-54-171-152.prem.tmns.net.au 203.54.171.152
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:401
Article PK: 93569

A further extension (to my previous post re isolation 0 on #tables) is for
ASE to support discrete isolation levels on each table in a select clause.
An example statement might look like ...

select T2.data
from db..table1 T1 at isolation 2,
db..table2 T2 at isolation 0
where T1.batch < @batch
and T1.batch_closed = 1
and T2.key = T1.key

This would be very useful in avoiding lock problems on table2 if it was a
very active table and consistency of the data returned was assured by the
batch being closed i.e. application sponsored certainty.

Any comments?

Regards

Frank Hamersley.


Jason Webster Posted on 2002-07-26 12:57:14.0Z
From: "Jason Webster" <jason.webster`@mail.state.ky.us>
References: <d5V4v4mICHA.378@forums.sybase.com>
Subject: Re: per table isolation levels within a select statement
Date: Fri, 26 Jul 2002 08:57:14 -0400
Lines: 31
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: <$D9O7VKNCHA.317@forums.sybase.com>
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: 205.204.186.5
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:367
Article PK: 93536

Would this include selects that follow inserts and subqueries that qualify
updates and deletes, or are we just talking about situations where it is
just an old fashioned select?

Jason Webster

"Frank_Hamersley" <terabite@bigpond.com> wrote in message
news:d5V4v4mICHA.378@forums.sybase.com...
> A further extension (to my previous post re isolation 0 on #tables) is for
> ASE to support discrete isolation levels on each table in a select clause.
> An example statement might look like ...
>
> select T2.data
> from db..table1 T1 at isolation 2,
> db..table2 T2 at isolation 0
> where T1.batch < @batch
> and T1.batch_closed = 1
> and T2.key = T1.key
>
> This would be very useful in avoiding lock problems on table2 if it was a
> very active table and consistency of the data returned was assured by the
> batch being closed i.e. application sponsored certainty.
>
> Any comments?
>
> Regards
>
> Frank Hamersley.
>
>


Frank_Hamersley Posted on 2002-07-31 01:29:07.0Z
From: "Frank_Hamersley" <terabite@bigpond.com>
References: <d5V4v4mICHA.378@forums.sybase.com> <$D9O7VKNCHA.317@forums.sybase.com>
Subject: Re: per table isolation levels within a select statement
Date: Wed, 31 Jul 2002 11:29:07 +1000
Lines: 40
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: <QRLNUMDOCHA.582@forums.sybase.com>
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: KTPP-p-203-54-171-31.prem.tmns.net.au 203.54.171.31
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:350
Article PK: 93519


"Jason Webster" wrote
> Would this include selects that follow inserts and subqueries that qualify
> updates and deletes, or are we just talking about situations where it is
> just an old fashioned select?

I can't see why it couldn't be made available (only by explicit choice as
indicated) on insert queries (example 1) or subqueries (example 2).

Basically the responsibility to understand the implications lies with the
developer. This presumes they know their application to judge when it is
safe to use the technique, but still provides an option to optimize
activities that might normally pose a problem on a very active TP system.

Cheers,

Frank.

(1)
select T1.batch_id, T2.data
into #batchdata
from db..table1 T1 at isolation 2,
db..table2 T2 at isolation 0
where T1.batch_date = @today
and T1.batch_closed = 1
and T2.batch_id = T1.batch_id

(2)
update db..table1
set status = T2.final_status
from db..table1 T1,
db..table2 T2 at isolation 0
where T1.batch_date = @today
and T1.batch_closed = 1
and T2.batch_id = T1.batch_id

In each example the gathering of T2 records can proceed at isolation 0
because the coder knows that a closed batch is not subject to any further
change from other sources.