Note: For applications reading rows using ISOLATION(UR) or lock avoidance, page or row locks are not taken on the base table. However, DB2 takes S-locks on the LOB to ensure that a partial or inconsistent LOB is not accessed.
Note: Inline LOBs, available as of DB2 V10, are treated like normal data and do not require LOB locking.
One reason LOB locks are used is to determine whether space from a deleted LOB can be reused by an inserted or updated LOB. DB2 will not reuse the storage for a deleted LOB until the DELETE has been committed and there are no more readers on the LOB. Another purpose for locking LOBs is to prevent deallocating space for a LOB that is currently being read. All readers, including “dirty readers” acquire S-locks on LOBs to prevent the storage for the LOB they are reading from being deallocated.
Types of LOB Locks
There are only two types of LOB locks:
- S-locks, or SHARE: The lock owner and any concurrent processes can SELECT, DELETE, or UPDATE the locked LOB. Concurrent processes can acquire an S-lock on the LOB.
- X-locks, or EXCLUSIVE: The lock owner can read or change the locked LOB, but concurrent processes cannot access the LOB.
Just like regular transaction locking, though, DB2 also takes LOB table space locks. If the LOB table space has a gross lock, DB2 does not acquire LOB locks. The following lock modes can be taken for a the LOB table space:
- S-lock, or SHARE: The lock owner and any concurrent processes can read and delete LOBs in the LOB table space. The lock owner does not need to take individual LOB locks.
- IS-lock, or INTENT SHARE: The lock owner can UPDATE LOBs to null or zero-length, or SELECT or DELETE LOBs in the LOB table space. Concurrent processes can both read and modify LOBs in the same table space. The lock owner acquires a LOB lock on any data that it reads or deletes.
- X-lock, or EXCLUSIVE: The lock owner can read or change LOBs in the LOB table space. The lock owner does not need to take individual LOB locks.
- IX-lock, or INTENT EXCLUSIVE: The lock owner and any concurrent process can read and change data in the LOB table space. The lock owner acquires an individual LOB lock for any LOB it accesses.
- SIX-lock, or SHARE WITH INTENT EXCLUSIVE: The lock owner can read and change data in the LOB table space. The lock owner obtains a LOB locks when inserting or updating. Concurrent processes can SELECT or DELETE data in the LOB table space (or UPDATE the LOB to a null or zero-length).
As with transaction locking, there is a hierarchical relationship between LOB locks and LOB table space locks (see Figure 1). If the LOB table space is locked with a gross lock, LOB locks are not acquired.
Figure 1. The DB2 LOB locking hierarchy.
The type of locking used is controlled using the LOCKSIZE clause for the LOB table space. LOCKSIZE TABLESPACE indicates that no LOB locks are to be acquired by processes that access the LOBs in the table space. Specifying LOCKSIZE LOB indicates that LOB locks and the associated LOB table space locks (IS or IX) are taken. The LOCKSIZE ANY specification allows DB2 to choose the size of the lock, which is usually to do LOB locking.
Duration of LOB Locks
The ACQUIRE option of BIND has no impact on LOB table space locking. DB2 will take locks on LOB table spaces as needed. However, the RELEASE option of BIND does control when LOB table space locks are releases. For RELEASE(COMMIT), the LOB table space lock is released at COMMIT (unless WITH HOLD is specified or a LOB locator is held).
LOB locks are taken as needed and are usually released at COMMIT. If that LOB value is assigned to a LOB locator, the S-lock on the LOB remains until the application commits. If the application uses HOLD LOCATOR, the locator (and the LOB lock) is not freed until the first commit operation after a FREE LOCATOR statement is issued, or until the thread is deallocated. If a cursor is defined WITH HOLD, LOB locks are held through COMMIT operations.
LOB Table Space Locking Considerations
Under some circumstances, DB2 can avoid acquiring a lock on a LOB table space. For example, when deleting a row where the LOB column is null, DB2 need not lock the LOB table space.
DB2 does not access the LOB table space in the following instances:
- A SELECT of a LOB that is null or zero-length
- An INSERT of a LOB that is null or zero-length
- When a null or zero-length LOB is modified (by UPDATE) to a null or zero-length
- A DELETE for a row where the LOB is null or zero-length
0 comments:
Post a Comment