Spotlight on Oracle 11.0 - Getting Started Guide

Reduce Lock Contention

Lock or Enqueue waits occur when a session waits to obtain a lock. In most cases, this occurs because of a lock on a table or row that the session wants to lock or modify. In some circumstances, the lock involved may be an Oracle internal lock (for instance, the Space Transaction enqueue). If the database is well tuned and the application design sound, enqueue waits should be negligible. Common causes of excessive enqueue waits are:

• Contention for a specific row in the database. The application design may require that many processes update or lock the same row in the database. One common example of this is when primary keys are generated using a sequence table.
• Table locks caused by foreign keys that have not been indexed. If a non-indexed foreign key is updated, then the parent table is subjected to a table lock until the transaction is complete.
• Old-style temporary tablespaces. If the tablespace named as the temporary tablespace has not been created with the TEMPORARY clause, sessions may contend for the space transaction lock.
• The space reserved for transactions within a data block is too small. By default, only one transaction slot for tables or two for indexes is allocated when the table or index is created. The number of transaction slots is determined by the INITRANS clause in the CREATE TABLE or INDEX statement. If additional transaction slots are required, they are created, providing there is free space in the block. However, if all transaction slots are in use (and there is no free space in the block), a session that needs to lock a row in the block encounters an enqueue wait. This occurs even if the row in question is not actually locked by another process. This can occur if both PCTFREE and INITRANS were set too low.