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.

isolation level 0 and select into

6 posts in General Discussion Last posting was on 2009-06-09 16:04:16.0Z
Gareth Davies Posted on 2009-06-09 10:54:57.0Z
From: Gareth Davies <gruff@sitemaker.cc>
User-Agent: Thunderbird 2.0.0.21 (Windows/20090302)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: isolation level 0 and select into
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: <4a2e3f81$1@forums-1-dub>
Date: 9 Jun 2009 03:54:57 -0700
X-Trace: forums-1-dub 1244544897 10.22.241.152 (9 Jun 2009 03:54:57 -0700)
X-Original-Trace: 9 Jun 2009 03:54:57 -0700, vip152.sybase.com
Lines: 20
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:27828
Article PK: 77080

Is isolation level 0 (dirty reads) incompatible with:

SELECT a,b,c
INTO #T
FROM A

or
INSERT #T
SELECT a,b,c
FROM A

?

I don't want to hold locks on table A that I'm reading from to create my
work table but Sybase seems to ignore the isolation level / FOR READ
ONLY irrespective of which method I try.

Thanks

Gareth


"Mark A. Parsons" <iron_horse Posted on 2009-06-09 10:57:39.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Thunderbird 1.5.0.10 (Windows/20070221)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: isolation level 0 and select into
References: <4a2e3f81$1@forums-1-dub>
In-Reply-To: <4a2e3f81$1@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Antivirus: avast! (VPS 090606-0, 06/06/2009), Outbound message
X-Antivirus-Status: Clean
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4a2e4023$1@forums-1-dub>
Date: 9 Jun 2009 03:57:39 -0700
X-Trace: forums-1-dub 1244545059 10.22.241.152 (9 Jun 2009 03:57:39 -0700)
X-Original-Trace: 9 Jun 2009 03:57:39 -0700, vip152.sybase.com
Lines: 23
X-Authenticated-User: TeamSybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:27829
Article PK: 77079

What do you mean by 'Sybase seems to ignore the isolation level'?

Gareth Davies wrote:
> Is isolation level 0 (dirty reads) incompatible with:
>
> SELECT a,b,c
> INTO #T
> FROM A
>
> or
> INSERT #T
> SELECT a,b,c
> FROM A
>
> ?
>
> I don't want to hold locks on table A that I'm reading from to create my
> work table but Sybase seems to ignore the isolation level / FOR READ
> ONLY irrespective of which method I try.
>
> Thanks
>
> Gareth


Carl Kayser Posted on 2009-06-09 11:53:24.0Z
From: "Carl Kayser" <kayser_c@bls.gov>
Newsgroups: sybase.public.ase.general
References: <4a2e3f81$1@forums-1-dub> <4a2e4023$1@forums-1-dub>
Subject: Re: isolation level 0 and select into
Lines: 32
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3350
X-RFC2646: Format=Flowed; Response
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4a2e4d34@forums-1-dub>
Date: 9 Jun 2009 04:53:24 -0700
X-Trace: forums-1-dub 1244548404 10.22.241.152 (9 Jun 2009 04:53:24 -0700)
X-Original-Trace: 9 Jun 2009 04:53:24 -0700, vip152.sybase.com
X-Authenticated-User: ase1251
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:27830
Article PK: 77082


"Mark A. Parsons" <iron_horse@no_spamola.compuserve.com> wrote in message
news:4a2e4023$1@forums-1-dub...
> What do you mean by 'Sybase seems to ignore the isolation level'?
>
> Gareth Davies wrote:
>> Is isolation level 0 (dirty reads) incompatible with:
>>
>> SELECT a,b,c
>> INTO #T
>> FROM A
>>
>> or
>> INSERT #T
>> SELECT a,b,c
>> FROM A
>>
>> ?
>>
>> I don't want to hold locks on table A that I'm reading from to create my
>> work table but Sybase seems to ignore the isolation level / FOR READ ONLY
>> irrespective of which method I try.
>>
>> Thanks
>>
>> Gareth

