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.

Hung processes

6 posts in Performance and Tuning Last posting was on 2011-10-12 01:03:28.0Z
Shane Posted on 2011-10-11 03:24:14.0Z
Sender: 672e.4e93acff.1804289383@sybase.com
From: Shane
Newsgroups: sybase.public.ase.performance+tuning
Subject: Hung processes
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4e93b6de.69f6.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 10 Oct 2011 20:24:14 -0700
X-Trace: forums-1-dub 1318303454 10.22.241.41 (10 Oct 2011 20:24:14 -0700)
X-Original-Trace: 10 Oct 2011 20:24:14 -0700, 10.22.241.41
Lines: 24
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:12984
Article PK: 91434

As of late one of our Sybase Instances has been experiencing
regular occurances of hanging processes. It has been
practically a daily occurance for the last week.

What's happening is a users session is holding numerous
exclusive page locks and isn't releasing them. Consequently
other users are being blocked. Ultimately the user either
stops the client session or an administrator kills the spid
releasing the locks. Nothing is being logged to the error
log.

Locks are being held against numerous table similtaneously,
a couple DOL table and the rest page level locking tables.

I've done DBCC checktable all the tables and they are OK
although some garbage collection can be done. My next step
is to do a REORG COMPACT the DOL tables but this is in hope
rather than knowing it will fix the problem.

Does anyone have a firm idea of what may be causing these
problems and how to correct them?

Thanks
Thanks


Bret Halford Posted on 2011-10-11 15:14:56.0Z
From: Bret Halford <bret@sybase.com>
User-Agent: Mozilla/5.0 (Windows NT 5.1; rv:7.0.1) Gecko/20110929 Thunderbird/7.0.1
MIME-Version: 1.0
Newsgroups: sybase.public.ase.performance+tuning
Subject: Re: Hung processes
References: <4e93b6de.69f6.1681692777@sybase.com>
In-Reply-To: <4e93b6de.69f6.1681692777@sybase.com>
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: <4e945d70$1@forums-1-dub>
Date: 11 Oct 2011 08:14:56 -0700
X-Trace: forums-1-dub 1318346096 10.22.241.152 (11 Oct 2011 08:14:56 -0700)
X-Original-Trace: 11 Oct 2011 08:14:56 -0700, vip152.sybase.com
Lines: 40
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:12985
Article PK: 91438


On 10/10/2011 9:24 PM, Shane wrote:
> As of late one of our Sybase Instances has been experiencing
> regular occurances of hanging processes. It has been
> practically a daily occurance for the last week.
>
> What's happening is a users session is holding numerous
> exclusive page locks and isn't releasing them. Consequently
> other users are being blocked. Ultimately the user either
> stops the client session or an administrator kills the spid
> releasing the locks. Nothing is being logged to the error
> log.
>
> Locks are being held against numerous table similtaneously,
> a couple DOL table and the rest page level locking tables.
>
> I've done DBCC checktable all the tables and they are OK
> although some garbage collection can be done. My next step
> is to do a REORG COMPACT the DOL tables but this is in hope
> rather than knowing it will fix the problem.
>
> Does anyone have a firm idea of what may be causing these
> problems and how to correct them?
>
> Thanks
> Thanks

By far the most likely reason is that the client is
issuing a BEGIN TRAN, then performing inserts/updates/deletes
and then failing to commit or rollback the transaction, just
leaving it open.

Sometimes this happens when the user switches client apps
to one that (perhaps unbeknownst to them) uses "chained mode" (aka
"autocommit off") by default when they were used to unchained
behavior. Chained mode implicitly issues a BEGIN tran that must
explicitly be closed, in unchained mode the user must explicitly
issue both BEGIN and COMMIT.

-bret


Bret Halford Posted on 2011-10-11 23:19:33.0Z
From: Bret Halford <bret@sybase.com>
User-Agent: Mozilla/5.0 (Windows NT 5.1; rv:7.0.1) Gecko/20110929 Thunderbird/7.0.1
MIME-Version: 1.0
Newsgroups: sybase.public.ase.performance+tuning
Subject: Re: Hung processes
References: <4e945d70$1@forums-1-dub> <4e94c662.3026.1681692777@sybase.com>
In-Reply-To: <4e94c662.3026.1681692777@sybase.com>
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: <4e94cf05$1@forums-1-dub>
Date: 11 Oct 2011 16:19:33 -0700
X-Trace: forums-1-dub 1318375173 10.22.241.152 (11 Oct 2011 16:19:33 -0700)
X-Original-Trace: 11 Oct 2011 16:19:33 -0700, vip152.sybase.com
Lines: 24
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:12988
Article PK: 91439


On 10/11/2011 4:42 PM, Shane wrote:
> Thanks. The client app and it's support sp's haven't
> changed in 10 months, however I'm looking into specifics of
> the app and data being entered. Any known instances where
> the network may cause this? Connections bewtween the server
> and client aren't being disconnected.

I'm not aware of any, but certainly this could be seen
if the packet containing the "commit tran" went missing.
I wouldn't expect this to be happening regularly and apparently
so specifically to the packet with the commit tran command
in it.

The usual TCP keepidle process wouldn't break the connection
because each side is still active (and waiting for the
other to do something).

Does this seem to be happening to just one particular login?
Perhaps auditing cmdtext for that login could shed some light.


-bret