In this post we are going to discuss about three main lock issues:
- > Lock Escalations
- > Lock Waits
- > DeadLocks
Lock Escalations:
How many of you know that locks will also use memory? Yes they uses a a certain amount of memory and CPU. Sometimes there may be many row level locks present on a single table. So it may lead to more usage of memory and CPU. This this kind of scenarios DB2 automatically acquire a table level lock by replacing all the row level locks to free up the lock memory. This is know as "Lock Escalation".
Lock Waits:
A lock wait occurs when one transaction tries to acquire a lock whose mode conflicts with a lock held by another transaction.
Two transactions can have lock conflict with one other, even if they are running under the same authorization name, and even if they are threads of same process.
Lock wait continues until the locks held by the transaction causing the conflict are released. In common these lock waits are released after commit, roll back, when forcing applications using FORCE APPLICATIONS or through LOCKTIMEOUT.
Lock timeout is one way of dealing with lock waits. we can change the values of lock timeout using LOCKTIMEOUT parameter from database configuration. The default value of LOCTIMEOUT is "-1" which means "wait forever". We can change it to "0" or higher. Changing the value of LOCKTIMEOUT can avoid lengthy lock waits, but:
> It does not solve the problem of why lock waits occure.
> The applications may not deal properly with the -911's and roll backs.
Deadlocks:
A deadlock is a special case of a lock wait in which two or more applications cannot proceed because each is waiting for a lock held by others.
These are resolved automatically by DB2's deadlock detectors, which is activated with the frequency specified in database configuration parameter DLCHKTIME.
Real time issue:
Once I'm working on a database which sizes 700GB total and it holds many applications per day. Suddenly I got an ping from my dev team that non of the applications are running. Then I noticed that there are 1500+ applications are in waiting state. Then I tried to get the snapshot detail but system through me out of memory error. I found that one application is taking too much CPU, i tried to get that which command is running by that application. Finally I forced the application to resolve that. Here what I have noticed is lock waits will occupy more memory.
0 comments:
Post a Comment