Lock Compatibility

A transaction can acquire a lock on an object that is already locked only if the existing lock is compatible with the lock the transaction is trying to acquire.

In the lock compatibility matrices in the following two tables, table locks are exclusive (TX) or shared (TS), record locks are exclusive (RX) or shared (RS). In addition, IX and IS table locks are set by the system automatically when RX and RS locks are placed on records of that table.

  TX TS IX IS
TX No No No No
TS No Yes No Yes
IX No No Yes Yes
IS No Yes Yes Yes
  RX RS
RX No No
RS No Yes

For example, assume two transactions, a and b, want to access the part table. When transaction a reads a record in the part table, the system must acquire an RS lock on the record, and implicitly acquires an IS lock on the part table. If transaction b tries to read the same record, there is no conflict, so transaction b will be granted an RS lock on the record and an IS lock on the part table. If transaction b tries to update the current record, a conflict occurs because the RS lock held by transaction a, and the RX lock required by transaction b are incompatible.

The deadlock avoidance mechanism takes one of the following actions:

However, if transaction b tries to update a different record in the part table, the system will acquire an RX lock on the record, and an IX lock on the table, and transaction b can be completed successfully. If transaction a had explicitly requested a TS lock on the part table, transaction b would not be allowed to update any record in the table, since both TX and IX locks are incompatible with a TS lock.