Working 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.

Table 8 shows the lock modes that a smart large object can support.

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.

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.

Using Byte-Range Locking

By default, the database server uses whole lock-all locks when it needs to lock a smart large object. Lock-all locks are an "all or nothing" lock; that is, they lock the entire smart large object. When the database server obtains an exclusive lock, no other user can access the data of the smart large object as long as the lock is held.

If this locking is too restrictive for the concurrency requirements of your application, you can use byte-range locking instead of lock-all locking. With byte-range locking, you can specify the range of bytes to lock in the smart-large-object data. If other users access other portions of the data, they can still acquire their own byte-range lock.

Use the IfxLoLock() method in the IfxSmartBlob class to specify byte-range locking:

public long IfxLoLock(int lofd, long offset, int whence, long 
   range, int lockmode) throws SQLException

To unlock a range of bytes in the object, use the IfxLoUnLock() method:

public long IfxLoUnLock( int lofd, long offset, int whence, long 
   range) throws SQLException

The lofd parameter is the locator file descriptor returned by the IfxLoCreate() or IfxLoOpen() method. The offset parameter is an offset from the starting seek position. The whence parameter identifies the starting seek position. The values are described in the table in Positioning Within a Smart Large Object.

The range parameter indicates the number of bytes to lock or unlock within the smart large object. The lockmode parameter indicates what type of lock to create. The values can be either IfxSmartBlob.LO_EXCLUSIVE_MODE or IfxSmartBlob.LO_SHARED_MODE.