Work with locks

To prevent simultaneous access to smart-large-object data, the database server 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 could 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 server 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)
    • In update mode, when you open a smart large object for writing (write-only, read/write, write/append)

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

  • The isolation level of the current transaction

    If the database table has an isolation mode of Repeatable Read, the server does not release any locks that it obtains on a smart large object until the end of the transaction.

By default, the server chooses the lock-all lock mode.

The server 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 close 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 server 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 server 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 server releases the lock when the current transaction terminates. However, the server obtains the lock again when the next function that needs a lock executes. If this behavior is undesirable, the server-side SQL application can use BEGIN WORK transaction blocks and place a COMMIT WORK or ROLLBACK WORK statement after the last statement that needs to use the lock.