Learning Objectives
After completing this session, you will be able to:
Describe how DB2 achieves the two conflicting goals of data integrity and data concurrency
Lock - Introduction
DB2 automatically guarantees the integrity of data by enforcing several locking strategies. These strategies permit multiple users from multiple environments to access and modify data
concurrently. DB2 locks prevent one program from accessing data that has been changed, but not yet committed, by another program. Locking process is controlled by DB2’s IRLM (Inter System Resource Lock Manager). However, whenever practical, DB2 tries to lock pages without going to the IRLM. This type of lock is called a latch.
Table Space - Recap
Data is actually stored in a structure known as table space. Each table space correlates to one or more individual physical VSAM data sets in the DASD volumes of a storage Group. Each table space contains one or more tables.
There are three different types of table space and are as follows:
Simple table space
Segmented table space
Partitioned table space Simple Table Space
In a simple table space, the space is divided into pages without any higher level structure. Simple table space can contain data from more than one table. As data rows from different tables can reside on the same page, concurrency will be reduced a lot. After DB2 version 2.1, the simple table spaces are almost obsolete.
Segmented Table Space
In a segmented table space, the space is divided into equal sized group of pages called
“Segments”. Each segment can contain rows from only one table. This is the most efficient type of table space because it maximizes concurrency.
Partitioned Table Space
In the partitioned table space, the space is divided into units called “Partitions”. Each partition contains part of one table and resides on a separate VSAM data set. Each partition table space can contain only one table. This is suitable for large tables that contain one million or more pages.
Lock Size
When a table space is defined or altered, the LOCKSIZE clause specifies a default lock size.
The lock size can be:
ROW
PAGE
TABLE
TABLESPACE
ANY
When the LOCKSIZE(ANY) option is used, DB2 selects the optimum lock size for each processing situation.
Lock Escalation
Lock hierarchy is as follows:
If the number of locks in one level exceeds an installation default, then DB2 locks a larger unit.
This is called Lock escalation.
In a non-segmented table space, the page or row lock is escalated to table space lock. In a segmented table space, the page or row lock is first escalated to table and then if necessary to a table space lock.
Lock Duration
Lock duration refers to the length of the time that a lock is maintained. The duration of a lock is based on the BIND options chosen for the program requesting locks. Locks can be acquired either immediately when the plan is requested to be run or iteratively as needed during the execution of the program.
Bind parameters affecting table space and table Locks
ACQUIRE(ALLOCATE):
o Locks will be acquired when the plan is allocated, which normally occurs when the first SQL statement is issued.
o This is used for batch processing.
ACQUIRE(USE):
o Locks will be acquired only as they are required, SQL statement by SQL statement.
o This is used for online processing.
RELEASE (DEALLOCATE): Locks are not released until the plan is terminated and is used for batch processing.
RELEASE(COMMIT): Locks are released when a COMMIT is issued and is used for online processing.
Bind parameters affecting page and row Locks
ISOLATION level:
o Repeatable Read (RR): This holds page and row locks until a COMMIT point is reached. No other program can modify the data. If the data is accessed twice during the unit of work, the same exact data will be returned.
o Read Stability (RS): This holds page and row locks until a COMMIT point is reached. But other programs can INSERT new data. If data is accessed twice during the unit of work, new rows may be returned, but old rows will not have changed.
o Cursor Stability (CS): This acquires and releases page or row locks as pages or rows are read and processed. This provides the greatest level of concurrency. But there is a chance of different data being returned by the same cursor if it is processed twice during the same unit of work.
o Uncommitted Read (UR): This is also known as dirty read processing. UR avoids locking altogether. Data can be read that may never actually exist in the database.
We can use this for working with the table that is rarely updated.
Regardless of the ISOLATION level chosen, all page locks are released when a COMMIT is encountered.
Type of Tablespace Space Division Contains
Simple No hierarchy; Only pages A page can contain rows from different tables
Segmented Segments One Segment – One Table
Partitioned Partitions Entire Partition table space – One table
Bind parameters affecting table and tablespace locks:
Lock Duration Locks are acquired
or released Recommendation ACQUIRE(ALLOCATE) When plan is allocated Use for batch processing
ACQUIRE(USE) As they are needed Use for online processing RELEASE(DEALLOCATE) When plan is
terminated
Use for batch processing
RELEASE(COMMIT) When a commit occurs Use for online processing
Bind parameters affecting page and row locks:
o Isolation level:
Repeatable Read (RR) Read Stability (RS) Cursor Stability (CS) Uncommitted Read (UR) Test Your Understanding
1. Explain about the three types of tablespaces.
2. Explain about the options available in the lock size.
3. What is lock escalation?
4. What is lock duration and how is it being achieved?
5. Explain about the different isolation levels.
Session 52: Locks
Learning Objectives
After completing this session, you will be able to:
Describe the remaining concepts of lock Lock Mode
Lock Mode determines what the program that owns the lock and what concurrent programs can do with the locked resource.
Following is the list of modes of table and table space locks:
Lock Meaning Access Acquired Access Allowed to Others
S SHARE Read only Read only
U UPDATE Read with intent to update Read only
X EXCLUSIVE Update No access
IS INTENT SHARE Read only Update
IX INTENT EXCLUSIVE Update Update
SIX SHARE/INTENT EXCLUSIVE
Read or update Read only
The following list tells you how tablespace locks are acquired:
Type of Processing LOCKSIZE Isolation Initial Lock
Acquired
MODIFY ANY CS IX
MODIFY PAGE/ROW CS IX
MODIFY TABLESPACE CS X
MODIFY ANY RR X
MODIFY PAGE/ROW RR X
MODIFY TABLESPACE RR X
SELECT ANY CS IS
SELECT PAGE/ROW CS IS
SELECT TABLESPACE CS S
SELECT ANY RR S
SELECT PAGE/ROW RR S
SELECT TABLESPACE RR S
The following list tells us how Table Locks are acquired Type of
Processing
LOCKSIZE Isolation Table space Lock Acquired
Table Lock Acquired
MODIFY ANY CS IS IX
MODIFY PAGE CS IS IX
MODIFY TABLE CS IS X
MODIFY ANY RR IS X
MODIFY PAGE RR IS X
MODIFY TABLE RR IS X
SELECT ANY CS IS IS
SELECT PAGE CS IS IS
SELECT TABLE CS IS S
SELECT ANY RR IS S
SELECT PAGE RR IS S
SELECT TABLE RR IS S
Following is the modes of page and row locks.
Lock Meaning Access Acquired Access Allowed to Others
S SHARE Read only Read only
U UPDATE Read with intent to update Read only
X EXCLUSIVE Update No access
The following list tells us how page Locks are acquired.
Type of Processing Page Lock Acquired Pages Affected SELECT/FETCH S Page by page as they are fetched OPEN CURSOR for S All pages affected SELECT SELECT/FETCH FOR UPDATE
OF
U Page by page as they are fetched
UPDATE X Page by page
INSERT X Page by page
DELETE X Page by page
The following list tells us how row Locks are acquired
Type of Processing Row Lock Acquired Rows Affected
Type of Processing Row Lock Acquired Rows Affected
UPDATE X Row by row
INSERT X Row by row
DELETE X Row by row
Suspension
The longer a lock is held, the greater the potential impact on other applications. When an
application requests a lock that is already held by another process, and the lock cannot be shared, that application is suspended. A suspended process temporarily stops running until the lock can be acquired. Lock suspensions can be a significant barrier to acceptable performance and application availability.
Timeout
When an application has been suspended for a predetermined period of time, it will be terminated.
When a process is terminated because it exceeds this period of time, it is said to be time out.
A timeout is caused by the unavailability of a given resource.
A sample scenario is as follows. This figure illustrates the flow of various transactions of two programs which have been executed simultaneously and how DB2 handles the integrity and concurrency.
Program 1 Program 2
Update Table A/Page 1 Lock established
Intermediate processing Update Table A/Page 1
. Lock (wait)
. Lock suspension
. Timeout -911 received
If Program 2, holding no other competitive locks, then requests a lock currently held by Program 1, DB2 tries to obtain the lock for a period of time. Then it quits trying. This example illustrates a timeout.
Deadlock
A deadlock occurs when two separate processes compete for resources held by one another. To break the deadlock, DB2 rolls back the current unit of work for one of the programs after the preset time interval for deadlocks and then terminates that program with the SQLCODE of -911 or -913.
This program is called victim. This will free the locks and allow the remaining program to continue.
Sample Scenario is as follows. This figure depicts how a deadlock scenario will happen by describing the flow of tasks in two programs executed simultaneously.
Program 1 Program 2
Update Table B/Page 1 Update Table A/Page 1
Lock established Lock established
Intermediate processing Intermediate processing
Update Table A/Page 1 Update Table B/Page 1
Lock (wait) Deadlock Lock (wait)
A deadlock occurs when Program 1 requests a lock for a data page held by Program 2 and Program 2 requests a lock for a data page held by Program 1.
DB2's solution for the deadlock is to target one of the two programs as the victim of the deadlock and deny that program's lock request by setting the SQLCODE to -911.
Constructs that affect locking
COBOL provides three constructs that affect locking:
WITH clause
WITH HOLD clause
LOCK TABLE statement
WITH Clause: This can be used to override the isolation level of a bound plan or package.
This can be used in the following:
SELECT statement
SELECT INTO statements
Searched deletes
Searched updates
INSERT statement that uses subquery
This cannot be used in subqueries except in INSERT statements. The isolation level in the WITH clause is effective only for the statement in which it appears.
Example:
SELECT *
FROM TB_PROJECT WITH UR;
WITH HOLD Clause: As you have already seen the cursor position is maintained past a commit point. So the locks needed to maintain the position are not released even if they were acquired with ISOLATION(CS) and RELEASE(COMMIT). You need to clearly monitor the use of WITH HOLD clause because this will increase the number of suspensions and timeouts.
LOCK TABLE statement:
LOCK TABLE <table-name> IN SHARE MODE
The LOCK TABLE statement is appropriate in a high priority program. The LOCK TABLE statement is also appropriate in a program that frequently acquires many row or page locks for a table before these locks are escalated to a next level. If the table specified in this statement is not in a
segmented table space, DB2 applies the lock to all of the tables in the table space, not just the one named.
Summary
Lock modes available for tablespaces and tables are:
o S
o U
o X
o IS
o IX
o SIX
Lock modes available for pages and rows are:
o S
o U
o X
When an application requests a lock that is already held by another process, and the lock cannot be shared, then that application is suspended.
When an application has been suspended for a predetermined period of time, it will be terminated and it is timed out.
A deadlock occurs when two separate processes compete for resources held by one another.
COBOL provides three constructs that affect locking:
o WITH clause
o WITH HOLD clause
o LOCK TABLE statement Test Your Understanding
1. Explain about the lock modes of tablespace, table, page, and row locks.
2. What is suspension?
3. What is timeout and how can you resolve the problem?
4. What is deadlock and how is it resolved by DB2?
5. Explain about the different COBOL constructs that affect locking.