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 0 and update locking

4 posts in General Discussion Last posting was on 2005-07-14 22:25:49.0Z
Tom Posted on 2005-07-14 14:07:26.0Z
Sender: 6165.42d67076.1804289383@sybase.com
From: Tom
Newsgroups: ianywhere.public.general
Subject: isolation 0 and update locking
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <42d67196.6178.1681692777@sybase.com>
X-Original-NNTP-Posting-Host: 10.22.241.42
X-Original-Trace: 14 Jul 2005 07:07:18 -0700, 10.22.241.42
Lines: 12
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 14 Jul 2005 07:07:19 -0700, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 14 Jul 2005 07:07:26 -0700
X-Trace: forums-1-dub 1121350046 10.22.108.75 (14 Jul 2005 07:07:26 -0700)
X-Original-Trace: 14 Jul 2005 07:07:26 -0700, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:4620
Article PK: 8431

I used two machine with isqlc for this testing on ASA 7 on
win2003 server.

update the same record from two machine without commit.
The second update just hangs. Both of them at isolation 0

Is hanging of the second update statement expected ??

After commit the first update at the other machine, the
second update went through.

Thank you


Greg Fenton Posted on 2005-07-14 16:21:07.0Z
From: Greg Fenton <greg.fenton_NOSPAM_@ianywhere.com>
Organization: iAnywhere Solutions Inc.
User-Agent: Mozilla Thunderbird 1.6.3.2f (Windows/20050317)
X-Accept-Language: en-us, en
MIME-Version: 1.0
Newsgroups: ianywhere.public.general
Subject: Re: isolation 0 and update locking
References: <42d67196.6178.1681692777@sybase.com>
In-Reply-To: <42d67196.6178.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: gfenton-t30.sybase.com
X-Original-NNTP-Posting-Host: gfenton-t30.sybase.com
Message-ID: <42d690f3@forums-1-dub>
Date: 14 Jul 2005 09:21:07 -0700
X-Trace: forums-1-dub 1121358067 10.25.98.243 (14 Jul 2005 09:21:07 -0700)
X-Original-Trace: 14 Jul 2005 09:21:07 -0700, gfenton-t30.sybase.com
Lines: 23
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:4622
Article PK: 8427


Tom wrote:
>
> update the same record from two machine without commit.
> The second update just hangs. Both of them at isolation 0
>
> Is hanging of the second update statement expected ??
>

Yes, absolutely. Isolation level only affects the way connections
behave during *read* operations. If both connections are doing *write*
operations to the same row then the second connection will either block
or it will received an error, depending on the setting of the BLOCKING
option (which defaults to 'on'...thus explaining the exact behaviour you
are seeing).

greg.fenton
--
Greg Fenton
Consultant, Solution Services, iAnywhere Solutions
--------
Visit the iAnywhere Solutions Developer Community
Whitepapers, TechDocs, Downloads
http://www.ianywhere.com/developer/


Greg Fenton Posted on 2005-07-14 22:25:49.0Z
From: Greg Fenton <greg.fenton_NOSPAM_@ianywhere.com>
Organization: iAnywhere Solutions Inc.
User-Agent: Mozilla Thunderbird 1.6.3.2f (Windows/20050317)
X-Accept-Language: en-us, en
MIME-Version: 1.0
Newsgroups: ianywhere.public.general
Subject: Re: isolation 0 and update locking
References: <42d67196.6178.1681692777@sybase.com> <42d690f3@forums-1-dub>
In-Reply-To: <42d690f3@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Original-NNTP-Posting-Host: vpn-concord-021.sybase.com
Message-ID: <42d6e668$1@forums-2-dub>
X-Original-Trace: 14 Jul 2005 15:25:44 -0700, vpn-concord-021.sybase.com
Lines: 28
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 14 Jul 2005 15:25:46 -0700, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 14 Jul 2005 15:25:49 -0700
X-Trace: forums-1-dub 1121379949 10.22.108.75 (14 Jul 2005 15:25:49 -0700)
X-Original-Trace: 14 Jul 2005 15:25:49 -0700, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:4626
Article PK: 8439


Greg Fenton wrote:
>
> Yes, absolutely. Isolation level only affects the way connections
> behave during *read* operations.

I should have been clearer with this statement. A connection's
isolation level will affect the way *it* behaves during read operations;
that is, the connection will or will not block when doing read
operations over rows that have write locks on them depending on the
isolation level.

A connection's isolation level might affect the write operations of
*other* connections as more read locks are acquired and are kept for
longer periods of time as the isolation level is increased.

A connection's isolation level will not affect how *it* behaves during a
write operation. All write operations will block (or get an error if
BLOCKING='off') if trying to operate on a row that already has a write lock.

greg.fenton
--
Greg Fenton
Consultant, Solution Services, iAnywhere Solutions
--------
Visit the iAnywhere Solutions Developer Community
Whitepapers, TechDocs, Downloads
http://www.ianywhere.com/developer/


Breck Carter [TeamSybase] Posted on 2005-07-14 19:03:39.0Z
From: "Breck Carter [TeamSybase]" <NOSPAM__bcarter@risingroad.com>
Newsgroups: ianywhere.public.general
Subject: Re: isolation 0 and update locking
Organization: RisingRoad Professional Services
Reply-To: NOSPAM__bcarter@risingroad.com
Message-ID: <a9ddd1dpokb6kplcgo0lrcb5i7hl8fi47k@4ax.com>
References: <42d67196.6178.1681692777@sybase.com>
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: 12.24.172.2
X-Original-NNTP-Posting-Host: 12.24.172.2
Date: 14 Jul 2005 12:03:39 -0700
X-Trace: forums-1-dub 1121367819 12.24.172.2 (14 Jul 2005 12:03:39 -0700)
X-Original-Trace: 14 Jul 2005 12:03:39 -0700, 12.24.172.2
Lines: 26
X-Authenticated-User: TeamSybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:4625
Article PK: 8430

FWIW the book (see below) contains detailed explanations of locking,
blocking and isolation levels, complete with examples.

Breck

On 14 Jul 2005 07:07:26 -0700, Tom wrote:

>I used two machine with isqlc for this testing on ASA 7 on
>win2003 server.
>
>update the same record from two machine without commit.
>The second update just hangs. Both of them at isolation 0
>
>Is hanging of the second update statement expected ??
>
>After commit the first update at the other machine, the
>second update went through.
>
>Thank you

--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/ASIN/1556225067/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com