|
|
|
|
This article was originally printed in DB2 Magazine.
In Part 1, I described a database as well as the three types of table spaces that can be defined within a database.
Locking Levels
The LOCKSIZE parameter is part of the CREATE TABLESPACE data definition language. What are the options for that parameter? In general (without regard to table space type), we can choose from five LOCKSIZE parameter values: TABLESPACE, TABLE, PAGE, ANY, and ROW. The larger the LOCKSIZE value (the farther to the left in that list), the lower the overhead and the less concurrency (in other words, one or two locks can buy you access to everything you need to see, but might cause others to have difficulty accessing the data that you control). The smaller the LOCKSIZE (the farther to the right in the list), the more overhead, but also the more concurrency (in other words, for the price of more locks, more people can be using the data simultaneously without getting into each other's way). These locksizes are referred to as locking levels (as in, "I am doing page-level locking," or, "this table space is defined with row-level locking").
Locksize Any
Let's get this LOCKSIZE ANY out of the way for now. ANY is a schizophrenic LOCKSIZE value. It starts as page-level locking, and then morphs into its other identity when opportunity and necessity permits. In Part 3, I'll discuss this identity switch while covering LOCK ESCALATION (what that term meant in days of old as well as what it means today with the MAXLOCKS parameter). So, for now, let us ignore LOCKSIZE ANY.
Writers Vs. Readers
Before we get specific about the locking levels for each of the three table space types, we must keep in mind a general concept. Readers are generally very friendly with each other. If (and that is a big if) they get locks on their locking level, they get SHARE (S) locks. They may even acquire no locks at all. They don't mind sharing their level with other readers, and sometimes they don't mind sharing space with writers (those doing maintenance). They're a friendly sort.
Writers, however, aren't friendly at all. They certainly won't share their level with each other. Nor will they share with most readers. They pretty much want exclusive control of their locking level. The only users who can access their data when they have EXCLUSIVE (X) locks on the level are readers willing to read uncommitted data.
How The Levels Work
Think of the locking levels as a three-part hierarchical structure with table space at the top (see Figure 1).
FIGURE 1. The DB2 locking-level hierarchy.
The path on the left is that of a simple table space. The path in the middle is that of a segmented table space. And the path on the right is that of a partitioned table space.
Simple Table Spaces
Three LOCKSIZE options control serialized access to a simple table space: TABLESPACE, PAGE, and ROW.
As you might remember from Part 1, rows from multiple tables may share a single page in a simple table space. This means that if your simple table space holds more than one table, there's no ability to lock each table individually. There is no table-level locking for the simple table space.
So, if a simple table space is defined with LOCKSIZE PAGE, the user who accesses that table space (intending to read a page) must first acquire a table space lock. However, you should be using segmented or partitioned table spaces for your tables, as simple table spaces are deprecated. In the next release of DB2, you'll be able to keep the simple table spaces you created in version 8 and earlier, but you won't be able to create any new ones.
Segmented Table Spaces
Four LOCKSIZE options control serialized access to a segmented table space: TABLESPACE, TABLE, PAGE, and ROW.
So, if a segmented table space is defined with LOCKSIZE PAGE, the user who accesses that table space (intending to read a page) must first acquire a table space lock and then a table lock.
Partitioned Table Spaces
Three LOCKSIZE options control serialized access to a PARTITIONED table space: TABLESPACE, PAGE, and ROW.
As you remember from Part 1, a PARTITIONED table space can only contain a single table. Therefore, there's no need for table-level locking. However, there may be a need to lock at a level between PAGE and TABLESPACE—the PARTITION level. In versions 6 and 7, a parameter called LOCKPART* is part of the DDL for creating a partitioned table space. When this parameter is set to NO (the default), and LOCKSIZE is set to PAGE, the user who accesses the table space (intending to read a page) must first acquire only a table space lock before accessing the page. When this parameter is set to YES, and LOCKSIZE is set to PAGE, the USER who accesses the table space (intending to read a page) must first acquire a table space lock and then a partition lock (on the partition in which the page resides).
Also, setting this parameter to YES allows programmers to explicitly override page- or row-level locking and switch to partition-level locking. They can do this by coding:
LOCK TABLE mytable PART 4 in SHARE MODE
An S lock on the whole partition would allow readers, but no writers, to access the data while this program is being run. Programmers can also code:
LOCK TABLE mytable PART 1 in EXCLUSIVE MODE
This statement would prevent everyone except those willing to read uncommitted data from accessing the data. The default for the versions 6 and 7 LOCKPART parameter was NO (for upward compatibility with the days when no partition locking was possible). However, IBM highly recommended that DBAs use LOCKPART YES because of performance issues, especially in a data sharing environment. The performance issues are so important that the LOCKPART parameter was eliminated in version 8; the only option now is to do partition-level locking.
Getting Into The Building...
Let's assume LOCKSIZE PAGE and a SEGMENTED TABLESPACE and that I'm trying to do an insert. To insert a row into a table, using the hierarchical structure in Figure 1, I must first get into the table space. Now I don't need an exclusive (X) lock on the table space but I do need a milder lock that will let folks know that I'm in the table space. The perfect lock for this is an "intent lock," in this case an IX lock (I "intend" to do something "exclusive" = IX). So, I ask for one of these "I intend to do something exclusive" lock (an IX lock) on the table space. Then I must get into the table. I do so by asking for another "I intend to do something exclusive" lock (IX lock)—on the table. I'll be holding two IX locks before I even touch a page. Just as you must first get into a building to get onto a floor to get into a room, in a DB2 segmented table space you must first get into the table space (building) to get into the table (right floor) to get to a page (room). Although most buildings in DB2 are single-story buildings, the table level lock is needed just in case someone defines another table in the same table space.
For a partitioned table space created in version 8 (or created using LOCKPART YES in prior releases), you must first get into the building (table space) to get into a tower (partition) to get into a room (page). With page level locking, intent locks will be acquired on the table space and the partition.
Just as there is no magic way of leaping into a room without first getting into the building and onto the right floor, there's no way to get into a page without locking the table space and table/partition.
The good news is that the intent locks are very friendly with each other. IX locks are compatible with other IX locks and they are also compatible with IS. So, if they're so friendly, why do we acquire them?
Holding these intent locks lets the lock manager know that someone is in the building, or the floor, or the tower without having to search room by room. If someone comes along and wants to burn down the building or lay new carpet on the floor or work on the elevator in the tower, the lock manager can see at a glance if someone with an IX or IS lock is there. You see, neither of these will share with an X lock. A LOCK TABLE mytable Part 4 in EXCLUSIVE MODE wouldn't be successful because these INTENT locks signify the presence of incompatible users.
Once we start entering the rooms (pages or rows), things get a bit more serious and a bit less friendly. At the page or row level, we get S (share for readers) locks, U (read for update) locks, or X (exclusive for writers) locks. Or we may, in fact, get no lock at all (a reader willing to read uncommitted data won't get a page or row lock). To provide data consistency, use "latching" for serialization at a lower cost.
The locking matrix (Figure 2) at the page level shows that:
X locks share with no other locks,
S locks share with other S locks as well as U locks, and
U locks share only with S locks.
In other words, a single page could have one X lock on it, and no other lock. Another page could have one or more S locks on it. A third page could have one or more S locks on it along with one and only one U. A U lock will not share a page with another U lock; no two users can read the same page for update at the same time.
FIGURE 2. The DB2 locking matrix.
Looking Ahead
Now that we understand how locks are handled in table spaces, we can move on to tackle some of the nuances of locking. In the next column we will look at how DSNZPARMs are used to control the length of time we're willing to wait for a lock, the maximum number of locks we may acquire, and other system level controls. I will also discuss issues such as timeouts vs. deadlocks and lock escalation vs. lock upgrades. Finally, we know that we don't get locks on indexes. So how can an index-only job timeout? That, too, is a topic in Part 3.
Bonnie Baker is a consultant and educator specializing in application performance issues on the DB2 OS/390 and z/OS platforms. She is an IBM DB2 Gold Consultant, a five-time winner of the IDUG Best Speaker award, and a member of the IDUG Speakers' Hall of Fame. She is best known for her series of seminars entitled "Things I Wish They'd Told Me Eight Years Ago" and the Programmers Only column in DB2 Magazine. You can reach her through Bonnie Baker Corp. at 813-837-3393 or bkbaker@bonniebaker.com.
"Table
Spaces and Locking Levels, Part 1," DB2 Magazine, Quarter 1, 2005
