Database lock Alarm

[jleon]

I have a system that remains with a high database lock percentage. The alarm is active almost all time.
What causes this alarm?

[AdamWoodland · Accepted solution]

The quick answer is "it is usually logic or file flushing to disk", so check you don't have some crazy logic running too much (Queries -> Logic Execution Status) and that you have anti-virus definitions configured correctly as per https://community.exchange.se.com/t5/Geo-SCADA-Knowledge-Base/bg-p/geo-scada-knowledge-base

So, with wild speculation aside, if you're running a version released in the past couple of years you get some funky lock analysis tools. You can enable LCK/Lock logging in the DB log files since forever but this new function does a pivot table on that so is much better for identifying the effect and then you can drill down through the logs for the cause.

What I recommend is:

1. Go to Server Status

2. Go to General -> Logging

3. Enable LCK/Lock logging (note this will drastically increase the amount of logging on your system which may impact performance and will cause DB log files to roll over much quicker, so make sure you have sufficient DB logs configured)

4. Still in Server Status go to Database -> Lock Diagnostic Read and also Write (in recent versions its two windows, previous version it was one). Right click on each one and choose "Enable Diagnostics"

5. Let it run for a bit (probably not too long in your case given you said always active)

6. Check out the screen for anything which is...

 a. Longest [Read|Write] Lock is > 1000mS

 b. [Read|Write] Locks Per Second is > 1 (note that things like InterestThread are probably more than 1, depends on what clients you have an what they're doing)

7. There might be something obvious, but I'd recommend you open a support case with the logs and then they can take a closer look (you will need to provide the DB logs with LCK logging enabled and DBSnaphot files which capture some of these Lock Diagnostics in)

8. When you aren't looking at it, disable LCK logging. The Diagnostic tabs you can leave as necessary.

There is a setting to reset the Diagnostics tab each snapshot, its probably not that useful for this short time frame analysis but support may ask for it.

Just for some info. The difference between read and write locks is you can only have one write lock active on a system, but you can have multiple read locks active concurrently. You can't have read and write locks active at the same time. This makes write locks very dangerous if they get out of control and are usually the cause of the system locking up for periods of time.

There are some "read" actions that are done under read locks for very specific reasons. Flushing data to disk is a write lock and so when anti-virus starts to interfere with the file writes that can have a serious effect.

If you have a high lock alarm but you're not having the system lock up you're probably looking at lots of read locks happening.

[jleon]

Thanks Adam, your information very useful. I was checking and testing the last month.

We have a external application that querie the database, that was the reason of high percentage databaselock.