< Day Day Up > |
When a row is read or modified in a table containing LOB columns , the application will obtain a normal transaction lock on the base table. Recall from Chapter 9, "Large Objects and Object/Relational Databases," that the actual values for LOBs are stored in a separate table space from the rest of the table data. The locks on the base table also control concurrency for the LOB table space. But DB2 uses locking strategies for large objects, too. A lock that is held on a LOB value is referred to as a LOB lock . LOB locks are deployed to manage the space used by LOBs and to ensure that LOB readers do not read partially updated LOBs. 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. 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 LocksThere are only two types of LOB locks:
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:
As with transaction locking, there is a hierarchical relationship between LOB locks and LOB table space locks (see Figure 23.5). If the LOB table space is locked with a gross lock, LOB locks are not acquired . Figure 23.5. 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 LocksThe 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 ConsiderationsUnder 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:
|
< Day Day Up > |