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.

ASE not releasing shared row locks during transaction

3 posts in General Discussion Last posting was on 2012-01-17 16:53:38.0Z
simon.niederberger Posted on 2012-01-17 08:17:52.0Z
Sender: 1e8.4f152c9f.1804289383@sybase.com
From: simon.niederberger@want.ch
Newsgroups: sybase.public.ase.general
Subject: ASE not releasing shared row locks during transaction
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4f152eb0.270.1681692777@sybase.com>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 17 Jan 2012 00:17:52 -0800
X-Trace: forums-1-dub 1326788272 172.20.134.41 (17 Jan 2012 00:17:52 -0800)
X-Original-Trace: 17 Jan 2012 00:17:52 -0800, 172.20.134.41
Lines: 22
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30832
Article PK: 73723

Hi all

We've got a search transaction which runs a SELECT on a
table with approx. 400k rows. The table has datarow locking,
and what we see is:

1. At first, more and more Shared Row Locks are created.
2. Eventually, lock promotion kicks in and a Shared Table
Lock is created
3. Our application grinds to a halt as the Shared Table Lock
blocks many other transactions, which block other
transactions, which ...

We're using the default ASE isolation level (1), so I'd
expect the Shared Row Locks to be released as soon as the
data has been fetched. What could be the reason the locks
are held and lead to this contention?

ASE 15.5/EBF 18159

Thanks
Simon


Rob V Posted on 2012-01-17 09:25:38.0Z
From: Rob V <rob@sypron.nl>
Reply-To: rob@sypron.nl
User-Agent: Mozilla/5.0 (Windows NT 5.1; rv:9.0) Gecko/20111222 Thunderbird/9.0.1
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: ASE not releasing shared row locks during transaction
References: <4f152eb0.270.1681692777@sybase.com>
In-Reply-To: <4f152eb0.270.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: <4f153e92$1@forums-1-dub>
Date: 17 Jan 2012 01:25:38 -0800
X-Trace: forums-1-dub 1326792338 10.22.241.152 (17 Jan 2012 01:25:38 -0800)
X-Original-Trace: 17 Jan 2012 01:25:38 -0800, vip152.sybase.com
Lines: 65
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30833
Article PK: 73722


On 17-Jan-2012 09:17, simon.niederberger@want.ch wrote:
> Hi all
>
> We've got a search transaction which runs a SELECT on a
> table with approx. 400k rows. The table has datarow locking,
> and what we see is:
>
> 1. At first, more and more Shared Row Locks are created.
> 2. Eventually, lock promotion kicks in and a Shared Table
> Lock is created
> 3. Our application grinds to a halt as the Shared Table Lock
> blocks many other transactions, which block other
> transactions, which ...
>
> We're using the default ASE isolation level (1), so I'd
> expect the Shared Row Locks to be released as soon as the
> data has been fetched. What could be the reason the locks
> are held and lead to this contention?
>
> ASE 15.5/EBF 18159
>
> Thanks
> Simon

If the shared row locks are not released immediately, then it means
you're using isolation level 3 (or 2). Since you mention you're sure
you're using isolation 1, here are some possibilities:

- isolation level 3 is set in a login trigger
- the session uses isolation level 1 but the query uses the 'holdlock'
keyword (which means isolation level 3 for that table alone), even
though the session uses isolation level 1


Also, while the query is running, you can check the actual isolation
level of the session by running:

dbcc traceon(3604)
go
dbcc pss (0,nnn) -- nnn = spid of the session
go

... and look for "pisolation", which indicates the isolation level
setting for the session at that moment. Note that this will not help you
find the 'holdlock' case. To find that, you should capture the SQL text,
for example with MDA table monSysSQLText.

--
HTH,

Rob V.
-----------------------------------------------------------------
Rob Verschoor

Certified Professional DBA for Sybase ASE, IQ, Replication Server

Author of Sybase books (order online at www.sypron.nl/shop):
"Tips, Tricks & Recipes for Sybase ASE"
"The Complete Sybase IQ Quick Reference Guide" (new!)
"The Complete Sybase ASE Quick Reference Guide"
"The Complete Sybase Replication Server Quick Reference Guide"

rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter: @rob_verschoor
Sypron B.V., The Netherlands | Chamber of Commerce 27138666
-----------------------------------------------------------------


Simon Niederberger Posted on 2012-01-17 16:53:38.0Z
Sender: 1e2e.4f15a6ec.1804289383@sybase.com
From: Simon Niederberger
Newsgroups: sybase.public.ase.general
Subject: Re: ASE not releasing shared row locks during transaction
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4f15a792.1e48.1681692777@sybase.com>
References: <4f153e92$1@forums-1-dub>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 17 Jan 2012 08:53:38 -0800
X-Trace: forums-1-dub 1326819218 172.20.134.41 (17 Jan 2012 08:53:38 -0800)
X-Original-Trace: 17 Jan 2012 08:53:38 -0800, 172.20.134.41
Lines: 11
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30835
Article PK: 73725

It turns out that the ORDER BY clause caused all Shared Row
Locks to be held until the SORT Operation is executed.
Running the identical SQL but without the ORDER BY resulted
in a single Shared Row Lock held at all times.

We could attempt to run a first SELECT without ORDER BY into
a temp table, then SELECT from there. But, the queries are
generate by an ORM, so we'll try to narrow down the search
otherwise. At least we now understand what's going on.

Simon