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.

Monitor Locks

5 posts in Windows NT Last posting was on 2000-10-17 13:02:50.0Z
Charanjiv Posted on 2000-05-03 01:11:47.0Z
Message-ID: <390F7CD3.E7DB4168@velos.ssind.com>
Date: Tue, 02 May 2000 18:11:47 -0700
From: Charanjiv <ckalha@velos.ssind.com>
X-Mailer: Mozilla 4.61 [en] (WinNT; I)
X-Accept-Language: en
MIME-Version: 1.0
Subject: Monitor Locks
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.sqlserver.nt
Lines: 9
NNTP-Posting-Host: 206.4.64.222
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:2212
Article PK: 1089646

Hi
Everyone, our application is facing some locking problems and it is not
possible to debug the code. Can you giveme some SQL through which we can
monitor the lock. There is a SYSLOCKS table but we are not able to get
anytng out of it.
We also are not in a position to run the Monitor server.

TIA
CSK


sean ryan Posted on 2000-10-17 13:02:50.0Z
From: sean ryan <william_s_ryan@navsup.navy.mil>
References: <390F7CD3.E7DB4168@velos.ssind.com>
Subject: Re: Monitor Locks
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit
Message-ID: <72H#NqDOAHA.202@forums.sybase.com>
Newsgroups: sybase.public.sqlserver.nt
Date: Tue, 17 Oct 2000 09:02:50 -0400
Lines: 71
NNTP-Posting-Host: bouka.yoko.fisc.navy.mil 206.39.68.134
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:1565
Article PK: 1089042

To get something a bit more useful (& user friendly) than you will from
sp_lock / sp_who you can try something like:

select usr=b.name,tbl=a.name,d.type from
<DB NAME>..sysobjects a,
master..syslogins b,
master..sysprocesses c,
master..syslocks d
where d.spid=c.spid and
c.suid=b.suid and
d.id=a.id
order by b.name,a.name

For convenience you can make this the body of a stored proc. When
evaluating this type of problem in the past I have altered this to
insert the returned data into a table along with a time stamp and give a
trusted user permissions to execute the stored proc when he/she felt
performance had dropped off. This approach allows you to gather dynamic
data as the problem is occurring for later analysis.
Remember to replace "<DB NAME>" on line #2 with the name of the database
where the locking is occurring and then run this query in that DB. Use
this key to evaluate the "type" column:


SYSLOCKS.TYPE
(control bit translation)

Decimal Hex Status

1 0x1 Exclusive table lock

2 0x2 Shared table lock

3 0x3 Exclusive intent lock (will do
page locking on indicated pages)

4 0x4 Shared intent lock

5 0x5 Exclusive page lock

6 0x6 Shared page lock

7 0x7 Update page lock (changes to
exclusive if page is modified)

256 0x100 Lock is blocking another
process

512 0x200 Demand lock


FYI, you may see combinations of these values. For example, you might
encounter 261. That is a combination of 256, "lock is blocking another
process", and 5, "Exclusive page lock", (256 + 5 = 261).
One of the others who replied here makes a good point about going to
DOL (row locking vice page locking) if you are on 11.9.2. You may also
want to look at your lock promotion configuration. If you are
escalating a large number of row locks to page locks or page locks to
table locks that could be causing you so pain.
If you are on a version prior to 11.9.2 you don’t have the DOL option.
You can however try setting ‘max rows per page’ to a very low number
like 1 or 2. This approach may yield less than desirable results but I
have seen cases where it has helped. Keep in mind that this approach
will certainly increased the size of your db.

Hope this helps,
Sean


=====================================================================
Message by Charanjiv <ckalha@velos.ssind.com> on: 5/2/00 6:11:48 PM

>Hi
>Everyone, our application is facing some locking problems and it is not
>possible to debug the code. Can you giveme some SQL through which we can
>monitor the lock. There is a SYSLOCKS table but we are not able to get
>anytng out of it.
>We also are not in a position to run the Monitor server.
>
>TIA
>CSK
>
>
>


