Browse Prior Art Database

A method and system to categorize lock events and locking metrics of a database automatically according to application specific data Disclosure Number: IPCOM000198805D
Publication Date: 2010-Aug-17
Document File: 4 page(s) / 34K

Publishing Venue

The Prior Art Database


In database systems, lock contention situations inevitably happen. Database Administrators have to spend a lot of time with complex tools to investigate such problems and identify the causing applications in order to finally solve the locking problem. This article introduces a new algorithm creating a tree representation which categorizes the locking problems by their connection context information. This tree representation can e.g. be visualized in a graphical user interface to help the database administrators in investigating their locking problems more fast and easily.

This text was extracted from a PDF file.
This is the abbreviated version, containing approximately 33% of the total text.

Page 1 of 4

A method and system to categorize lock events and locking metrics of a database automatically according to application specific data

In database systems, lock contention situations inevitably happen when more than 1 application concurrently access the same database object. Senior DBAs have to spend a lot of their time with complex tools to retrieve context information from the database system, then diagnose the relationship of concurrent applications related to the specific lock, in order to try to resolve the contention by stopping the problematic application or unit of work. On the other side,

junior DBAs may not have the required

skills to use such complex tools or understand the information returned by such tools. In both cases the DBAs need to categorize the locking situations to find the causing applications and to solve the locking problem together with the system administrators or the application developers. Categorizing the applications which take part in a locking problem can be a difficult manual task which may take a lot of time. The herein described algorithm helps the DBAs by categorizing the locking contentions automatically based on application properties.

Each database application provides application specific information while connecting to the database. This information is originally intended for accounting purposes but is also very useful for investigating any kind of problems, including performance problems in general and locking contentions in particular. The herein described algorithm categorizes the applications engaged in a locking problem by those attributes. This categorization can then be used e.g. to visualize the applications in a locking tree representing the logical structure of all applications working on the database system.

The automatic categorization of such locking events provides the following advantages in comparison to already existing manual solutions:

Less time needed to find the applications or the set of applications engaged in the






most locking events.

Hence less time required to solve the actual locking problems or to improve the

locking contention of the applications working against the database. Reduces costs to investigate and solve locking problems.

Less skills required to investigate locking contentions.

Combined display of locking events and business / application logic possible (

e.g. locking events per application type, or lock wait time per user, or locking events per application name ).

Definition of terms:

Workload Cluster Group

Contains a user-defined definition of attributes to be used to group the database connections together and a filter which is used to filter out certain applications before grouping them.

Workload Cluster

Result of grouping a number of database applications. Each set of connections with the same attribute values (concerning those attributes defined in the clustering rule of the connected workload cluster group) are grouped together in t...