A method and system to analyze & resolve locks contention of database - Locking Tree
Publication Date: 2010-Aug-06
The IP.com Prior Art Database
In customer product database system, the locks contention inevitably happen when more than 1 client application concurrently access the same record or the same table. Furthermore, deadlock is worse condition of locks contention, which would prevent all routine transactions accessing the locked records if it is not solved at once. Senior DBA have to spend a lot time with complex tools to retrieve context information from database system, then diagnose relationship of concurrent applications related to the specific lock, then try to resolve contention thru stopping problematic application or unit of work. On the other side, junior DBAs are lack of domain knowledge to understand mass information from database system, even they didn't know how to use these complex tools to retrieve context information of locks. For both cases, DBAs would be hard to take immediate action to solve lock contention in order to keep offering better performance of database system.
A method and system to analyze
&& resolve locks contention of database - Locking Tree
resolve locks contention of database - Locking Tree
Locking Tree is organized based on locking context information retrieved from database, the solution is:
1. Prepare locking data with consolidated database management knowledge.
1.1 Develop tool to retrieve locking information from database platform with special tools based on consolidated expertise from senior DBAs. E.g. for DB2 LUW , call Snapshot command for Locks, Applications, call Event command for Lock Wait, Lock timeout and deadlock and call direct SQL for more context information. For Oracle or other database, to call direct SQL and accordingly commands.
1.2 Store lock detail into LOCKS table, store detail of involved applications into APPL table for further analysis.
2. Sort and join locking data to show list of applications of locking participant with summary
3. Organize holding/
waiting applications as tree from a specific participant application with following methods
(Ax means Application with No.x, Lx means lock with No.x)
A1 (Waiter for L5, Holder for L1)
A2 (Holder for L2, Waiter for L4)
A3 (Waiter for L1)
A4 (Hollder for L4)
A5 (Holder for L5, Waiter for L2 ) ( Selected Participant ) A6 (Waiter for L5)
3.1 Case 1: Expand locking applications for specified application to down level or upper level.
1. Execute a SQL to get status of selected application from join of APPL and LOCKS tables with predicate phrase "appl
Selected app A5, 2 records returns. A5 is Holder of lock (L5), and it is also Waiter of lock L2.
2. If selected application (A5) is waiter of another lock (L2), execute another SQL to search holder of lock (L2) from join of APPL and LOCKS tables with predicate phrase "lock
_name='L2'". Holder (A2) of lock (L2) is found. Otherwise, stop search.
3. If parent node (A2) is a waiter for another lock, show action button "Show Waiting" after current root node (A2), it allows user to locate upper level parent node when clicking it. Otherwise, show no button.
4. If selected app (A5) is holder of lock (L5) as well, search direct waiter nodes thru SQL with predicate phrase "Lock
Waiter'" and show them (A6, A1) as children of selected app (A5). Otherwise, stop search.
5. If child node are holder of another lock (L1), show a button "Show Holding", it allows user to expend tree to down-level when click...