Byte-Range Locking

Rather than locking the entire smart large object, you can lock only a specific byte range of a smart large object. Byte-range locking is advantageous because it allows multiple users to update the same smart large object simultaneously, as long as they are updating different parts of it. Also, users can read a part of a smart large object while another user is updating or reading a different part of the same smart large object.

Figure 41 shows two locks placed on a single smart large object. The first lock is on bytes 2, 3, and 4. The second lock is on byte 6 alone.

Figure 41. Example of Byte-Range Locking

How the Database Server Manages Byte-Range Locks

The database server manages byte-range locks in the lock table in a similar fashion to other locks placed on rows, pages, and tables. However, the lock table must store the byte range as well.

If a user places a second lock on a byte range adjacent to a byte range that the user currently has locked, the database server consolidates the two locks into one lock on the entire range. If a user holds locks that Figure 41 shows, and the user requests a lock on byte five, the database server consolidates the locks placed on bytes two through six into one lock.

Likewise, if a user unlocks only a portion of the bytes included within a byte-range lock, the database server might be split into multiple byte-range locks. In Figure 41, the user could unlock byte three, which causes the database server to change the one lock on bytes two through four to one lock on byte two and one lock on byte four.

Using Byte-Range Locks

By default, the database server places a lock on the smart large object. To use byte-range locks, you must perform one of the following actions:

• To set byte-range locking for the sbspace that stores the smart large object, use the onspaces utility. The following example sets byte-range locking for the new sbspace:

  onspaces -c -S slo -g 2 -p /ix/9.2/liz/slo -o 0 -s 1000
     -Df LOCK_MODE=RANGE

  When you set the default locking mode for the sbspace to byte-range locking, the database server locks only the necessary bytes when it updates any smart large objects stored in the sbspace.

• To set byte-range locking for the smart large object when you open it, use one of the following methods:
  • Set the MI_LO_LOCKRANGE flag in the mi_lo_open() DataBlade API function.
  • Set the LO_LOCKRANGE flag in the ifx_lo_open() ESQL/C function.

    When you set byte-range locking for the individual smart large object, the database server implicitly locks only the necessary bytes when it selects or updates the smart large object.

• To lock a byte range explicitly, use one of the following functions:
  • mi_lo_lock()
  • ifx_lo_lock()

    These functions lock the range of bytes that you specify for the smart large object. If you specify an exclusive lock with either function, UPDATE statements do not place locks on the smart large object if they update the locked bytes.

    The database server releases exclusive byte-range locks placed with mi_lo_lock() or ifx_lo_lock() at the end of the transaction. The database server releases shared byte-range locks placed with mi_lo_lock() or ifx_lo_lock() based on the same rules as locks placed with SELECT statements, depending upon the isolation level. You can also release shared byte-range locks with one of the following functions:

  • mi_lo_unlock()
  • ifx_lo_unlock()

    For more information about the DataBlade API functions, see the IBM Informix: DataBlade API Programmer's Guide. For more information about ESQL/C functions, see the IBM Informix: ESQL/C Programmer's Manual.

Monitoring Byte-Range Locks

Use onstat -k to list all byte-range locks. Use the onstat -K command to list byte-range locks and all waiters for byte-range locks. Figure 42 shows an excerpt from the output of onstat -k.

Byte-Range Locks
rowid/LOid   tblsnum  address  status   owner    offset   size     type
104          200004   a020e90  HDR
[2, 2, 3]             a020ee4  HOLD     a1b46d0  50       10           S 
202          200004   a021034  HDR
[2, 2, 5]             a021088  HOLD     a1b51e0  40       5            S 
102          200004   a035608  HDR
[2, 2, 1]             a0358fc  HOLD     a1b4148  0        500          S 
                      a035758  HOLD     a1b3638  300      100          S 
 21 active, 2000 total, 2048 hash buckets 

Byte-range locks produce the following information in the onstat -k output.

Column

Description

rowid

The rowid of the row that contains the locked smart large object

LOid

The three values: sbspace number, chunk number, and sequence number (a value that represents the position in the chunk)

tblsnum

The number of the tblspace that holds the smart large object

address

The lock address

status

Status of the lock

HDR is a placeholder. HOLD indicates the user specified in the owner column owns the lock. WAIT (shown only with onstat -K) indicates that the user specified in the owner column is waiting for the lock.

owner

The address of the owner (or waiter)

Cross reference this value with the address in onstat -u.

offset

The offset into the smart large object where the bytes are locked

size

The number of bytes locked, starting at the value in the offset column

type

S (shared lock) or X (exclusive)

Setting Number of Locks for Byte-Range Locking

When you use byte-range locking, the database server can use more locks because of the possibility of multiple locks on one smart large object. Monitor the number of locks used with onstat -k. Even though the lock table grows when it runs out of space, you might want to increase the LOCKS parameter to match lock usage so that the database server does not have to allocate more space dynamically.