Locking modes

The smart-large-object open mode includes a lock mode, which determines the kind of the lock requests made on a smart large object.

To prevent simultaneous access to smart-large-object data, the smart-large-object optimizer obtains a lock on this data when you open the smart large object. This smart-large-object lock is distinct from the following kinds of locks:
  • Row locks

    A lock on a smart large object does not lock the row in which the smart large object resides. However, if you retrieve a smart large object from a row and the row is still current, the database server might hold a row lock as well as a smart-large-object lock. Locks are held on the smart large object instead of on the row because many columns can be accessing the same smart-large-object data.

  • Locks of different smart large objects in the same row of a table

    A lock on one smart large object does not affect other smart large objects in the row.

The following table shows the lock modes that a smart large object can support.
Table 1. Lock modes for a smart large object
Lock mode Purpose Description
Lock-all Lock the entire smart large object Indicates that lock requests apply to all data for the smart large object
Byte-range Lock only specified portions of the smart large object Indicates that lock requests apply only to the specified number of bytes of smart-large-object data
When the smart-large-object optimizer opens a smart large object, it uses the following information to determine the lock mode of the smart large object:
  • The access mode of the smart large object
    The database server obtains a lock as follows:
    • In share mode, when you open a smart large object for reading (read-only or dirty read)
    • In update mode, when you open a smart large object for writing (write-only, read-write, write/append, truncate)

      When a write operation (or some other update) is actually performed on the smart large object, the database server upgrades this lock to an exclusive lock.

  • The isolation level of the current transaction

    If you have selected an isolation mode of repeatable read, the smart-large-object optimizer does not release any locks that it obtains on a smart large object until the end of the transaction.

By default, the smart-large-object optimizer chooses the lock-all lock mode. You can request locks on the data of a smart large object at the byte level with a byte-range lock.

The smart-large-object optimizer retains the lock as follows:
  • It holds share-mode locks and update locks (which have not yet been upgraded to exclusive locks) until one of the following events occurs:
    • The closing of the smart large object
    • The end of the transaction
    • An explicit request to release the lock (for a byte-range lock only)
  • It holds exclusive locks until the end of the transaction even if you close the smart large object.
When one of the preceding conditions occurs, the smart-large-object optimizer releases the lock on the smart large object.
Important: You lose the lock at the end of a transaction even if the smart large object remains open. When the smart-large-object optimizer detects that a smart large object has no active lock, it automatically obtains a new lock when the first access occurs to the smart large object. The lock that it obtains is based on the original access mode of the smart large object.

The smart-large-object optimizer releases the lock when the current transaction terminates. However, the optimizer obtains the lock again when the next function that needs a lock executes. If this behavior is undesirable, use BEGIN WORK transaction blocks and place a COMMIT WORK or ROLLBACK WORK statement after the last statement that needs to use the lock.