Presentation by: Steve Thomas, CA Technologies
Date: May 01, 2017, IDUG Db2 North American Tech Conference, Anaheim, California
Slides: A Beginners guide to Locks Latches Claims and Drains in DB2 for zOS
Actual Presentation: Youtube : A Beginners Guide to Locks, Latches, Claims and Drains in Db2 for zOS
Locking ensures data integrity
One of the key requirements for ACID transactions
Every lock in Db2 costs around same in terms of resources(non-data sharing envirnoment)
What you want to do in general is to hold smallest number of smallest locks you can for the shortest time. This will allow everybody else process as much as possible.
Db2 stores locks in IRLM(Internal Resource Lock Manager), its a separate address space(ssidIRLM). This IRLM concept is from IMS.
In data sharing environment, Db2 stores locks in Coupling Facility and each member has its own IRLM but there is a lock structure in the coupling facility that contains the locks for the data sharing system.
Db2 used to store locks in 64bit memory since V8.
LOCK TABLESQL or by Lock Escalation
3 Lock Modes are available at Page and Row level
If you look at it, two U locks are not compatible with each other. If two people try to update the same page, second one is going to wait.
Imagine a scenario, where user1 has an X lock on a row and user2 is trying to lock the table by issuing a LOCK TABLE statement or user2 application trying to escalate to higher level which is incompatible with X lock of user1. If Db2 didn't have other types of locks, they have look at every page on the object to determine whether there was a compatible on lock on it before it can take the lock escalation or the LOCK TABLE statement. So what Db2 does is, it takes intent locks. So you take X lock at the row level, Db2 takes IX lock one-up the hierarchy, now you issue a LOCK TABLE statement, Db2 can see somebody has X lock lower down because it can see intent lock higher-up. It has to check one lock, so its better for performance.
You have IS(Intent Share) and IX(Intent Exclusive) to handle these scenarios. There is a SIX locks(Share with Intent Exclusive). This is a rare one, how you get this is, you take an IX lock and then issue LOCK TABLE for SHARE. So you have page or row lock locked at Exclusive level and then issue a LOCK TABLE at SHARE mode.
If Db2 spots these, its going to abort one of these applications.
This tells the behaviour that Db2 has to do
RR -- Repeatable Read
RS -- Read Stability
CS -- Cursor Stability
UR -- Uncommitted Read
Overriding Isolation at Statement level
So when a program comes and accesses the object claim count is increased(includes WITH UR and LOCK AVOIDANCE using no locks) and when it commits the claim is released, except in the case of CURSOR WITH HOLD. Db2 doesn't have to look at the lock manager, it doesn't have to look at individual locks, it can just look at claim count to know how many people are using the object at one point. If the claim count is 0 meaning there is no interest in the object, so any utility or command can use it.
When a drain is issued:
Basically means, i want to read a page and i know i don't event want to take a lock to start with. Db2 uses number of items used by lock avoidance.
Possibly Uncommitted(PUNC) bit in the header of each row. It is set whenever a row is updated and reset on periodic basis using number of criteria(eg:- REORG). In each page header there is a PLOGRBA(page log RBA) which is last update happened on page. This could be RBA or LRSN it depends on whether you are in data sharing or single member.
Db2 also updated URID which is the start RBA/LRSN of the first update in a UoW. Db2 basically records the URID of the oldest uncommitted UoW in the subsystem. In non-data sharing its called CLSN(Commit Log Sequence Number) and in data sharing its the GCLSN(Global Commit Log Sequence Number), GCLSN is the oldest CLSN of all the members.
This is how Lock Avoidance works
TIMESTAMP NOT NULL GENERATED ALWAYS FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP
GENERATED BY DEFAULTas well
SELECT ... FOR READ ONLYto avoid U locks
WHERE RCT = :program-variable. If its same, nothings updated otherwise something is updated.
FOR READ ONLY