Locking Data

Team-Fly

---

 

	DB2 Universal Database for OS/390 v7.1 Application Certification Guide By Susan Lawson
	Table of Contents
	Chapter 16.  Locking and Concurrency

---

Locking Data

DB2 uses transaction locking (via the IRLMInternal Resource Lock Manager), latches, and other non-IRLM mechanisms to control concurrency and access of SQL statements and utilities. These mechanisms are there to associate a resource with a process so that other processes cannot access the same resource when it would cause lost updates and access to uncommitted data. Generally , a process will hold a lock on manipulated data until it has completed its work. This way it ensures that other processes do not get hold of data that has been changed but not committed. Another use is for repeatable read, when an application has the need to reread data that must still be in the same state as it was when it was initially read. There are also user options to avoid locking and allow for access to uncommitted data, and system settings and mechanisms to provide for lock avoidance when it would not cause a data integrity problem.

Not all objects need to have locks acquired on them to establish concurrency. The following objects can be locked:

• User tables

• Catalog tables

• Directory tables

Indexes are not locked, as serialization is controlled by latches, and concurrency is controlled by data locking. There are also drains and claims used to control utilities and DB2 commands. Draining allows utilities and commands to acquire partial or full control of a needed object with minimal interruption to concurrent access.

DB2 often needs to access data in the catalog, and the data being read or updated must be locked. The following locks can be used:

• During binding

  - SKCT (Skeleton Cursor Table for a plan)

  - SKPT (Skeleton Package Table for a package)

• During DDL activity

  - DBD (Database Descriptor)

Lock Attributes

Locks should be viewed as having

• Size : How much data is locked.

• Mode: Whether or not others are allowed to read and/or update the locked object.

• Duration: How long the lock is held.

Each plays a role in the acquisition and release of a lock, and understanding these attributes can help us use them wisely and avoid contention .

More than one type of lock can exist for a specific user for a particular tablespace. This depends on the mode of the lock. For example, locks might be held on the tablespace, table, page, and row simultaneously for a particular user. Each lock has its own mode.

Lock Sizes

Locks can be taken on various objects in DB2. The size of the lock determines how much of the total object will be locked. There are several objects that can be locked in DB2, determining the lock size:

• Non-LOB data:

  - Tablespace Lock the entire tablespace.
  
  

  - Table Only segmented tablespaces allow for an individual table to be locked.
  
  

  - Partition Lock only a partition.
  
  

  - Page Lock an individual page in a table.
  
  

  - Row Lock an individual row in a table.
  
  

• LOB data

  - LOB locks.

Tablespace locks are the most encompassing lock, and of course this allows for the least amount of concurrency. It would seem that for maximum concurrency, we should use row locking; however, locks are overhead and row-level locks can cause excessive overhead in terms of processing time and storage. Page locking is generally the acceptable lock level for the majority of situations. There are always exceptions, however, such as table locking (discussed later).

NOTE

It is important to understand that in the hierarchy of locks, row locks and page locks occur at the same level. If either a page lock or a row lock is escalated, it moves to the same higher level, and a row lock can never escalate to a page lock.

LOCK TABLE Statement

There are some situations that may call for the ability to exclusively lock a table or partition. There is a single SQL statement that can override locking at any time: the LOCK TABLE IN EXCLUSIVE MODE statement. A typical reason to override locking is if you want to take a snapshot of data and be able to access a table without any other concurrent processes changing data. You can allow other processes read-only access to the table by using the LOCK TABLE IN SHARE MODE statement. This is different from the repeatable read (RR) isolation, because RR prevents changes only from rows or pages that you have already accessed. You can prevent all other access with LOCK TABLE IN EXCLUSIVE MODE.

The LOCK TABLE/TABLESPACE statement can be used only to get a lock, in either share (S) mode or exclusive (X) mode. The lock is acquired when the SQL statement is executed. Its release is dependent upon the previous settings of the ACQUIRE and RELEASE parameters. The table or tablespace lock is released at thread deallocation if RELEASE(DEALLOCATE) was specified in the options when the program was bound. The lock also can be released at either COMMIT or ROLLBACK time if the RELEASE(COMMIT) option was specified at bind time. Despite the type of locking held when the SQL LOCK TABLE statement was issued, when the COMMIT or ROLLBACK is issued, the system falls back to page-level locking unless another SQL LOCK TABLE statement is issued. A user or application must constantly reissue the SQL LOCK TABLE statement after the COMMIT or ROLLBACK when RELEASE(COMMIT) has been specified if locking is still needed at the table or tablespace level.

