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.

Client application hanging (table locks?)

2 posts in General Discussion Last posting was on 2003-11-05 18:05:29.0Z
umberto dallorso Posted on 2003-11-03 15:47:03.0Z
Sender: 549e.3fa6793d.1804289383@sybase.com
From: umberto dallorso
Newsgroups: ianywhere.public.general
Subject: Client application hanging (table locks?)
X-Mailer: WebNews to Mail Gateway v1.1s
Message-ID: <3fa679a0.54a7.846930886@sybase.com>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="-=_forums-2-dub3fa679a0"
X-Original-NNTP-Posting-Host: 10.22.241.42
X-Original-Trace: 3 Nov 2003 07:52:00 -0800, 10.22.241.42
Lines: 5093
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 3 Nov 2003 07:38:57 -0800, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 3 Nov 2003 07:47:03 -0800
X-Trace: forums-1-dub 1067874423 10.22.108.75 (3 Nov 2003 07:47:03 -0800)
X-Original-Trace: 3 Nov 2003 07:47:03 -0800, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:1998
Article PK: 4229

I have recently upgraded the version of an application, and
migrated the database from Sybase 5.5.05 to ASA 8.0.2.4249.

ASA installation was performed installing, in sequence:

1) ASA 8.0.0
2) ASA MR 8.0.2
3) ASA EBF 4249

After database upgrade (modified schema for new application
functionalities) I've unloaded the db and reload it into a
new one created with ASA 8.0.2.4249.

The application is a multiuser client / server connecting
via ODBC.

What we are experiencing now is that "randomly" (meaning it
does not happen every day) clients "hang" while doing simple
insert or update operations.
This happen after maybe 4/5 hours during which everything
seems to be OK.
In most cases, if the Client is left hanging, the
transaction finally gests committed (in 5-10 minutes time),
but sometimes the only solution is close the application
(CTRL+ALT+DEL).

The problem "pops up" when multiple users are
simoultaneously connected to the database (I have the same
behaviour on a DB with an average of 6 users and one with an
average of 16 users, but testing the system with just one
connected user does not create any problems).
ASA installation type is server (default 20 threads).

Reading through the newsgroup, I found that the most similar
cases reported involve table locking. attached please find
the result of the sa_locks(), sa_conn_properties() and
sa_conn_info stored procedures which were executed in
correspondance of 2 occourrencies of such behaviour.
In effect, they return some information about locks, but I'm
not sure I'm reading them correctly.

Could someone take a look at them (attached) to verify that
the problem is indeed this one (am I looking in the right
direction?).
Are there any other checks related to this that I might
perform?

Thanks in advance for any precious suggestion,
brgds,
Umberto.


Nick Elson Posted on 2003-11-05 18:05:29.0Z
From: "Nick Elson" <no_spam_nicelson@sybase.com>
Newsgroups: ianywhere.public.general
References: <3fa679a0.54a7.846930886@sybase.com>
Subject: Re: Client application hanging (no table locks here!)
Lines: 126
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
X-Original-NNTP-Posting-Host: nicelson-xp.sybase.com
Message-ID: <3fa93d1a$1@forums-2-dub>
X-Original-Trace: 5 Nov 2003 10:10:34 -0800, nicelson-xp.sybase.com
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 5 Nov 2003 09:57:11 -0800, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 5 Nov 2003 10:05:29 -0800
X-Trace: forums-1-dub 1068055529 10.22.108.75 (5 Nov 2003 10:05:29 -0800)
X-Original-Trace: 5 Nov 2003 10:05:29 -0800, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2029
Article PK: 4246

I suspect this is entirely normal behavior.

The SPA0000 lock that C has is blocking B and B is blocking
A. This is most likely due to a cursor that is positioned on that
single record and should move onto the next 'row' (or so).

These are the locks of interest here:

1534184572
user
user.OrderFormLog
S

Shared schema lock

1534184572
user
user.OrderFormLog
E
21475513692
Exclusive - Write Lock

1534184572
user
user.OrderFormLog
EPA0000
25770480988
Exclusive - Phantom - AntiPhantom Lock

1534184572
user
user.OrderFormLog
SPA0000
34360135967
Shared - Phantom - AntiPhantom Lock



The S lock is the schema lock; only necessary to prevent alter/drop table
type operations.
The E lock with lock_name: 21475513692 is the only modified row.
The EPA0000 is an index lock on the primary key index (due to the row
changed above).
(from these last two locks I am guessing the operation here is an update
that changed a
primary key column value and the EPA lock is locking the range for the old
value but the
operation may just be a delete).

That leaves the SPA0000 lock. This is an open cursor scan lock that is
marking the current
row's key value in the index. Due to isolation levels (isolation level 1 I
suspect) and possibly
due to the fact that there has been an update on that table as well, the
shared lock is needed
to prevent modifications from other connections.

Unless there is a reason to believe otherwise, this all seems normal. If
you don't believe
this is not what should be happening here, then you'll need to gather more
specifics and
put together a test case that reproduces the problem at will. Then open up
a support
case to get some hands-on involvement.


Good luck,

Nick

<umberto dallorso> wrote in message
news:3fa679a0.54a7.846930886@sybase.com...
> I have recently upgraded the version of an application, and
> migrated the database from Sybase 5.5.05 to ASA 8.0.2.4249.
>
> ASA installation was performed installing, in sequence:
>
> 1) ASA 8.0.0
> 2) ASA MR 8.0.2
> 3) ASA EBF 4249
>
> After database upgrade (modified schema for new application
> functionalities) I've unloaded the db and reload it into a
> new one created with ASA 8.0.2.4249.
>
> The application is a multiuser client / server connecting
> via ODBC.
>
> What we are experiencing now is that "randomly" (meaning it
> does not happen every day) clients "hang" while doing simple
> insert or update operations.
> This happen after maybe 4/5 hours during which everything
> seems to be OK.
> In most cases, if the Client is left hanging, the
> transaction finally gests committed (in 5-10 minutes time),
> but sometimes the only solution is close the application
> (CTRL+ALT+DEL).
>
> The problem "pops up" when multiple users are
> simoultaneously connected to the database (I have the same
> behaviour on a DB with an average of 6 users and one with an
> average of 16 users, but testing the system with just one
> connected user does not create any problems).
> ASA installation type is server (default 20 threads).
>
> Reading through the newsgroup, I found that the most similar
> cases reported involve table locking. attached please find
> the result of the sa_locks(), sa_conn_properties() and
> sa_conn_info stored procedures which were executed in
> correspondance of 2 occourrencies of such behaviour.
> In effect, they return some information about locks, but I'm
> not sure I'm reading them correctly.
>
> Could someone take a look at them (attached) to verify that
> the problem is indeed this one (am I looking in the right
> direction?).
> Are there any other checks related to this that I might
> perform?
>
> Thanks in advance for any precious suggestion,
> brgds,
> Umberto.
>