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.

Bizarre lock problem with iAnywhere JDBC SA 11.0.1.2376

9 posts in General Discussion Last posting was on 2010-02-25 13:36:29.0Z
Bill Williams Posted on 2010-02-19 17:10:08.0Z
From: "Bill Williams" <billwilliams@qcsoftware.com>
Newsgroups: ianywhere.public.general
Subject: Bizarre lock problem with iAnywhere JDBC SA 11.0.1.2376
Lines: 4879
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5843
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4b7ec5f0@forums-1-dub>
Date: 19 Feb 2010 09:10:08 -0800
X-Trace: forums-1-dub 1266599408 10.22.241.152 (19 Feb 2010 09:10:08 -0800)
X-Original-Trace: 19 Feb 2010 09:10:08 -0800, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:7902
Article PK: 6934

I have two different applications that are trying to work with the same
records and having a bizarre locking issue. First let me start by saying if
I use the jConnect JDBC driver for both applications, I have no problem.

The database is running on windows server 2003 running SA 11.0.1.2376.

The two programs are using connection names orderMgr and rfMgr.

The rfMgr program is using iAnywhere from SA 11.0.1.2376 while the orderMgr
is using jConnect.

The flow is this:

The rfMgr modifies some records, performs a commit and then sends a message
to the orderMgr requesting it make some more changes to these records.

The orderMgr attempts to do an update on the WorkOrders table and it blocks
against the rfMgr connection.

I have attached a document with two different Sybase Central screen shots.
One is the connection screen showing the blocked connection information.
You will see connection 311 is the rfMgr and the last request type was a
commit. You will also see connection 297, which is the orderMgr, is blocked
on 311.

The second screen shot is the table locks screen showing the locks that
exist. I'm not familiar with the newer display of locks types (I'm use to
ASA 9) but you can see the rfMgr is holding a row lock on the same row the
orderMgr is trying to update.

I did verify in the code that the rfMgr does a commit and then sends a
message to the orderMgr and does not do any other database access after the
commit.

The bad thing is the rfMgr is waiting for a response message from the
orderMgr before it goes on and since the orderMgr is blocked on the rfMgr's
connection, I basically have a deadlock.


"Kory Hodgson (Sybase iAnywhere)" <khodgson Posted on 2010-02-19 21:22:56.0Z
From: "Kory Hodgson (Sybase iAnywhere)" <khodgson@A_SPAM_FREE_sybase.com>
User-Agent: Thunderbird 2.0.0.23 (Windows/20090812)
MIME-Version: 1.0
Newsgroups: ianywhere.public.general
Subject: Re: Bizarre lock problem with iAnywhere JDBC SA 11.0.1.2376
References: <4b7ec5f0@forums-1-dub>
In-Reply-To: <4b7ec5f0@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: <4b7f0130$1@forums-1-dub>
Date: 19 Feb 2010 13:22:56 -0800
X-Trace: forums-1-dub 1266614576 10.22.241.152 (19 Feb 2010 13:22:56 -0800)
X-Original-Trace: 19 Feb 2010 13:22:56 -0800, vip152.sybase.com
Lines: 60
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:7905
Article PK: 6935

Bill,