The LOCK TABLE IN EXCLUSIVE MODE statement can also be used to avoid additional overhead if you are updating a large portion of a table. This can be more efficient, because by preventing concurrent accesses to the data, DB2 avoids the overhead to lock and unlock each page during updates.

Timeouts can also be prevented by using either the LOCK TABLE IN EXCLUSIVE or IN SHARE MODE statement. This allows you to process the data quickly without the possibility of having to contend for locks with other applications.

If further granularity is needed, you can choose to lock only the partition, as seen in the following example:

 LOCK TABLE CERTTB PART 1 IN EXCLUSIVE MODE 

This example would take an X lock on Part 1 regardless of any other locks held on other partitions in the tablespace.

NOTE

The PART keyword can be used only on tablespaces defined with LOCKPART(YES).

Selective Partition Locking

By using LOCKPART(YES) for a partitioned tablespace, only the individual partitions are locked when they are accessed. This is known as selective partition locking (SPL). It also provides the ability to tell DB2 not to escalate locks to the tablespace level, but rather to the partition level. This allows for less propagation of locks to the coupling facility in a data sharing environment and better concurrency for applications accessing data in various partitions. This can be especially useful if usage against the partitions is spread over multiple members using affinity routing.

However, SLP will help in a nondata sharing environment only if an agent actually escalates. If you are not having escalation problems, then you may want to consider not using SPL. Without SPL, the parent intent lock is taken on the last partition, regardless of which ones you access. These locks are almost always intent locks and therefore almost never cause a problem. With SPL, the parent intent lock is taken on whichever partition you access. If escalation occurs, with SPL it occurs just for the partition (or partitions) on which excessive locks occurred. Since, without SPL, the parent lock is only on the last one, we escalate the lock size to the entire page set and access to all partitions is denied .

LOB Locking

There is a new lock mode for LOBs called a LOB lock. LOB locks have different characteristics from regular locks. There is no concept of row or page locking with a LOB. LOB locking is not at all like the traditional transaction-level locking. Since LOBs are in an associated object, concurrency between the base table and the LOB must be maintained at all times, even if the base table is using UR (uncommitted read). A LOB lock still needs to be held on the LOB to provide consistency and most importantly to maintain space in the LOB tablespace. LOB locks avoid conflicts between readers and deletes and updates. A SELECT or DELETE will acquire a share-mode LOB lock. The lock doesn't prevent the DELETE, but it will prevent the reuse of the deallocated pages until all of the LOB locks are released. A shadow copy of the LOB will exist until the LOB locks are released. INSERTs will acquire exclusive LOB locks on new LOBs (an UPDATE is basically a DELETE followed by an INSERT). The LOCK TABLE statement, which acquires a lock on the entire object, can still be used and will not require individual LOB locks.

The ACQUIRE option of BIND has no effect on when the tablespace lock on the LOB tablespace is taken. Locks on LOB tablespaces are acquired when they are needed. The tablespace lock is released according to the value specified on the RELEASE option of BIND with a couple of exceptions: when a cursor is defined WITH HOLD or a LOB locator is held. When a cursor is defined WITH HOLD, LOB locks are held through commit operations. When a LOB value is assigned to a LOB locator, the lock acquired will remain until the application commits. If the application uses HOLD LOCATOR, the locator and the LOB lock must be held and cannot be freed until certain conditions exist. The locks would be held until the first commit operation after a FREE LOCATOR statement is issued or until the thread is deallocated. Using LOB columns generally requires an increase in the number of locks held by a process. As a starting point for preventing unforeseen problems, LOCKMAX should be nonzero on the LOB tablespaces. An even better strategy is to use the gross locks during heavy inserting processes to prevent most of the locking difficulties.

Lock Modes

The following are the lock modes in DB2:

• IS intent share

• S share

• IX intent exclusive

• U update

• SIX share with intent exclusive

• X exclusive

