Question Looking For Sql Script To List Exclusive Table Locks

[Ryszard Musielak]

Hello,

I am not very familiar with the Progress dictionary, so I am looking for someone who tried to write an SQL script to check any exclusive table locks. Does anyone have such a script?

My idea is to run a UNIX/ .SQL sqript using "sqlexp". This is then to send an SMS alert about any long held locks.

Thank you in advance,
Richard

 

[RealHeavyDude]

If I were you I would rather monitor the transactions and care about long running transactions. The Lock table is not a virtual system table - it is a real table and constantly quering it can impose a significant performance hit on your application.

I only have the ABL code ( don't know whether you can query virtual system Tables via SQL - others might know ):

for each _Trans no-lock where _Trans._Trans-State = 'ACTIVE',
_Connect no-lock where _Connect._Connect-Id = _Trans._Trans-UsrNum + 1:

   /* Calculate difference between _Trans._Trans-Txtime time stamp and now
      to get the duration and do stuff when the duration exceeds a defined
      limit */

end.

 

[Ryszard Musielak]

constantly quering it can impose a significant performance

Thank you for that note. I would probably only impose this check every let's say 30 minutes. The problem is that it's a third party application and I have no influence on how the code is written. Also user sessions are not set to expire. I don't know if this can be set on a database level. Our application has a menu driven system to set users up, but I haven't seen a timeout option.

Regards,
Richard

 

[RealHeavyDude]

On our database we too have to cope with very old and poorly coded programs developed by third parties. Before I was in charge the production database was going down in flames roughly once a week due to long running transactions causing the before image to blow the file system. Since we can't afford the investment to fix thousands of old programs we needed to develop a defense strategy.

That last line of defense on our databases is our database monitor which takes care, amongst others, of such long running transactions. It checks them every 30 seconds ( with 800 users bad things can happen quite fast ). Depending on the before image panic level it will even take extreme measures such as disconnecting client sessions ( be it batch processes or interactive users ) leaving long running transactions open. Again, If I were you, I would NOT care about the locks ( yes, they might be ugly ), instead I would care about long running transactions. For one, you can't just unlock a record but you can find which processes do have long running transactions uncommitted and you can kick this processes out of the database.

If your problem are share locks you could try to add the -NL parameter to the parameters to the client sessions. Per default the default lock is share-lock if not specified otherwise. This will alter the behavior to default to no-lock and might help long ways making your situation better. If that does not solve your problem then you are running out of options unless you can afford to fix the offending code.

If your problem is pessimistic locking then monitoring the transactions is the way to go. Any client that attempts to get an exclusive-lock on any record in the database can only do so within an active transaction. Therefore a transaction which you can monitor is automatically spawned when a record is locked exclusively.

You can't set an idle timeout for processes connecting to the database on the database level. Also, there is no such client setting available. Therefore, everybody has to roll his/her own. Whether this is a good or a bad thing is subject to debate. I tend to see it as a bad thing.

Heavy Regards, RealHeavyDude.

 

[TomBascom]

Sorry RHD but _LOCK is indeed a VST and not a real table

VST's are a reflection of data structures in memory that look like, and can be queried as if they are real tables.

However -- _LOCK is infamous for poor query performance. Unlike the other VSTs the underlying data structure does not really map neatly onto a db table. (VSTs use an "id" field as their pseudo-index, but most useful queries against _LOCK want to use either a RECID or a usernum -- which means that they basically get treated as table scans) Thus it is really only reasonable to query _LOCK when the lock table size (-L) is very, very small. Like it might be in a a development environment.

Because it "works" in development some programmers have created clever little bits of code to detect old locks or to figure out who holds a lock when there are lock conflicts. In a development environment these tools can actually be useful.

Woe unto anyone who uses such a tool in production.

As -L grows the time required to get a response from a query against _LOCK grows exponentially. Regardless of how many locks are actually in use. Prior to OE11.4 querying _LOCK in a production environment could be catastrophic. Starting with OE11.4 Progress improved _LOCK by implementing it as a "snapshot" rather than a dynamic look at memory that changes out from under you as the query runs. This reduces the impact of querying _LOCK in production from "catastrophic" to merely "bad". It is still a *lot* of data to trawl through for very little benefit.

As RHD says -- what you really need to be paying attention to are old transactions. Get that under control and your long lock problems will go away.

 

[Rob Fitzpatrick]

I second Tom's and RHD's statements about monitoring long-running transactions rather than querying _Lock. Regarding SQL, VSTs can indeed be queried via the SQL engine.

It is possible to have some contention issues "fly under the radar" when you are monitoring for long-running transactions, as there could be clients holding share locks outside of transactions. Such issues can have application impacts but I wouldn't classify them as dangerous. Long-running transactions are dangerous as they can contribute to rapid before-image file growth, even if the offending transaction itself doesn't make many database changes. If unchecked, this BI growth can stall the database or cause an abnormal shutdown.

 

[Ryszard Musielak]

Thanks for everyone, you've given be enough information. So no database setting/client setting. And not recommended querying _lock. Basically no ready tool to monitor locks except the tool in "promon", which is manual. I may explore what this parameter is:

add the -NL parameter to the parameters to the client sessions.

For now the only case we had is a program that has timed out. Probably the lock was cleared by the system or simply by the user disconnecting, so the program just had to be restarted. Very unfortunate setup when people work night shifts and call you at 3AM to resolve such an idiotic situation. I guess for now my way to go is to ask the third party to implement some sort of a restart process.

One last final question:

That last line of defense on our databases is our database monitor which takes care, amongst others, of such long running transactions. It checks them every 30 seconds ( with 800 users bad things can happen quite fast ).

RealHeavyDude, can you shed some light on what table/VST you query, is this a batch that you have?

Thanks again.
Kind Regards,
Richard

 

[RealHeavyDude]

I've already posted a code snippet in the thread. The _Trans VST lists the transactions. You can query for active transactions. The _Connect VST gives you the user number you need to disonnect the user either with proutil or promon.

Just one thing you need to consider: Although the _Trans VST contains a duration field you should rather use the _Trans-Txtime time stamp. The reason being that in some releases of OE ( we use 11.3 ) the value in the duration field might be insanely high due to an integer overflow. Therefore you should use the time stamp when the transaction started to determine the duration of the transaction.

The before image panic level I mentioned above is a different story. Usually we disconnect interactive users after 2 hours. But, if the before image is filling up ( say it is 80% utilized ) we disconnect any interactive user having an open transaction for more than 1 minute immediately. Unfortunately the utilization of the before image is not available in the VST - you can only get the high water mark - but the highwater mark will only be reset when you restart the database and not when you have solved the long running transaction. Therefore you need to actually calculate the number of before image clusters in use. I've already posted some code samples on how to do it in this forum which you can find here How To Monitor Bi Growth, Utilized And Free Blocks...

Heavy Regards, RealHeavyDude.

 

[TomBascom]

ProTop knows how much BI space is being used It also knows about long transactions and "blocked" users.

 

[Cringer]

It can also indicate that your customer has gone out of business, but that's a tangent we'd better not go down