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:
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:
Lock AttributesLocks should be viewed as having
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 SizesLocks 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:
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
LOCK TABLE StatementThere 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
Selective Partition LockingBy 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 LockingThere 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 ModesThe following are the lock modes in DB2:
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
Table 16-2. Pages and Rows
Table or Tablespace LocksWhen using page or row locks, the table or tablespace lock will be
NOTE
When not using row or page locks, the table/tablespace lock will be:
Page or row locksPage locks can only be one of three types:
The lock mode is determined by the Data Manipulation Language (DML) statement according to the following:
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 DurationsTable, Partition, and Tablespace Lock DurationsTablespace and table lock duration is determined by the BIND ACQUIRE and RELEASE parameters.
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 DurationsThe 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
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 StabilityThis 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 StabilityThis 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 ReadThis 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 ReadThis 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
S Share Lock DurationUsing SELECT or FETCH without update intent will acquire this lock. It is released for plans bound with CS when:
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 DurationA 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
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 DurationExclusive locks are released when
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 |