Modes are easy to understand with a simple formula. If the table or tablespace lock has an I in it, which stands for intent, then row or page locks are in use on the individual pages. In all other cases, the table or tablespace lock will be the only lock used. The intent locks act as an indicator to DB2 to identify what is occurring within the table or tablespace. Some locking is beyond the control of the programmer and designer, while other locks can be controlled to a degree. Table 16-1 lists the compatibility of the different lock modes for tablespaces, tables, and partitions. Table 16-2 lists the compatibilities for pages and rows.

Table 16-1. Tablespaces, Tables, and Partitions

Lock Mode	IS	IX	S	U	SIX	X
IS	yes	yes	yes	yes	yes	no
IX	yes	yes	no	no	no	no
S	yes	no	yes	yes	no	no
U	yes	no	yes	no	no	no
SIX	yes	no	no	no	no	no
X	no	no	no	no	no	no

Table 16-2. Pages and Rows

Lock Mode	S	U	X
S	yes	yes	no
U	yes	no	no
X	no	no	no

Table or Tablespace Locks

When using page or row locks, the table or tablespace lock will be

• IS

  - Lock owner wants to read only (may lock pages/rows)

  - Allow read of lock owner's pages/rows and read/update of all other pages/rows

  1. - The lock owner has share locks on the pages, which allows other users to read and update.

• IX

  - Lock owner wants to update (may lock pages/rows)

  - Allow read/update of all other pages/rows

  1. - The lock owner can have share, update, or exclusive locks on the pages. Other users can still read and update.

• SIX

  - Lock owner wants to read (will not lock pages/rows)

  - Lock owner wants to update (may lock pages/rows)

  - Allow read of all other pages/rows

  1. - The lock owner can have share, update, or exclusive locks on the pages. Other users can still read, but no other user can update.

NOTE

SIX is not a common lock. This mode usually occurs when an IX lock already exists and a share has to be acquired.

When not using row or page locks, the table/tablespace lock will be:

• S

  - Lock owner wants to read only

  - Others are allowed read only

  1. - No one can update.

• U

  - Lock owner wants to read with possibility of update

  - Others allowed read only

  1. - No other user can update.

• X

  - Lock owner wants to update

  - Other access not allowed

  1. - User needs exclusive use of the table or tablespace. No other user is allowed.

Page or row locks

Page locks can only be one of three types:

• S: Restricts other users to read-only use of this page. S or U locks can be acquired by other users. Using SELECT or FETCH without update intent will acquire this lock

• U: This allows the user to go to the actual update step if required. Other applications can get S locks, but nothing else.

• X: User has updated the data on the page and no other user can get any page lock on the page

The lock mode is determined by the Data Manipulation Language (DML) statement according to the following:

SELECT...	S
SELECT...FOR UPDATE OF...	U
UPDATE/DELETE/INSERT	X

DB2 does not allow update (UPDATE/INSERT/DELETE) of uncommitted changes by other users. Reading of uncommitted data is allowed only if isolation level of UR is specified in the SQL WITH clause or as a BIND option.

Lock Durations

Table, Partition, and Tablespace Lock Durations

Tablespace and table lock duration is determined by the BIND ACQUIRE and RELEASE parameters.

• ACQUIRE determines when table, tablespace, or partition locks are taken.

  - ALLOCATE: When the first SQL statement is issued, the maximum required lock is taken on all the objects in the plan or package.
  
  

  - USE: When a SQL statement is issued, the required lock is taken on the involved objects.
  
  

• RELEASE determines when table, tablespace, or partition locks are released

  - DEALLOCATE: At the end of the program.
  
  

  - COMMIT: At COMMIT.
  
  

There is an exception that applies when a cursor has been defined using the WITH HOLD option. The locks needed to maintain position of the cursor are then held until the next commit after the cursor is closed (RELEASE COMMIT) or until the application deallocates it resources (RELEASE DEALLOCATE).

Page or Row Lock Durations

The duration of the lock defines the actual length of time that the lock will be held (the period between acquiring and releasing the lock). We can control how long locks are held through various means, such as commit scopes, program bind parameters, and system parameters. We of course want to maximize concurrency, so we do not want to hold locks longer than necessary (using a large lock for the duration of the process), but we do not want to consume excessive memory and CPU overhead by taking an excessive number of smaller locks (such as taking several row locks over a period of time for the same process).

Isolation levels are set when a program is bound or when used in an individual SQL statement using the WITH ISOLATION clause. The isolation levels directly affect the duration that locks are held. The isolation levels that can be used either at bind time or with an individual SQL statement are