I don't know for sure that this is the cause, but the iAnywhere JDBC
driver has a default isolation level of 0 where the JConnect driver has
a default isolation level of 1
(http://dcx.sybase.com/index.html#1101en/dbadmin_en11/isolation-level-option.html).

It appears that the orderManager has shared locks on the table that
rfMgr is trying to commit to, and since orderMgr is at isolation level 1
I beleive this will block the commit hence your deadlock scenario.

You can likely resolve this by setting the JDBC connection's isolation
level to 1
(http://dcx.sybase.com/index.html#1101en/dbusage_en11/udtchan.html) to
simulate the way the two JConnect connections were behaving.

Kory Hodgson
Sybase iAnywhere

Bill Williams wrote:
> I have two different applications that are trying to work with the same
> records and having a bizarre locking issue. First let me start by saying if
> I use the jConnect JDBC driver for both applications, I have no problem.
>
> The database is running on windows server 2003 running SA 11.0.1.2376.
>
> The two programs are using connection names orderMgr and rfMgr.
>
> The rfMgr program is using iAnywhere from SA 11.0.1.2376 while the orderMgr
> is using jConnect.
>
> The flow is this:
>
> The rfMgr modifies some records, performs a commit and then sends a message
> to the orderMgr requesting it make some more changes to these records.
>
> The orderMgr attempts to do an update on the WorkOrders table and it blocks
> against the rfMgr connection.
>
> I have attached a document with two different Sybase Central screen shots.
> One is the connection screen showing the blocked connection information.
> You will see connection 311 is the rfMgr and the last request type was a
> commit. You will also see connection 297, which is the orderMgr, is blocked
> on 311.
>
> The second screen shot is the table locks screen showing the locks that
> exist. I'm not familiar with the newer display of locks types (I'm use to
> ASA 9) but you can see the rfMgr is holding a row lock on the same row the
> orderMgr is trying to update.
>
> I did verify in the code that the rfMgr does a commit and then sends a
> message to the orderMgr and does not do any other database access after the
> commit.
>
> The bad thing is the rfMgr is waiting for a response message from the
> orderMgr before it goes on and since the orderMgr is blocked on the rfMgr's
> connection, I basically have a deadlock.
>
>


Glenn Paulley [Sybase iAnywhere] Posted on 2010-02-19 21:44:46.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: Bizarre lock problem with iAnywhere JDBC SA 11.0.1.2376
References: <4b7ec5f0@forums-1-dub> <4b7f0130$1@forums-1-dub>
In-Reply-To: <4b7f0130$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: <4b7f064e$1@forums-1-dub>
Date: 19 Feb 2010 13:44:46 -0800
X-Trace: forums-1-dub 1266615886 10.22.241.152 (19 Feb 2010 13:44:46 -0800)
X-Original-Trace: 19 Feb 2010 13:44:46 -0800, vip152.sybase.com
Lines: 113
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:7906
Article PK: 6936

The server does not acquire read position locks at isolation level 0, so
Kory's suggestion below will not solve the problem. From the evidence
you posted, I believe you are already running at isolation level 1 on
both connections.

A plausible explanation is as follows.

By default, with the SQL Anywhere JDBC drivers, JDBC cursors are opened
WITH HOLD. If your application does not close all open cursors before
executing the COMMIT, then these cursors will remain open and, I
believe, both position and long-term read locks will not be released
upon the COMMIT. Peter Bumbulis in Engineering is verifying for me the
precise nature of the server's locking mechanisms when a WITH HOLD
cursor is involved, and I hope to be able to post a follow-up message to
that effect early next week.

With jConnect, cursors are fire-hose cursors; after OPEN, all of the
rows in the cursor are sent to the client (even if the client only wants
the first row with a single FETCH). Consequently, when you open a cursor
in your application using jConnect, the result set is scrolled all the
way to the end, and the cursor's position will be at the end of the
result set. Hence no cursor will retain an isolation level 1 read lock
on any row (since each server-side cursor is positioned at the end of
its result set) and your application won't run into the deadlock scenario.

My suggestion, assuming my guess is correct: close the offending
cursor(s) before you issue the COMMIT, and see what happens.

Glenn

Kory Hodgson (Sybase iAnywhere) wrote:
> Bill,
>
> I don't know for sure that this is the cause, but the iAnywhere JDBC
> driver has a default isolation level of 0 where the JConnect driver has
> a default isolation level of 1
> (http://dcx.sybase.com/index.html#1101en/dbadmin_en11/isolation-level-option.html).
>
>
> It appears that the orderManager has shared locks on the table that
> rfMgr is trying to commit to, and since orderMgr is at isolation level 1
> I beleive this will block the commit hence your deadlock scenario.
>
> You can likely resolve this by setting the JDBC connection's isolation
> level to 1
> (http://dcx.sybase.com/index.html#1101en/dbusage_en11/udtchan.html) to
> simulate the way the two JConnect connections were behaving.
>
> Kory Hodgson
> Sybase iAnywhere
>
>
> Bill Williams wrote:
>> I have two different applications that are trying to work with the
>> same records and having a bizarre locking issue. First let me start
>> by saying if I use the jConnect JDBC driver for both applications, I
>> have no problem.
>>
>> The database is running on windows server 2003 running SA 11.0.1.2376.
>>
>> The two programs are using connection names orderMgr and rfMgr.
>>
>> The rfMgr program is using iAnywhere from SA 11.0.1.2376 while the
>> orderMgr is using jConnect.
>>
>> The flow is this:
>>
>> The rfMgr modifies some records, performs a commit and then sends a
>> message to the orderMgr requesting it make some more changes to these
>> records.
>>
>> The orderMgr attempts to do an update on the WorkOrders table and it
>> blocks against the rfMgr connection.
>>
>> I have attached a document with two different Sybase Central screen
>> shots. One is the connection screen showing the blocked connection
>> information. You will see connection 311 is the rfMgr and the last
>> request type was a commit. You will also see connection 297, which is
>> the orderMgr, is blocked on 311.
>>
>> The second screen shot is the table locks screen showing the locks
>> that exist. I'm not familiar with the newer display of locks types
>> (I'm use to ASA 9) but you can see the rfMgr is holding a row lock on
>> the same row the orderMgr is trying to update.
>>
>> I did verify in the code that the rfMgr does a commit and then sends a
>> message to the orderMgr and does not do any other database access
>> after the commit.
>>
>> The bad thing is the rfMgr is waiting for a response message from the
>> orderMgr before it goes on and since the orderMgr is blocked on the
>> rfMgr's connection, I basically have a deadlock.
>>
>>

--
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


Karim Khamis [Sybase iAnywhere] Posted on 2010-02-19 21:50:20.0Z
From: "Karim Khamis [Sybase iAnywhere]" <kkhamis@sybase.com>
User-Agent: Thunderbird 2.0.0.23 (Windows/20090812)
MIME-Version: 1.0
Newsgroups: ianywhere.public.general
Subject: Re: Bizarre lock problem with iAnywhere JDBC SA 11.0.1.2376
References: <4b7ec5f0@forums-1-dub> <4b7f0130$1@forums-1-dub> <4b7f064e$1@forums-1-dub>
In-Reply-To: <4b7f064e$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: <4b7f079c$1@forums-1-dub>
Date: 19 Feb 2010 13:50:20 -0800
X-Trace: forums-1-dub 1266616220 10.22.241.152 (19 Feb 2010 13:50:20 -0800)
X-Original-Trace: 19 Feb 2010 13:50:20 -0800, vip152.sybase.com
Lines: 105
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:7907
Article PK: 6937

Bill,

I agree with Glenn's hypothesis and suggestion. Note that the easiest
way to "close the offending cursors" is to explicitly call close() on
the Statement/PreparedStatement object. Make note of Glenn's suggestion
to call close() on the Statement/PreparedStatement object BEFORE calling
Connection.commit().

Karim

Glenn Paulley [Sybase iAnywhere] wrote:
> The server does not acquire read position locks at isolation level 0, so
> Kory's suggestion below will not solve the problem. From the evidence
> you posted, I believe you are already running at isolation level 1 on
> both connections.
>
> A plausible explanation is as follows.
>
> By default, with the SQL Anywhere JDBC drivers, JDBC cursors are opened
> WITH HOLD. If your application does not close all open cursors before
> executing the COMMIT, then these cursors will remain open and, I
> believe, both position and long-term read locks will not be released
> upon the COMMIT. Peter Bumbulis in Engineering is verifying for me the
> precise nature of the server's locking mechanisms when a WITH HOLD
> cursor is involved, and I hope to be able to post a follow-up message to
> that effect early next week.
>
> With jConnect, cursors are fire-hose cursors; after OPEN, all of the
> rows in the cursor are sent to the client (even if the client only wants
> the first row with a single FETCH). Consequently, when you open a cursor
> in your application using jConnect, the result set is scrolled all the
> way to the end, and the cursor's position will be at the end of the
> result set. Hence no cursor will retain an isolation level 1 read lock
> on any row (since each server-side cursor is positioned at the end of
> its result set) and your application won't run into the deadlock scenario.
>
> My suggestion, assuming my guess is correct: close the offending
> cursor(s) before you issue the COMMIT, and see what happens.
>
> Glenn
>
> Kory Hodgson (Sybase iAnywhere) wrote:
>> Bill,
>>
>> I don't know for sure that this is the cause, but the iAnywhere JDBC
>> driver has a default isolation level of 0 where the JConnect driver
>> has a default isolation level of 1
>> (http://dcx.sybase.com/index.html#1101en/dbadmin_en11/isolation-level-option.html).
>>
>>
>> It appears that the orderManager has shared locks on the table that
>> rfMgr is trying to commit to, and since orderMgr is at isolation level
>> 1 I beleive this will block the commit hence your deadlock scenario.
>>
>> You can likely resolve this by setting the JDBC connection's isolation
>> level to 1
>> (http://dcx.sybase.com/index.html#1101en/dbusage_en11/udtchan.html) to
>> simulate the way the two JConnect connections were behaving.
>>
>> Kory Hodgson
>> Sybase iAnywhere
>>
>>
>> Bill Williams wrote:
>>> I have two different applications that are trying to work with the
>>> same records and having a bizarre locking issue. First let me start
>>> by saying if I use the jConnect JDBC driver for both applications, I
>>> have no problem.
>>>
>>> The database is running on windows server 2003 running SA 11.0.1.2376.
>>>
>>> The two programs are using connection names orderMgr and rfMgr.
>>>
>>> The rfMgr program is using iAnywhere from SA 11.0.1.2376 while the
>>> orderMgr is using jConnect.
>>>
>>> The flow is this:
>>>
>>> The rfMgr modifies some records, performs a commit and then sends a
>>> message to the orderMgr requesting it make some more changes to these
>>> records.
>>>
>>> The orderMgr attempts to do an update on the WorkOrders table and it
>>> blocks against the rfMgr connection.
>>>
>>> I have attached a document with two different Sybase Central screen
>>> shots. One is the connection screen showing the blocked connection
>>> information. You will see connection 311 is the rfMgr and the last
>>> request type was a commit. You will also see connection 297, which
>>> is the orderMgr, is blocked on 311.
>>>
>>> The second screen shot is the table locks screen showing the locks
>>> that exist. I'm not familiar with the newer display of locks types
>>> (I'm use to ASA 9) but you can see the rfMgr is holding a row lock on
>>> the same row the orderMgr is trying to update.
>>>
>>> I did verify in the code that the rfMgr does a commit and then sends
>>> a message to the orderMgr and does not do any other database access
>>> after the commit.
>>>
>>> The bad thing is the rfMgr is waiting for a response message from the
>>> orderMgr before it goes on and since the orderMgr is blocked on the
>>> rfMgr's connection, I basically have a deadlock.
>>>
>>>
>


Breck Carter [TeamSybase] Posted on 2010-02-20 13:37:34.0Z
From: "Breck Carter [TeamSybase]" <NOSPAM__breck.carter@gmail.com>
Newsgroups: ianywhere.public.general
Subject: Re: Bizarre lock problem with iAnywhere JDBC SA 11.0.1.2376
Organization: RisingRoad Professional Services
Reply-To: NOSPAM__breck.carter@gmail.com
Message-ID: <bapvn5tr2vm10k8si624n9s0tlvk6d0a7i@4ax.com>
References: <4b7ec5f0@forums-1-dub> <4b7f0130$1@forums-1-dub> <4b7f064e$1@forums-1-dub>
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: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 20 Feb 2010 05:37:34 -0800
X-Trace: forums-1-dub 1266673054 10.22.241.152 (20 Feb 2010 05:37:34 -0800)
X-Original-Trace: 20 Feb 2010 05:37:34 -0800, vip152.sybase.com
Lines: 20
X-Authenticated-User: TeamSybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:7909
Article PK: 6938

On 19 Feb 2010 13:44:46 -0800, "Glenn Paulley [Sybase iAnywhere]"

<paulley@ianywhere.com> wrote:

>Peter Bumbulis in Engineering is verifying for me the
>precise nature of the server's locking mechanisms when a WITH HOLD
>cursor is involved, and I hope to be able to post a follow-up message to
>that effect early next week.

Other people await Peter's missive... the suspense is growing!

Breck

--
Breck Carter - Blog: http://sqlanywhere.blogspot.com/

SQLA questions and answers: http://sqla.stackexchange.com

RisingRoad helps SQL Anywhere developers make better databases
http://www.risingroad.com/
Breck.Carter at gmail


Bill Williams Posted on 2010-02-22 13:24:18.0Z
From: "Bill Williams" <billwilliams@qcsoftware.com>
Newsgroups: ianywhere.public.general
References: <4b7ec5f0@forums-1-dub> <4b7f0130$1@forums-1-dub> <4b7f064e$1@forums-1-dub>
Subject: Re: Bizarre lock problem with iAnywhere JDBC SA 11.0.1.2376
Lines: 121
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5843
X-RFC2646: Format=Flowed; Response
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4b828582$1@forums-1-dub>
Date: 22 Feb 2010 05:24:18 -0800
X-Trace: forums-1-dub 1266845058 10.22.241.152 (22 Feb 2010 05:24:18 -0800)
X-Original-Trace: 22 Feb 2010 05:24:18 -0800, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:7912
Article PK: 6942

Glenn, you are correct, all connections are isolation level 1 (that's the
default settings our applications open JDBC connections with).

It would be great if there was some way other than closing the statement but
I guess we'll see after you find out what Peter has to say about it.

"Glenn Paulley [Sybase iAnywhere]" <paulley@ianywhere.com> wrote in message
news:4b7f064e$1@forums-1-dub...
> The server does not acquire read position locks at isolation level 0, so
> Kory's suggestion below will not solve the problem. From the evidence you
> posted, I believe you are already running at isolation level 1 on both
> connections.
>
> A plausible explanation is as follows.
>
> By default, with the SQL Anywhere JDBC drivers, JDBC cursors are opened
> WITH HOLD. If your application does not close all open cursors before
> executing the COMMIT, then these cursors will remain open and, I believe,
> both position and long-term read locks will not be released upon the
> COMMIT. Peter Bumbulis in Engineering is verifying for me the precise
> nature of the server's locking mechanisms when a WITH HOLD cursor is
> involved, and I hope to be able to post a follow-up message to that effect
> early next week.
>
> With jConnect, cursors are fire-hose cursors; after OPEN, all of the rows
> in the cursor are sent to the client (even if the client only wants the
> first row with a single FETCH). Consequently, when you open a cursor in
> your application using jConnect, the result set is scrolled all the way to
> the end, and the cursor's position will be at the end of the result set.
> Hence no cursor will retain an isolation level 1 read lock on any row
> (since each server-side cursor is positioned at the end of its result set)
> and your application won't run into the deadlock scenario.
>
> My suggestion, assuming my guess is correct: close the offending cursor(s)
> before you issue the COMMIT, and see what happens.
>
> Glenn
>
> Kory Hodgson (Sybase iAnywhere) wrote:
>> Bill,
>>
>> I don't know for sure that this is the cause, but the iAnywhere JDBC
>> driver has a default isolation level of 0 where the JConnect driver has a
>> default isolation level of 1
>> (http://dcx.sybase.com/index.html#1101en/dbadmin_en11/isolation-level-option.html).
>> It appears that the orderManager has shared locks on the table that rfMgr
>> is trying to commit to, and since orderMgr is at isolation level 1 I
>> beleive this will block the commit hence your deadlock scenario.
>>
>> You can likely resolve this by setting the JDBC connection's isolation
>> level to 1
>> (http://dcx.sybase.com/index.html#1101en/dbusage_en11/udtchan.html) to
>> simulate the way the two JConnect connections were behaving.
>>
>> Kory Hodgson
>> Sybase iAnywhere
>>
>>
>> Bill Williams wrote:
>>> I have two different applications that are trying to work with the same
>>> records and having a bizarre locking issue. First let me start by
>>> saying if I use the jConnect JDBC driver for both applications, I have
>>> no problem.
>>>
>>> The database is running on windows server 2003 running SA 11.0.1.2376.
>>>
>>> The two programs are using connection names orderMgr and rfMgr.
>>>
>>> The rfMgr program is using iAnywhere from SA 11.0.1.2376 while the
>>> orderMgr is using jConnect.
>>>
>>> The flow is this:
>>>
>>> The rfMgr modifies some records, performs a commit and then sends a
>>> message to the orderMgr requesting it make some more changes to these
>>> records.
>>>
>>> The orderMgr attempts to do an update on the WorkOrders table and it
>>> blocks against the rfMgr connection.
>>>
>>> I have attached a document with two different Sybase Central screen
>>> shots. One is the connection screen showing the blocked connection
>>> information. You will see connection 311 is the rfMgr and the last
>>> request type was a commit. You will also see connection 297, which is
>>> the orderMgr, is blocked on 311.
>>>
>>> The second screen shot is the table locks screen showing the locks that
>>> exist. I'm not familiar with the newer display of locks types (I'm use
>>> to ASA 9) but you can see the rfMgr is holding a row lock on the same
>>> row the orderMgr is trying to update.
>>>
>>> I did verify in the code that the rfMgr does a commit and then sends a
>>> message to the orderMgr and does not do any other database access after
>>> the commit.
>>>
>>> The bad thing is the rfMgr is waiting for a response message from the
>>> orderMgr before it goes on and since the orderMgr is blocked on the
>>> rfMgr's connection, I basically have a deadlock.
>>>
>>>
>
> --
> 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


Bill Williams Posted on 2010-02-24 12:36:53.0Z
From: "Bill Williams" <billwilliams@qcsoftware.com>
Newsgroups: ianywhere.public.general
References: <4b7ec5f0@forums-1-dub> <4b7f0130$1@forums-1-dub> <4b7f064e$1@forums-1-dub>
Subject: Re: Bizarre lock problem with iAnywhere JDBC SA 11.0.1.2376
Lines: 117
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5843
X-RFC2646: Format=Flowed; Response
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4b851d65$1@forums-1-dub>
Date: 24 Feb 2010 04:36:53 -0800
X-Trace: forums-1-dub 1267015013 10.22.241.152 (24 Feb 2010 04:36:53 -0800)
X-Original-Trace: 24 Feb 2010 04:36:53 -0800, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:7938
Article PK: 6964

Any news from Peter?

"Glenn Paulley [Sybase iAnywhere]" <paulley@ianywhere.com> wrote in message
news:4b7f064e$1@forums-1-dub...
> The server does not acquire read position locks at isolation level 0, so
> Kory's suggestion below will not solve the problem. From the evidence you
> posted, I believe you are already running at isolation level 1 on both
> connections.
>
> A plausible explanation is as follows.
>
> By default, with the SQL Anywhere JDBC drivers, JDBC cursors are opened
> WITH HOLD. If your application does not close all open cursors before
> executing the COMMIT, then these cursors will remain open and, I believe,
> both position and long-term read locks will not be released upon the
> COMMIT. Peter Bumbulis in Engineering is verifying for me the precise
> nature of the server's locking mechanisms when a WITH HOLD cursor is
> involved, and I hope to be able to post a follow-up message to that effect
> early next week.
>
> With jConnect, cursors are fire-hose cursors; after OPEN, all of the rows
> in the cursor are sent to the client (even if the client only wants the
> first row with a single FETCH). Consequently, when you open a cursor in
> your application using jConnect, the result set is scrolled all the way to
> the end, and the cursor's position will be at the end of the result set.
> Hence no cursor will retain an isolation level 1 read lock on any row
> (since each server-side cursor is positioned at the end of its result set)
> and your application won't run into the deadlock scenario.
>
> My suggestion, assuming my guess is correct: close the offending cursor(s)
> before you issue the COMMIT, and see what happens.
>
> Glenn
>
> Kory Hodgson (Sybase iAnywhere) wrote:
>> Bill,
>>
>> I don't know for sure that this is the cause, but the iAnywhere JDBC
>> driver has a default isolation level of 0 where the JConnect driver has a
>> default isolation level of 1
>> (http://dcx.sybase.com/index.html#1101en/dbadmin_en11/isolation-level-option.html).
>> It appears that the orderManager has shared locks on the table that rfMgr
>> is trying to commit to, and since orderMgr is at isolation level 1 I
>> beleive this will block the commit hence your deadlock scenario.
>>
>> You can likely resolve this by setting the JDBC connection's isolation
>> level to 1
>> (http://dcx.sybase.com/index.html#1101en/dbusage_en11/udtchan.html) to
>> simulate the way the two JConnect connections were behaving.
>>
>> Kory Hodgson
>> Sybase iAnywhere
>>
>>
>> Bill Williams wrote:
>>> I have two different applications that are trying to work with the same
>>> records and having a bizarre locking issue. First let me start by
>>> saying if I use the jConnect JDBC driver for both applications, I have
>>> no problem.
>>>
>>> The database is running on windows server 2003 running SA 11.0.1.2376.
>>>
>>> The two programs are using connection names orderMgr and rfMgr.
>>>
>>> The rfMgr program is using iAnywhere from SA 11.0.1.2376 while the
>>> orderMgr is using jConnect.
>>>
>>> The flow is this:
>>>
>>> The rfMgr modifies some records, performs a commit and then sends a
>>> message to the orderMgr requesting it make some more changes to these
>>> records.
>>>
>>> The orderMgr attempts to do an update on the WorkOrders table and it
>>> blocks against the rfMgr connection.
>>>
>>> I have attached a document with two different Sybase Central screen
>>> shots. One is the connection screen showing the blocked connection
>>> information. You will see connection 311 is the rfMgr and the last
>>> request type was a commit. You will also see connection 297, which is
>>> the orderMgr, is blocked on 311.
>>>
>>> The second screen shot is the table locks screen showing the locks that
>>> exist. I'm not familiar with the newer display of locks types (I'm use
>>> to ASA 9) but you can see the rfMgr is holding a row lock on the same
>>> row the orderMgr is trying to update.
>>>
>>> I did verify in the code that the rfMgr does a commit and then sends a
>>> message to the orderMgr and does not do any other database access after
>>> the commit.
>>>
>>> The bad thing is the rfMgr is waiting for a response message from the
>>> orderMgr before it goes on and since the orderMgr is blocked on the
>>> rfMgr's connection, I basically have a deadlock.
>>>
>>>
>
> --
> 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-24 21:37:09.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: Bizarre lock problem with iAnywhere JDBC SA 11.0.1.2376
References: <4b7ec5f0@forums-1-dub> <4b7f0130$1@forums-1-dub> <4b7f064e$1@forums-1-dub> <4b851d65$1@forums-1-dub>
In-Reply-To: <4b851d65$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: <4b859c05@forums-1-dub>
Date: 24 Feb 2010 13:37:09 -0800
X-Trace: forums-1-dub 1267047429 10.22.241.152 (24 Feb 2010 13:37:09 -0800)
X-Original-Trace: 24 Feb 2010 13:37:09 -0800, vip152.sybase.com
Lines: 165
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:7948
Article PK: 6976

I've confirmed the server's behaviour with Peter regarding locking and
WITH HOLD cursors.

WITH HOLD cursors are kept open across a COMMIT, and optionally left
open across a ROLLBACK (depending on the setting of the option
ANSI_CLOSE_CURSORS_ON_ROLLBACK for this connection).

If a WITH HOLD cursor acquires a position lock (because it is
functioning at isolation level 1), and the connection subsequently
performs a COMMIT, then those position locks will be retained. These
position locks must be retained because the cursor has not been closed
and the position lock maintains cursor stability on the row the cursor
is positioned on.

In addition, schema locks that were acquired on tables referenced by the
cursor are retained across COMMIT.

Other transaction-duration locks (particularly row locks - intent, read,
or write) - are released on COMMIT. We are investigating why table
INTENT locks appear to be retained across COMMIT (from your example).
Though these table intent locks are appearing in sa_locks() output (and
they should not), they are not causing a blocking issue for your
application - table intent locks only conflict with the LOCK TABLE
statement.

In your situation, it is the retained position locks that are causing
the conflict, and the only way to remove the lock is to close the cursor
beforehand.

Bill Williams wrote:
> Any news from Peter?
>
> "Glenn Paulley [Sybase iAnywhere]" <paulley@ianywhere.com> wrote in message
> news:4b7f064e$1@forums-1-dub...
>> The server does not acquire read position locks at isolation level 0, so
>> Kory's suggestion below will not solve the problem. From the evidence you
>> posted, I believe you are already running at isolation level 1 on both
>> connections.
>>
>> A plausible explanation is as follows.
>>
>> By default, with the SQL Anywhere JDBC drivers, JDBC cursors are opened
>> WITH HOLD. If your application does not close all open cursors before
>> executing the COMMIT, then these cursors will remain open and, I believe,
>> both position and long-term read locks will not be released upon the
>> COMMIT. Peter Bumbulis in Engineering is verifying for me the precise
>> nature of the server's locking mechanisms when a WITH HOLD cursor is
>> involved, and I hope to be able to post a follow-up message to that effect
>> early next week.
>>
>> With jConnect, cursors are fire-hose cursors; after OPEN, all of the rows
>> in the cursor are sent to the client (even if the client only wants the
>> first row with a single FETCH). Consequently, when you open a cursor in
>> your application using jConnect, the result set is scrolled all the way to
>> the end, and the cursor's position will be at the end of the result set.
>> Hence no cursor will retain an isolation level 1 read lock on any row
>> (since each server-side cursor is positioned at the end of its result set)
>> and your application won't run into the deadlock scenario.
>>
>> My suggestion, assuming my guess is correct: close the offending cursor(s)
>> before you issue the COMMIT, and see what happens.
>>
>> Glenn
>>
>> Kory Hodgson (Sybase iAnywhere) wrote:
>>> Bill,
>>>
>>> I don't know for sure that this is the cause, but the iAnywhere JDBC
>>> driver has a default isolation level of 0 where the JConnect driver has a
>>> default isolation level of 1
>>> (http://dcx.sybase.com/index.html#1101en/dbadmin_en11/isolation-level-option.html).
>>> It appears that the orderManager has shared locks on the table that rfMgr
>>> is trying to commit to, and since orderMgr is at isolation level 1 I
>>> beleive this will block the commit hence your deadlock scenario.
>>>
>>> You can likely resolve this by setting the JDBC connection's isolation
>>> level to 1
>>> (http://dcx.sybase.com/index.html#1101en/dbusage_en11/udtchan.html) to
>>> simulate the way the two JConnect connections were behaving.
>>>
>>> Kory Hodgson
>>> Sybase iAnywhere
>>>
>>>
>>> Bill Williams wrote:
>>>> I have two different applications that are trying to work with the same
>>>> records and having a bizarre locking issue. First let me start by
>>>> saying if I use the jConnect JDBC driver for both applications, I have
>>>> no problem.
>>>>
>>>> The database is running on windows server 2003 running SA 11.0.1.2376.
>>>>
>>>> The two programs are using connection names orderMgr and rfMgr.
>>>>
>>>> The rfMgr program is using iAnywhere from SA 11.0.1.2376 while the
>>>> orderMgr is using jConnect.
>>>>
>>>> The flow is this:
>>>>
>>>> The rfMgr modifies some records, performs a commit and then sends a
>>>> message to the orderMgr requesting it make some more changes to these
>>>> records.
>>>>
>>>> The orderMgr attempts to do an update on the WorkOrders table and it
>>>> blocks against the rfMgr connection.
>>>>
>>>> I have attached a document with two different Sybase Central screen
>>>> shots. One is the connection screen showing the blocked connection
>>>> information. You will see connection 311 is the rfMgr and the last
>>>> request type was a commit. You will also see connection 297, which is
>>>> the orderMgr, is blocked on 311.
>>>>
>>>> The second screen shot is the table locks screen showing the locks that
>>>> exist. I'm not familiar with the newer display of locks types (I'm use
>>>> to ASA 9) but you can see the rfMgr is holding a row lock on the same
>>>> row the orderMgr is trying to update.
>>>>
>>>> I did verify in the code that the rfMgr does a commit and then sends a
>>>> message to the orderMgr and does not do any other database access after
>>>> the commit.
>>>>
>>>> The bad thing is the rfMgr is waiting for a response message from the
>>>> orderMgr before it goes on and since the orderMgr is blocked on the
>>>> rfMgr's connection, I basically have a deadlock.
>>>>
>>>>
>> --
>> 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


Bill Williams Posted on 2010-02-25 13:36:29.0Z
From: Bill Williams <bill.williams3@verizon.net>
User-Agent: Mozilla/5.0 (Macintosh; U; Intel Mac OS X 10.5; en-US; rv:1.9.1.7) Gecko/20100111 Thunderbird/3.0.1
MIME-Version: 1.0
Newsgroups: ianywhere.public.general
Subject: Re: Bizarre lock problem with iAnywhere JDBC SA 11.0.1.2376
References: <4b7ec5f0@forums-1-dub> <4b7f0130$1@forums-1-dub> <4b7f064e$1@forums-1-dub> <4b851d65$1@forums-1-dub> <4b859c05@forums-1-dub>
In-Reply-To: <4b859c05@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: <4b867cdd@forums-1-dub>
Date: 25 Feb 2010 05:36:29 -0800
X-Trace: forums-1-dub 1267104989 10.22.241.152 (25 Feb 2010 05:36:29 -0800)
X-Original-Trace: 25 Feb 2010 05:36:29 -0800, vip152.sybase.com
Lines: 153
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:7954
Article PK: 6982

Thanks for the confirmation.

On 2/24/10 4:37 PM, Glenn Paulley [Sybase iAnywhere] wrote:
> I've confirmed the server's behaviour with Peter regarding locking and
> WITH HOLD cursors.
>
> WITH HOLD cursors are kept open across a COMMIT, and optionally left
> open across a ROLLBACK (depending on the setting of the option
> ANSI_CLOSE_CURSORS_ON_ROLLBACK for this connection).
>
> If a WITH HOLD cursor acquires a position lock (because it is
> functioning at isolation level 1), and the connection subsequently
> performs a COMMIT, then those position locks will be retained. These
> position locks must be retained because the cursor has not been closed
> and the position lock maintains cursor stability on the row the cursor
> is positioned on.
>
> In addition, schema locks that were acquired on tables referenced by the
> cursor are retained across COMMIT.
>
> Other transaction-duration locks (particularly row locks - intent, read,
> or write) - are released on COMMIT. We are investigating why table
> INTENT locks appear to be retained across COMMIT (from your example).
> Though these table intent locks are appearing in sa_locks() output (and
> they should not), they are not causing a blocking issue for your
> application - table intent locks only conflict with the LOCK TABLE
> statement.
>
> In your situation, it is the retained position locks that are causing
> the conflict, and the only way to remove the lock is to close the cursor
> beforehand.
>
> Bill Williams wrote:
>> Any news from Peter?
>>
>> "Glenn Paulley [Sybase iAnywhere]" <paulley@ianywhere.com> wrote in
>> message news:4b7f064e$1@forums-1-dub...
>>> The server does not acquire read position locks at isolation level 0,
>>> so Kory's suggestion below will not solve the problem. From the
>>> evidence you posted, I believe you are already running at isolation
>>> level 1 on both connections.
>>>
>>> A plausible explanation is as follows.
>>>
>>> By default, with the SQL Anywhere JDBC drivers, JDBC cursors are
>>> opened WITH HOLD. If your application does not close all open cursors
>>> before executing the COMMIT, then these cursors will remain open and,
>>> I believe, both position and long-term read locks will not be
>>> released upon the COMMIT. Peter Bumbulis in Engineering is verifying
>>> for me the precise nature of the server's locking mechanisms when a
>>> WITH HOLD cursor is involved, and I hope to be able to post a
>>> follow-up message to that effect early next week.
>>>
>>> With jConnect, cursors are fire-hose cursors; after OPEN, all of the
>>> rows in the cursor are sent to the client (even if the client only
>>> wants the first row with a single FETCH). Consequently, when you open
>>> a cursor in your application using jConnect, the result set is
>>> scrolled all the way to the end, and the cursor's position will be at
>>> the end of the result set. Hence no cursor will retain an isolation
>>> level 1 read lock on any row (since each server-side cursor is
>>> positioned at the end of its result set) and your application won't
>>> run into the deadlock scenario.
>>>
>>> My suggestion, assuming my guess is correct: close the offending
>>> cursor(s) before you issue the COMMIT, and see what happens.
>>>
>>> Glenn
>>>
>>> Kory Hodgson (Sybase iAnywhere) wrote:
>>>> Bill,
>>>>
>>>> I don't know for sure that this is the cause, but the iAnywhere JDBC
>>>> driver has a default isolation level of 0 where the JConnect driver
>>>> has a default isolation level of 1
>>>> (http://dcx.sybase.com/index.html#1101en/dbadmin_en11/isolation-level-option.html).
>>>> It appears that the orderManager has shared locks on the table that
>>>> rfMgr is trying to commit to, and since orderMgr is at isolation
>>>> level 1 I beleive this will block the commit hence your deadlock
>>>> scenario.
>>>>
>>>> You can likely resolve this by setting the JDBC connection's
>>>> isolation level to 1
>>>> (http://dcx.sybase.com/index.html#1101en/dbusage_en11/udtchan.html)
>>>> to simulate the way the two JConnect connections were behaving.
>>>>
>>>> Kory Hodgson
>>>> Sybase iAnywhere
>>>>
>>>>
>>>> Bill Williams wrote:
>>>>> I have two different applications that are trying to work with the
>>>>> same records and having a bizarre locking issue. First let me start
>>>>> by saying if I use the jConnect JDBC driver for both applications,
>>>>> I have no problem.
>>>>>
>>>>> The database is running on windows server 2003 running SA 11.0.1.2376.
>>>>>
>>>>> The two programs are using connection names orderMgr and rfMgr.
>>>>>
>>>>> The rfMgr program is using iAnywhere from SA 11.0.1.2376 while the
>>>>> orderMgr is using jConnect.
>>>>>
>>>>> The flow is this:
>>>>>
>>>>> The rfMgr modifies some records, performs a commit and then sends a
>>>>> message to the orderMgr requesting it make some more changes to
>>>>> these records.
>>>>>
>>>>> The orderMgr attempts to do an update on the WorkOrders table and
>>>>> it blocks against the rfMgr connection.
>>>>>
>>>>> I have attached a document with two different Sybase Central screen
>>>>> shots. One is the connection screen showing the blocked connection
>>>>> information. You will see connection 311 is the rfMgr and the last
>>>>> request type was a commit. You will also see connection 297, which
>>>>> is the orderMgr, is blocked on 311.
>>>>>
>>>>> The second screen shot is the table locks screen showing the locks
>>>>> that exist. I'm not familiar with the newer display of locks types
>>>>> (I'm use to ASA 9) but you can see the rfMgr is holding a row lock
>>>>> on the same row the orderMgr is trying to update.
>>>>>
>>>>> I did verify in the code that the rfMgr does a commit and then
>>>>> sends a message to the orderMgr and does not do any other database
>>>>> access after the commit.
>>>>>
>>>>> The bad thing is the rfMgr is waiting for a response message from
>>>>> the orderMgr before it goes on and since the orderMgr is blocked on
>>>>> the rfMgr's connection, I basically have a deadlock.
>>>>>
>>>>>
>>> --
>>> 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
>>
>>
>>
>