SrSybDBA Posted on 2000-06-24 01:44:20.0Z
From: "SrSybDBA" <reywang@go.com>
References: <390F7CD3.E7DB4168@velos.ssind.com>
Subject: Re: Monitor Locks
Date: Fri, 23 Jun 2000 21:44:20 -0400
Lines: 26
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.00.2919.6700
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2919.6700
Message-ID: <JzoXS8X3$GA.203@forums.sybase.com>
Newsgroups: sybase.public.sqlserver.nt
NNTP-Posting-Host: 62.mercerville-11-12rs.nj.dial-access.att.net 12.78.144.62
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:1973
Article PK: 1089410

Couple easy solutions on this problem:
1. if you are at 1192, try row level locking
2. named cache will help

I kind of wonder, if you deadlock in tempdb, I might try have a tempdb named
cache
and tempdb log named cache.

Make sure you have 4K and 16K memory pool, let help.

"Charanjiv" <ckalha@velos.ssind.com> wrote in message
news:390F7CD3.E7DB4168@velos.ssind.com...
> Hi
> Everyone, our application is facing some locking problems and it is not
> possible to debug the code. Can you giveme some SQL through which we can
> monitor the lock. There is a SYSLOCKS table but we are not able to get
> anytng out of it.
> We also are not in a position to run the Monitor server.
>
> TIA
> CSK
>
>


Peter Veilleux Posted on 2000-05-03 12:52:12.0Z
Message-ID: <391020FC.BF581677@nvestservices.com>
Date: Wed, 03 May 2000 08:52:12 -0400
From: Peter Veilleux <pveilleux@nvestservices.com>
X-Mailer: Mozilla 4.72 [en] (WinNT; U)
X-Accept-Language: en
MIME-Version: 1.0
Subject: Re: Monitor Locks
References: <390F7CD3.E7DB4168@velos.ssind.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.sqlserver.nt
Lines: 13
NNTP-Posting-Host: nefclient149.mutualfunds.com 206.136.227.149
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:2205
Article PK: 1089638

how about sp_lock? sp_who also gives some locking information. Grab the
Performance and Tuning guide. Tons of info about locking.....

HTH,
Peter

Charanjiv wrote:

> Hi
> Everyone, our application is facing some locking problems and it is not
> possible to debug the code. Can you giveme some SQL through which we can
> monitor the lock. There is a SYSLOCKS table but we are not able to get
> anytng out of it.
> We also are not in a position to run the Monitor server.
>
> TIA
> CSK


David Owen Posted on 2000-05-15 18:23:40.0Z
Sender: dowen@kashka.midsomer.org
Subject: Re: Monitor Locks
References: <390F7CD3.E7DB4168@velos.ssind.com> <391020FC.BF581677@nvestservices.com>
From: David Owen <dowen@midsomer.org>
Organization: Midsomer Consultants Inc.
Date: 15 May 2000 12:23:40 -0600
Message-ID: <m2aehrwu37.fsf@kashka.midsomer.org>
Lines: 17
User-Agent: Gnus/5.0802 (Gnus v5.8.2) Emacs/20.5
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Newsgroups: sybase.public.sqlserver.nt
NNTP-Posting-Host: h29-5d-f4-26324826.cg.shawcable.net 24.70.23.180
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:2168
Article PK: 1089600


>> Hi Everyone, our application is facing some locking problems and it

>> is not possible to debug the code. Can you giveme some SQL through
>> which we can monitor the lock. There is a SYSLOCKS table but we are
>> not able to get anytng out of it. We also are not in a position to
>> run the Monitor server.

Another (Sybperl on NT) option is to run my sybmon.pl script. It uses
Sybperl/Tk to refresh a window with the set of locks displayed.

http://www.midsomer.org

dowen

--
David Owen Midsomer Consultants Inc. dowen@midsomer.org
Sybase FAQ: http://www.isug.com/Sybase_FAQ