We all know DB2 places lock on the data when we query the database.But why does DB2 does that?.
Answer is very simple. To maintain
concurrency and
consistency.
To make it clear, Concurrency means "Access to the data by two or more applications using SQL query."
By Consistency we mean that "when ever the data is getting changed, the control information of the page must be updated and it should be consistent". As we go deeper into locking,we must know that DB2 handles this locking mechanism with the help of IRLM.
How does this locking mechanism happens ?
IRLM stands for Internal Resource lock manager.As the name says it all, handles all these locking mechanism. IRLM runs in different subsystem and in its own address space.Every DB2 subsystem has its own instance of IRLM running.This is what happens when we run a query.
1->When a SQL Query needs to be run, DB2 talks with IRLM with cross system MVS calls.
2->IRLM receives this request and checks if it can/cannot provide the requested locks.
3->It then conveys this information back to MVS and MVS informs DB2 regarding the lock availability.
The locks set by DB2 have the following attributes :
LOCK SIZE,
LOCK MODE,
LOCK DURATION.
Lets have some more in-depth idea about each of these attributes.
LOCK SIZE: Lock sizes typically specify how much data in the DB2 objects shall be locked.By objects we mean the db2 resources like table space,table and other resource.Broadly DB2 supports locking at 4 levels.Namely A) Table Space B) Table C) Page D) Row. (From most data locked to least data locked)
 |
| A diagrammatic representation of Table and Row level locking |
Table space: Lock the entire table space.
Table: Only segmented table spaces permit an individual table to be locked.
Partition: DB2 Locks only a partition in a
partitioned table space
Page: Lock an individual page in a table space..
This is best choice in terms of concurrency control
Row: Lock an individual row in a table.This is the lowest level of granularity.
Allows two users to access different rows on the same page @ same time,But @ the cost of increased overhead.
Note: we must know that ROW and PAGE lock happens at the same level.
LOB DATA: For large object type (BLOB,CLOB) DB2 provides LOB locks.
XML DATA: DB2 also provides locks for xml data.
Lets see a a brief on each one of these locks below:
Table space and Table locks: In case of simple table space, table locks may lock data in other tables,because rows can be intermingled on different pages. In case of segmented table space,only the pages from single table will be locked.
When accessing a Partioned Table space,DB2 will lock only the partition being used in query, instead of the entire table space.
In a few instances, DB2 may not be able to take partition-level locks:
When the plan or package is bound with ACQUIRE(ALLOCATE)
When the table space is defined with LOCKSIZE TABLESPACE
When the LOCK TABLE statement is used without the PART option.
PAGE locks:DB2 internally continues read/write operation in terms of equal size units known as pages.
The size can vary from 4k,8k,16l,32k. The default page size is 4K.
So a page lock in a Simple Table space may locks rows from other tables since one or more tables can be intermingled on a page. In SEGMENTED TABLE SPACE,a page lock will lock only rows of single table.
ROW locks: Will be needed when multiple applications need page for simultaneous processing.
Note: Row lock & page lock operates occurs at the same level.
LOB locks: LOB or large object locks are bit different from regular locks.
LOB locks are taken when INSERT or UPDATE is used. No locks are taken for
SELECT statemnt or DELETE statement.LOB lock is taken only when it is needed.So even if we use ACQUIRE option in BIND card,it would not have any effect on the LOB table space.
So, how do we specify all these Lock sizes ?
When creating or altering the tablespace, we specify a lock size (ROW, PAGE, TABLE or TABLESPACE) or LOCKSIZE ANY, indicating that DB2 should choose the lock size whenever the table is accessed.
LOCK MODE:
When a sql statement is first executed, DB2 first takes a type of lock,called INTENT LOCK on the table or Tablespace.Later This intent lock
later is changed to 'S', or 'U' or 'X' lock against the row or page.
Types of INTENT LOCK:
Intent share: The transaction intends to read but not update data pages and, therefore, takes S locks on them; it tolerates concurrent transactions taking S, IS, SIX, IX, or U locks.
Intent exclusive: The transaction intends to read or change data and therefore take an X lock against the data pages; it tolerates concurrent transactions taking IX or IS locks.
Shared with intent exclusive: The transaction intends to read or change data; it tolerates other transactions taking an IS lock on the tablespace, which allows them to read data by taking S page locks. They cannot change the data.
Thus, primary purpose of intent lock is to act as a Traffic signal and cause a program to wait for a required S, U, or X lock at the table and tablespace level until the necessary locks have been released from the rows and pages and there is a compatible intent lock.
Further More locks can be taken in share (S), exclusive (X), or update (U) mode.
(S)hare lock is taken is response to a SELECT statement in the query.It allows 2 or more programs to read simultaneously
(X)lusive lock indicates the user needs exclusive use of table or TS/partition for update activity and other access is not allowed.
(U)pdate lock is taken when there is a '
possibility to update'.If the SQL query uses 'FOR UPDATE OF' clause, a U lock is taken.Other applications can get S locks to read the data until the Update(U) lock is promoted to an Exclusive(X) lock at the
instant the update occurs.
A pictorial view of locking
 |
| Pictorial view of 4 lock modes. It shows how concurrent programs locks are affected wile one lock is in place. |
LOCK DURATION:
The lock duration is the length of time a lock can be held by a requester.
It can be handled by the ACQUIRE(ALLOCATE/USE) & RELEASE(DEALLOCATE/COMMIT).
The ACQUIRE and RELEASE parameters impact table space locking.
ACQUIRE(ALLOCATE) versus ACQUIRE(USE): The ALLOCATE option specifies that locks will be acquired when the plan is allocated, which normally occurs when the first SQL statement is issued. The USE option indicates that locks will be acquired only as they are required, SQL statement by SQL statement.
RELEASE(DEALLOCATE) versus RELEASE(COMMIT): When you specify DEALLOCATE for a plan, locks are not released until the plan is terminated. When you specify COMMIT, table space locks are released when a COMMIT is issued.
What is a DB2 LATCH? What is the Difference between a lock and latch?
As we have seen, IRLM maintains the concurrency, A page consistency is maintained by a
latch.
Think of latch as a indicator or signal. If the signal is ON, then the page is latched, ie, the page control information is currently being updated. A latch is
not handled by
IRLM. Latches are purely internal to DB2 and are not visible at transactional level.
