Lock Diagnostics Read

The Lock Diagnostics Read category provides summary information about database read lock usage. You can use this information when analyzing system performance.

Unlike write locks, read lock usages can exceed 100%. This can occur due to multiple threads of execution each holding a read lock for the duration of the measurement period.

Priority is given to write locks, so a read lock might be relinquished after a short period if a database write is waiting. Any new read lock request will wait until all pending write locks have completed. This might result in a number of read locks occurring before a particular read activity is completed. This only occurs if temporary release of the database read lock is feasible - this is not suitable for some read activities (which need to retain their read lock to completion).

The database read locks are shown in rows, with each row representing a different lock. The status information shown for each lock is as follows:

• Lock Name—The Lock Name is the name of the lock in Geo SCADA Expert and indicates what or who has requested the lock. In some cases, reference is also made to:

  • A Server Links Id (such as L06#03), which corresponds to the Id column in the Server Links category of the Server Status Tool. This can be used to identify a client (or driver) that might be putting more load on the system than expected. For example, it might highlight that a user on a ViewX client is executing a large query against the database. Read lock over-usage is not necessarily indicative of a problem, as it is a shared resource. However, if server performance becomes an issue this might highlight the area that is causing the issue. The documentation about running SQL queries mentions the preparation and care that should be taken to help ensure that such a query does not affect system performance adversely.

• Lock Type—The Lock Type is either DB for the main database lock which protects all data, or Metadata which protects changes to the Metadata in Geo SCADA Expert (via the Edit Metadata option that is available from the context-sensitive menu on the root group (see Display the Metadata Window)).
• Start Time—The Start Time is the time that the lock was first acquired. This is either from when Geo SCADA Expert was started, after a snapshot interval if the ResetCountersOnSnapshot log file setting is true, or after a manual reset in the Server Status tool.
• Last Access—Last Access is the last time that the lock was acquired.
• Read Locks—Read Locks is the total number of read locks, either from startup or since the lock diagnostics were last reset.
• Read Locks Per Second—Read Locks Per Second is the number of read locks per second as an average of the same period.
• Average Read Lock—The Average Read Lock is the average time the lock spent in a read lock.
• Longest Read Lock—The Longest Read Lock is the duration of longest read lock, either since start-up or since the lock diagnostics were last reset.
• Longest Read Lock Time—The Longest Read Lock Time is the timestamp of the longest read lock time.
• Time in Read Lock—The Time in Read Lock is the total time in read lock, either since start-up or since the lock diagnostics were last reset. It is expressed as a percentage.
• Average Read Lock Wait—The Average Read Lock Wait is the average time to wait to acquire a write lock.
• Longest Read Lock Wait—The Longest Read Lock Wait is the longest time to wait to acquire a read lock.
• Longest Read Lock Wait Time—The Longest Read Lock Wait Time is the timestamp of longest time to wait to acquire a read lock.

You can access a context-sensitive menu by right-clicking on an entry in the list.

The options that are available are:

• Disable Diagnostics—Select this option to disable all Lock Diagnostics. The comment 'Per-lock diagnostics disabled' replaces the list of locks.
• Reset Lock Diagnostic—Select this option to reset the selected lock diagnostic.
• Reset All Lock Diagnostics—Select this option to reset all of the lock diagnostics.
• Enable Diagnostics—Select this option to enable Lock Diagnostics. The option is only available for use when the Lock Diagnostics are disabled. Locks per type are acquired.