how to find out the reason for blocking locks?

[Former Member]

PB Application try to create data in ASE 12.5. With this operation, there are sql, sp, trigger involved. Most of the it is okay. With lock of sleeping or sync sleeping lock. User wait for a short time to get the result.

but some time, there are blocking lock.

If spid for the app is 1. then looks like this app also generate another spid, say it is 2. and 2 is blocking by 1. spid 1 holding lock!

then all user is frozen because of spid 1 holding lock!!!  How to figure it out the reason for blocking locks?

[Former Member]

Thanks, Mark. Yes, sp_lock, sp_who, ... are common tool I used.

I found out one case like:

I have a SP which call a View for data.

When I run this SP, suppose the sipd is 1. then it will create another spid 2 because of access view and it gone very quick. This cause spid 1 block spid 2 within a short time.

If the are many user run the app, the app will be  frozen and everyone is waiting. Finally it release, no dead lock, but the performance is not acceptable.

That why I have this question.

[javier_barthe]

Hi Kent,

PB autocommit its in true or false? Perhaps app its holding blocks because of transaction still oppened.

If you have installed MDA tables, take a look at monlocks you can collect this table in order to get the exact table/page that spid 1 holds to block spid 2.

I hope this help.

Regards.

Javier.

[Former Member]

Make sense. Will try. Thanks.

[Former Member]

have tried. Find out one question:

some objectid invalid when trying use following to find who is:

select * from sysobjects where id=objectid

Does it mean it is a temp table in tempdb or something else in tempdb?

[Mark_A_Parsons]

Have you tried limiting your processing to only those monLocks records that relate to your current databas, eg, monLocks.DBID = db_id() ?

[javier_barthe]

Kent,

I don´t think so, because tempdb objects (#) are not share accross spids, unless you have a tempdb.. table creation.

Try collecting monlocks every 5 seconds if needed, but take the object_name in it like this:

select object_name(ObjectID,DBID) as Tabla, * from master..monlocks then take a look at BlockedState in ('Blocked','Blocking').

I hope this help.

Regards.-

Javier.