The above commands don't allow for the option of "at read uncommitted", etc.
Errors 7375 and 7377 will occur with isql. (It might be that his front-end
doesn't display the error messages.)


"Mark A. Parsons" <iron_horse Posted on 2009-06-09 12:36:08.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Thunderbird 1.5.0.10 (Windows/20070221)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: isolation level 0 and select into
References: <4a2e3f81$1@forums-1-dub> <4a2e4023$1@forums-1-dub> <4a2e4d34@forums-1-dub>
In-Reply-To: <4a2e4d34@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Antivirus: avast! (VPS 090606-0, 06/06/2009), Outbound message
X-Antivirus-Status: Clean
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4a2e5738$1@forums-1-dub>
Date: 9 Jun 2009 05:36:08 -0700
X-Trace: forums-1-dub 1244550968 10.22.241.152 (9 Jun 2009 05:36:08 -0700)
X-Original-Trace: 9 Jun 2009 05:36:08 -0700, vip152.sybase.com
Lines: 103
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:27831
Article PK: 77081

Yes and no.

If the OP were to add an isolation level clause to the SELECT statements then yes, an error would occur.

If the OP were to set the isolation level at the sesssion level then no, no error would occur. What happens then
depends on what's going on with table A ... the SELECTs would most likely succeed if no one is holding an exclusive lock
on table A, or the SELECTs may hang (ie, block) if someone is holding an exclusive lock on table A. And the SELECTs
could block another session attempting to write to table A.

========================
-- session #1

1> select * into #t1 from t1 at isolation 0
2> go

Msg 7375, Level 15, State 1:
Server 'db_dr', Line 1:
SELECT INTO cannot be specified with isolation level clause.

1> set transaction isolation level 0
2> go

1> select * into #t1 from t1
2> go

(577 rows affected)

1> drop table #t1
2> go

-- session #2

1> begin tran
2> update t1 set c=c+1 where a = 2055675340
3> go

-- session #2 now holding an exclusive lock on part of t1

-- session #1

1> set transaction isolation level 0
2> go

1> select * into #t1 from t1
2> go

-- session #1 hangs/blocks on session #2's exclusive lock;
-- isolation level 0 not enabled for the select/into

-- session #3
-- while session #2 is still holding exclusive lock on t1

1> set transaction isolation level 0
2> go

1> select * from t1
2> go

-- result set generated ...

-- all rows are returned from t1;
-- isolation level 0 is enabled for the select
========================

The isolation level setting typically applies to read only queries ... while the OP is running queries that perform
writes ... so the isolation level is basically ignored.

I was more intersted in what behaviour the OP a) is seeing and b) hopes to see.

Carl Kayser wrote:
> "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com> wrote in message
> news:4a2e4023$1@forums-1-dub...
>> What do you mean by 'Sybase seems to ignore the isolation level'?
>>
>> Gareth Davies wrote:
>>> Is isolation level 0 (dirty reads) incompatible with:
>>>
>>> SELECT a,b,c
>>> INTO #T
>>> FROM A
>>>
>>> or
>>> INSERT #T
>>> SELECT a,b,c
>>> FROM A
>>>
>>> ?
>>>
>>> I don't want to hold locks on table A that I'm reading from to create my
>>> work table but Sybase seems to ignore the isolation level / FOR READ ONLY
>>> irrespective of which method I try.
>>>
>>> Thanks
>>>
>>> Gareth
>
> The above commands don't allow for the option of "at read uncommitted", etc.
> Errors 7375 and 7377 will occur with isql. (It might be that his front-end
> doesn't display the error messages.)
>
>


Gareth Davies Posted on 2009-06-09 13:15:56.0Z
From: Gareth Davies <gruff@sitemaker.cc>
User-Agent: Thunderbird 2.0.0.21 (Windows/20090302)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: isolation level 0 and select into
References: <4a2e3f81$1@forums-1-dub> <4a2e4023$1@forums-1-dub>
In-Reply-To: <4a2e4023$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: <4a2e608c$1@forums-1-dub>
Date: 9 Jun 2009 06:15:56 -0700
X-Trace: forums-1-dub 1244553356 10.22.241.152 (9 Jun 2009 06:15:56 -0700)
X-Original-Trace: 9 Jun 2009 06:15:56 -0700, vip152.sybase.com
Lines: 28
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:27832
Article PK: 77083

I mean if I try "set isolation level 0" or use "for read only" with
either of those select statements then sh_intent locks are still taken
out on table A.

Mark A. Parsons wrote:
> What do you mean by 'Sybase seems to ignore the isolation level'?
>
> Gareth Davies wrote:
>> Is isolation level 0 (dirty reads) incompatible with:
>>
>> SELECT a,b,c
>> INTO #T
>> FROM A
>>
>> or
>> INSERT #T
>> SELECT a,b,c
>> FROM A
>>
>> ?
>>
>> I don't want to hold locks on table A that I'm reading from to create
>> my work table but Sybase seems to ignore the isolation level / FOR
>> READ ONLY irrespective of which method I try.
>>
>> Thanks
>>
>> Gareth


Bret Halford [Sybase] Posted on 2009-06-09 16:04:16.0Z
From: "Bret Halford [Sybase]" <bret@sybase.com>
Organization: Sybase, Inc.
User-Agent: Thunderbird 2.0.0.21 (Windows/20090302)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: isolation level 0 and select into
References: <4a2e3f81$1@forums-1-dub> <4a2e4023$1@forums-1-dub> <4a2e608c$1@forums-1-dub>
In-Reply-To: <4a2e608c$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: <4a2e8800$1@forums-1-dub>
Date: 9 Jun 2009 09:04:16 -0700
X-Trace: forums-1-dub 1244563456 10.22.241.152 (9 Jun 2009 09:04:16 -0700)
X-Original-Trace: 9 Jun 2009 09:04:16 -0700, vip152.sybase.com
Lines: 14
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:27834
Article PK: 77085


Gareth Davies wrote:
> I mean if I try "set isolation level 0" or use "for read only" with
> either of those select statements then sh_intent locks are still taken
> out on table A.

This is expected behavior (although I understand the desire to have it
otherwise). isolation level 0 can only be used for pure "read-only"
statements. Doc CR 546900 is open to have this restriction better
described in the documentation. Feature request 169328 exists to
have this restriction lifted, but there doesn't seem to be enough
customer interest to have it implemented.

-bret