OK, let's start with the "why" of Db2 lock avoidance. In support of a large application workload, Db2 does a lot of stuff. One thing that Db2 does about as much as anything else is issue lock and unlock requests. To see what I mean, take a look at a statistics long (aka statistics detail) report, generated by your Db2 monitor, covering a period of high activity on the system (or check the monitor's online display of lock activity). I looked at a report just now, reflecting activity in a large real-world production Db2 for z/OS subsystem, and what does it show? 106 million lock requests and 23 million unlock requests in one hour. That's about 30,000 lock requests per second, and about 6400 unlock requests per second, on average (there are more lock than unlock requests because multiple X-locks, acquired by an application process in the course of modifying data values, are released at a commit point with one unlock request). Now, the workload associated with all that lock activity is a big one, driving in excess of 700 million SQL data manipulation statements during the hour between the report's FROM and TO times, but that kind of volume is by no means unusual for a large Db2 site. Why the big lock request number? Two words: data integrity. Locks acquired on data pages or rows (depending on the granularity of locking in effect for a table space) help to ensure that data consistency is maintained in a Db2 for z/OS system, and that programs retrieve only committed data from the database (i.e., values that have been "hardened" in the database in the wake of data-change operations).
One lock request consumes very little in the way of CPU cycles, but tens of thousands of lock requests per second? That's another matter. When the execution volume for a given Db2 operation is really high, there is CPU-efficiency value in reducing the frequency of said operation. The Db2 development team had that in mind when, more than two decades ago, they delivered a capability called lock avoidance. Essentially, lock avoidance enables Db2 to reduce lock activity for an application workload by issuing S-lock requests (i.e., data-read locks associated with execution of queries) only when such locks are needed to ensure retrieval of committed data values.
How does Db2 know when an S-lock on.a page or row is needed to guarantee retrieval of committed data values (i.e., to avoid returning uncommitted data changes to a program)? It knows through the use of two lock-avoidance indicators: CLSNs and PUNC bits.
- CLSNs - This acronym stands for "commit log sequence number." In every page of every Db2 page set, Db2 records the log point corresponding to the most recent update of the page's contents. Additionally, for every page set or partition (for partitioned objects), Db2 keeps track of the starting log point of the oldest still-in-flight unit of work that is changing data in the page set or partition. That latter log point is the commit log sequence number of the page set or partition. When going to retrieve data in a page, Db2 can check the page's last-updated log point and compare that to the CLSN of the page set or partition. Using simple numbers for example, suppose that a page's last-updated log point (information that, again, is stored in the page) is 20, and the CLSN of the page set or partition in which the page is located is 30. Because the starting log point of the oldest still-in-flight unit of work affecting the page set or partition is higher than (meaning, is later in time than) the log point of the last update of the page, Db2 knows that all the data in the page is in a committed state, so guaranteed-committed data can be retrieved from the page with no need for S-lock acquisition on the page (or on rows therein). When the oldest still-in-flight data-changing unit of work affecting a page set or partition commits, the CLSN of the page set or partition moves up to the starting log point of what had previously been the next oldest still-in-flight data-changing unit of work affecting the page set or partition. Even if (using simple numbers as before) the last-updated log point of a page is seen to be 50, and the CLSN of the associated page set or partition is 40 (meaning that data in the page might have been updated by a still-in-flight data-changing unit of work), lock avoidance may still be possible for a data retrieval operation targeting the page, thanks to PUNC bits.
- PUNC bits - PUNC is short for "possibly uncommitted." Included in the control information on every Db2 data page and every index page are bits that indicate whether or not a row contains possibly uncommitted data (in the case of an index page, the PUNC bits are associated with the RIDs, or row IDs, that point to rows in the underlying Db2 table). When a row is changed, its PUNC bit is set. That being the case, when Db2 examines a row (or its RID in an index) and sees that the PUNC bit is NOT set, Db2 knows that the data in the row is committed, and the data can be retrieved without the need for an S-lock on the data page or row to ensure data committed-ness. So, why is this indicator called the "possibly" uncommitted bit, as opposed to the "for sure uncommitted" bit? Because Db2 does not synchronously reset a "turned on" PUNC bit when the data change that caused the bit to be set is committed - doing that would have an unacceptable overhead cost. Instead, PUNC bits that are turned on as a result of data-change activity are reset asynchronously, in the background, when certain events happen (one such event is execution of the REORG utility for a table space; another is when more than 25% of the rows in a page have their PUNC bits turned on and the page set's or partition's CLSN advances). Because of the asynchronous nature of PUNC bit resetting, relative to the turning on of a PUNC bit, the turned-on PUNC bit setting can only be interpreted as meaning, "Maybe the data in this row is committed and the PUNC bit hasn't been reset, or maybe the data is in fact not committed." When an application program wants only committed data values to be retrieved by queries (i.e., when isolation level UR, short for "uncommitted read," is not in effect for the program), "maybe" isn't good enough, and Db2 will request an S-lock on the row or page to ensure the committed state of data values (successful acquisition of an S-lock means that the page or row is not X-locked, and that means the row or page contains only committed data).
I want to impart now a couple more items of information pertaining to Db2 for z/OS lock avoidance. First, lock avoidance can be utilized to the maximum extent possible when a Db2 package is bound with ISOLATION(CS) - short for cursor stability - and CURRENTDATA(NO). Second, what I have described in this entry is lock avoidance as it occurs in a non-data sharing Db2 system. Lock avoidance in a Db2 data sharing environment - including the Db2 12 enhancement referenced at the start of this entry - will be the subject of my next post to this blog. I hope to have that written within the next two weeks, so check back in if you use - or are interested in - Db2 data sharing.
What do we mean by transaction locks ? This link tells me about lock size,duration ,lock mode but I do not see anything about transaction lock in specific.
ReplyDeletehttps://www.ibm.com/docs/hr/db2-for-zos/12?topic=locks-transaction
That's just a term that broadly applies to locks that are acquired in the processing of a Db2 for z/OS-accessing transaction.
DeleteRobert
When we issue "-DIS DB() TS() CLAIMERS " we see locks acquired for resource.
Deletewhat does it mean by
"Claimer's are different from transaction locks" .How is it different?
Locks control concurrency of access to data. Claims indicate intent of data access ("I intend to read data in this page set or partition," or "I intend to change data in this page set or partition"). Claims (and drains, an associated Db2 for z/OS concept) are explained on this page of the Db2 for z/OS documentation: https://www.ibm.com/docs/en/db2-for-zos/13?topic=concurrency-claims-drains.
DeleteRobert
Thanks for the info
DeleteFor locksize ANY defined in table structure-How /on what basis DB2 decide if it should take page or row. how do this gets controlled?
ReplyDeleteI'm not aware that that information is documented. My understanding is that LOCKSIZE(ANY) will cause Db2 to choose page-level locking for child rows, but Db2 "reserves the right" in that case to choose row-level locking. I do not know of a scenario that would cause Db2 to choose row-level locking when LOCKSIZE(ANY) is in effect.
DeleteRobert
When I issue DIS DB() TS() LOCKS command ,we only see below entry
DeleteNAME PART TYPE STATUS CONNID CORRID LCKINFO
TS1 01 TS RW TSO USER1 H-IX,P,C
From this info,how do we know which is parent lock and child lock .
How should we interpret this?
-DISPLAY DATABASE with the LOCKS option will show parent lock information. The meaning of the information in the output is explained on this page in the Db2 for z/OS documentation: https://www.ibm.com/docs/en/db2-for-zos/13?topic=messages-dsnt361i#dsnt361i__lock. On that page, if you do a search on the string 'status-lock', that will take you right to the part of the page that explains the meaning of data in the LOCKINFO part of the output.
DeleteRobert
If "LOCK STATUS" is the parent lock info ,where can we find the child lock info?
DeleteIs this something that can be found by doing computations from lock modes and compatibility table related to this parent lock?
You won't find child lock information in the output of a Db2 -DISPLAY command - that would be unwieldy, as a given process could hold thousands of child locks at one time. There might be a Db2 performance trace record that would provide child lock information, but that would probably be a trace with significant CPU time that you would not want to incur.
DeleteChild lock type is generally quite straightforward, and is based on the SQL DML statement in question: data-changing statements (INSERT, UPDATE, DELETE) will get X child locks for changed pages or rows; read-only statements (e.g., read-only SELECT statements) will get S child locks on pages or rows accessed; a cursor defined with FOR UPDATE will get U child locks on rows or pages accessed, and those U locks will be converted to X locks if rows are changed by way of the cursor. The detailed information about child locks acquired for various SQL DML statements, with various LOCKSIZE and ISOLATION values in effect, is found in the tables on this page in the Db2 for z/OS documentation: https://www.ibm.com/docs/en/db2-for-zos/13?topic=types-locks-acquired-sql-statements.
Robert
Hello,
ReplyDeleteIntent share option in Partition, table space, and table lock modes mentioned as "Concurrent processes can both read and change the data".
https://www.ibm.com/docs/en/db2-for-zos/12?topic=locks-lock-modes-compatibility
But the TABLE 2 stats Lock mode IS is not compatible with X .Both are contradicting .what do you think?
There's no contradiction here. A process that is reading data from a Db2 for z/OS table will typically get an IS "parent" lock ("parent" refers to a lock at the partition, table space, or table level). A process that is going to change data in a table (insert and/or update and/or delete) will typically get an IX parent lock. IS and IX locks do not conflict with each other; so, indeed, when a process holds an IS parent lock, other processes can read and/or change data in the partition/table space/table in question.
DeleteX parent locks, which are quite unusual (generally acquired by a process explicitly as the result of a LOCK TABLE statement, or by way of lock escalation), are not compatible with any other lock on the partition/table space/table in question - they block out everything else.
Robert
This is what I'm looking at :
Delete"Concurrent processes can both read(both IS &S LOCK applies I think) and change the data(X AND IX lock applies I think)" .
When we say "change the data" it can be X lock as well as IX ?
If I consider X lock ,then for IS-> Shouldn't we address it as "concurrent process can read but not change"?
It seems that you have a problem with the documentation stating that when a process holds an IS parent lock (table space- or partition-level lock) then other processes can change as well as read data in the associated table space or partition. THAT IS A TRUE STATEMENT. When a process holds an IS lock on a table space or partition (almost always the case for a data-reading process), all this is required for another process to change (as well as read) data in the table space or partition is for the other process to acquire an IX lock on the table space or partition. In fact, THAT IS ALMOST ALWAYS WHAT HAPPENS. A data-changing process will get an IX parent lock just about every time. An IS parent lock will block a request for an X lock on the relevant table space or partition, but in my experience exclusive parent locks (whether S or X) are highly unusual. Generally speaking, such a parent lock will be acquired either through execution of a LOCK TABLE statement or because of lock escalation, and what I have observed is that use of LOCK TABLE and lock escalation are both quite rare - in plenty of Db2 for z/OS environments, we're talking about exceedingly rare.
DeleteAgain, the thing for you to keep in mind is that the parent lock acquired by a data-reading process WILL ALMOST ALWAYS be IS, and the parent lock acquired by a data-changing process WILL ALMOST ALWAYS be IX, and IS and IX parent locks are absolutely compatible with each other.
Robert