• UR uncommitted read

• CS cursor stability

• RS read stability

• RR repeatable read

Of these, cursor stability, read stability, and repeatable read have to do with lock durations, where uncommitted read has to do with overriding locks.

Cursor Stability

This level of program isolation holds a lock on the row or page (depending on the lock size defined) only if the cursor is actually positioned on that row or page. The lock will be released when the cursor moves to a new row or page, but the lock will be held until a commit is issued if changes are being made to the data. This option allows for the maximum concurrency for applications that are accessing the same data, but cannot allow uncommitted reads.

Read Stability

This option holds locks on all rows or pages qualified by stage 1 predicates for the application until the commit is issued. Nonqualified rows or pages, even though touched, are not locked, as with the RR option. Uncommitted changes of other applications cannot be read, but if an application issues the same query again, any data changed or inserted by other applications will be read, since RS allows other applications to insert new rows or update rows that could fall within the range of the query. This option is less restrictive but similar in function to repeatable read.

Repeatable Read

This option holds a lock on all rows or pages touched by an application program since the last commit was issued, whether or not all those rows or pages satisfied the query. It holds these locks until the next commit point, which ensures that if the application needs to read the data again, the values will be the same (no other process could update the locked data). This option is the most restrictive in terms of concurrency of applications.

Uncommitted Read

This option tells DB2 not to take any locks and to ignore other locks and allow read access to the locked data. It allows an application to run concurrently with other processes (except mass deletes and utilities that drain all claim classes).

Table 16-3 shows a comparison of the different isolation levels.

Table 16-3. Isolation Level Comparisons

Access by Other Applications	UR	CS	RS	RR
Can the application see uncommitted changes made by other application processes?	yes	no	no	no
Can the application update uncommitted changes made by other application processes?	no	no	no	no
Can the re-execution of a statement be affected by other application processes?	yes	yes	yes	no
Can updated rows be updated by other application processes?	no	no	no	no
Can updated rows be read by other application processes that are running at an isolation level other than UR?	no	no	no	no
Can updated rows be read by other application processes that are running at the UR isolation level?	yes	yes	yes	yes
Can accessed rows be updated by other application processes?	yes	no	no	no
Can accessed rows be read by other application processes?	yes	yes	yes	yes

S Share Lock Duration

Using SELECT or FETCH without update intent will acquire this lock. It is released for plans bound with CS when:

• A TSO process issues a SQL COMMIT or ROLLBACK.

• An IMS process issues the next SYNCH or ROLB.

• A CICS synch point call occurs.

• A noncursor SQL statement completes without update.

• The cursor position moves to a new row/page without update.

• The cursor is closed.

Using the cursor WITH HOLD option will hold the locks over COMMIT or ROLLBACK processes in TSO and CAF. If there is no COMMIT used in the program, the locks will be held until the cursor is closed. It is important to understand that the lock will be held on the previous row/page until DB2 gets the lock on the new one.

U Update Lock Duration

A FETCH on a cursor that specifies FOR UPDATE OF acquires this lock. UPDATE and DELETE statements without a cursor also acquire this lock. UPDATE and DELETE statements cause an implicit SELECT to occur first, matching the conditions in the WHERE clause. During this SELECT, DB2 places a U lock on the page accessed, before completing the UPDATE or DELETE. DB2 releases the lock for plans bound with CS when

• A TSO process issues an SQL COMMIT or ROLLBACK.

• An IMS process issues the next SYNCH or ROLB.

• The next synch point call occurs in CICS.

• A noncursor SQL statement has completed without update.

• The cursor position moves to a new page without update.

• The cursor is closed without an update having occurred.

Using the cursor WITH HOLD option in TSO will hold the lock over COMMIT and ROLLBACK. However, the COMMIT will demote the U lock back to an S lock. If an update is to occur, then the lock will be promoted to an X lock before changing the data page.

X Exclusive Lock Duration

Exclusive locks are released when

• A TSO process issues a SQL COMMIT or ROLLBACK.

• An IMS process issues the next SYNCH or ROLB.

• The next synch point call occurs in CICS.

The lock will be held over COMMIT and ROLLBACK in TSO and CAF when using the cursor WITH HOLD option. However, the COMMIT will demote the X lock back to an S lock.

---

Team-Fly

Top