The behavior of the lock types

Informix® database servers store locks in an internal lock table. When the database server reads a row, it checks if the row or its associated page, table, or database is listed in the lock table. If it is in the lock table, the database server must also check the lock type. The lock table can contain the following types of locks.
Lock name Description Statement usually placing the lock
S Shared lock SELECT
X Exclusive lock INSERT, UPDATE, DELETE
U Update lock SELECT in an update cursor
B Byte lock Any statement that updates VARCHAR columns

In addition, the lock table might store intent locks. An intent lock can be an intent shared (IS), intent exclusive (IX), or intent shared exclusive (SIX). An intent lock is the lock the database server (lock manager) places on a higher granularity object when a lower granularity object needs to be locked. For example, when a user locks a row or page in Shared lock mode, the database server places an IS (intent shared) lock on the table to provide an instant check that no other user holds an X lock on the table. In this case, intent locks are placed on the table only and not on the row or page. Intent locks can be placed at the level of a row, page, or table only.

The user does not have direct control over intent locks; the lock manager internally manages all intent locks.

The following table shows what locks a user (or the database server) can place if another user (or the database server) holds a certain type of lock. For example, if one user holds an exclusive lock on an item, another user requesting any kind of lock (exclusive, update or shared) receives an error. In addition, the database server is unable to place any intent locks on an item if a user holds an exclusive lock on the item.
Hold X lock Hold U lock Hold S lock Hold IS lock Hold SIX lock Hold IX lock
Request X lock No No No No No No
Request U lock No No Yes Yes No No
Request S lock No Yes Yes Yes No No
Request IS lock No Yes Yes Yes Yes Yes
Request SIX lock No No No Yes No No
Request IX lock No No No Yes No Yes

For information about how locking affects performance, see your Informix® Performance Guide.