LOCKS

Contention for locks is one of the best known and understood forms of Oracle contention.   Some contention for locks is inevitable in even the most well tuned multi-user transactional application.  However, while some lock contention is inevitable, a lot of lock contention can and should be avoided.

Locks are an essential mechanism in any transactional multi-user database system:  the ACID (Atomic-Consistent-Independent-Durable) properties of a transaction can only be implemented by restricting simultaneous changes to the database.  This is achieved by placing locks on modified data.  These locks persist until the transaction ends – usually by issuing a COMMIT or ROLLBACK statement (although note that many DDL statements implicitly issue a COMMIT).  

Without locks, a change made by one transaction could be overwritten by another transaction that executes at the same time.  Consider, for example, the scenario shown in Figure 1. When two different sessions try to update the same account, we encounter some obvious difficulties if locks are not in place.

Figure 1 Transaction without locks

In this scenario, account number 2 starts with a balance of $2000.  Transaction A reduces the balance of the account by $100.  Before transaction A commits, transaction B increases the account value by $300.  Because transaction B cannot see the uncommitted updates made by transaction A, it increases the balance to $2,300.  Because we allowed two transactions to simultaneously modify the same row, the database is now in an inconsistent state.  The end balance for the account will be the value set by whichever transaction commits last.  If transaction B is the last to commit, then the owner of account #2 will have $100 more than she should.  On the other hand, if transaction A commits first, the account owner will be $300 out of pocket!

This clearly unacceptable result is completely avoidable when locks are placed on rows that have been changed, as is illustrated in Figure 2.

 Figure 2 Transaction with locks

Now, when transaction A updates account #2, the relevant row is locked and cannot be updated by another transaction.  Transaction B must wait for transaction A to be committed before its update can proceed.  When transaction A commits, transaction B applies its update to the modified account balance, and the integrity of the account balance is maintained.

Row level locking in Oracle

Oracle was the first commercial database server to implement an effective row level locking strategy.  Prior to row level locking, locks would be applied either at the table level or at the block/page level.  Without row level locking, transactions would experience lock contention even when updating different data and consequently lock contention would more often be the primary restriction on database throughput.   You could argue that Oracle’s row level locking enabled relational databases to be the basis for serious transaction processing applications.

Oracle’s row level locks are robust and generally reliable.  However, there are some well understood circumstances in which Oracle will still apply locks at the block or table level– as we will see. 

Types of locks

There’s a number of ways to categorize Oracle locks, and some of the distinctions can be unimportant and confusing.   I find the following categorizations effective:

• • Lock type:  A two letter code that indicates the type of lock.  Most application locks will receive the code ‘TX’.  From 10g onwards,  the V$LOCK_TYPE table lists all the codes together with a brief explanation of the meaning.

  •  

  • Lock wait type:  From 10g onwards, individual locks types are associated with one or more wait category.  Each of these wait types start with ‘enq:’ – the term enqueue referring to the action of placing a request for a lock.  For example there are four wait states that are associated with the TX lock.

  •  

  • Shared or exclusive: Shared locks will allow other sessions to place a shared lock on a resource, but prevent an exclusive lock.  Exclusive locks will block any request on the resource.

  •  

  • Implicit or explicit:  The LOCK TABLE statement and the FOR UPDATE clause are both explicit locking statements: One locks a table while the other locks one or more rows.  Implicit locks occur when DML (INSERT, UPDATE, DELETE) statements are executed.

Application locking strategies

If Oracle locking is working as intended, the nature and degree of Oracle lock contention will depend on the application design.  Application transaction design should generally aim to hold locks for the least possible amount of time, although you should almost never sacrifice transactional integrity to reduce concurrency.

One of the most significant design decisions that affect lock duration will be the decision between employing the optimistic locking strategy and the pessimistic locking strategy.

The pessimistic locking strategy

The pessimistic locking strategy is based on an assumption that it is possible that a row will be updated by another user between the time you fetch it and the time you update it. To avoid any contention, the pessimistic locking strategy requires that you lock the rows as they are retrieved. The application is therefore assured that no changes will be made to the row between the time the row is retrieved and the time it is updated.

The optimistic locking strategy

The optimistic locking strategy is based on the assumption that it is very unlikely that an update will be applied to a row between the time it is retrieved and the time it is modified. Based on this assumption, the optimistic locking strategy does not require that the row be locked when fetched. However, to avoid the possibility that the row will be updated between retrieval and modification, it will be necessary to check that the row has not been changed by another session when being modified or locked. This can be done by checking a time-stamp value, or by checking that the original selection criteria still applies. If it is detected that the row has been modified, then it will be necessary to either re-try the transaction or return an error to the user.

The optimistic and pessimistic locking strategies are diagrammed in Figure 3. Each strategy has its strengths and weaknesses and the choice of strategy can affect the performance of your application. Consider the following points when deciding upon an appropriate locking strategy:

• • The optimistic locking strategy tends to hold locks for shorter periods of time, thus reducing the potential for lock contention.

  •  

  • In an interactive application, the pessimistic locking strategy can allow locks to be held indefinitely. This is a common phenomenon in an interactive application which fetches and locks data pending and waits for the user to hit the “OK” button. It’s quite possible for the row to remain locked for hours if the user “goes to lunch”—not realizing that a lock has been placed on the row displayed.

  •  

  • If the optimism in the optimistic locking strategy is misplaced—if it is reasonably common for rows to be updated by other sessions between retrieval and update—then the optimistic locking strategy can lead to poor batch performance or user frustration as a high proportion of updates are rejected with a “try again” message. 

 Figure 3 The optimistic and pessimistic locking strategies

When row level locking breaks down

Lock contention can arise from normal application activity, but my experience has been that the most significant lock contention issues arise when Oracle’s row level locking scheme fails.   There are a couple of circumstances that can cause an SQL against a single row to lock more than just that row or even an entire table.  The most common circumstances are:

• • Unindexed foreign key constraints

  • Bitmap indexes

  • Full ITL (Interested Transaction Lists)

Unindexed foreign keys

Under the right – or wrong – circumstances, foreign key constraints that have no supporting index can result in table level locks being applied.  Specifically, if the primary key of a parent table is changed - including by DELETE - then the entire child table will be locked unless there is an index on the child table’s foreign key column.

Consider the following tables:

CREATE TABLE parenttable

   (parent_id NUMBER PRIMARY KEY ,

    parent_data VARCHAR(2000) );

CREATE TABLE childtable

 (child_id NUMBER,

  parent_id NUMBER,  

  child_data VARCHAR(2000) ,

  PRIMARY KEY (child_id) ,

  FOREIGN KEY (parent_id) REFERENCES parenttable(parent_id) );

ITL locks

Oracle’s row level locking implementation achieves a high degree of scalability by maintaining lists of row level locks in each data block rather than in some centralized data structure.   This list is called the Interested Transaction List or ITL.  The ITL approach works well, but occasionally the list in the block can fill up.  Normally this won’t happen unless there is a massive amount of concurrent demand for a small number of rows – in which case the full ITL will be the least of your problems.

However, you can accidentally create a situation in which the ITL has no room to grow by setting poor values for the parameters INITRANS, MAXTRANS or PCTFREE.  INITRANS controls the number of  entries that will be available in each block when first created.  PCTFREE keeps some room in the block free for rows to grow when UPDATEd but also for the ITL to grow.  If PCTFREE is very low (which you might do to get very high row densities) then the ITL list might be unable to grow with increasing transactions values.  Of course, setting a very low value for MAXTRANS will have the same effect.

These issues are uniquely identified (from 10g onwards) by the “allocate ITL entry” wait on the TX enqueue.  Unfortunately, the only practical solution at this point is to rebuild the table. 

Bitmap indexes

For the right type of query – typically OLAP or data warehousing queries - Bitmap indexes can provide far better results than a traditional B-Tree index.   However, bitmap indexes have significant locking implications that usually render them unsuitable for OLTP applications.  

If a bitmap index exists on a column being updated, then Oracle will lock all the other rows in the same “bitmap index fragment”.  Since bitmap entries are very small, this can result in a lot of rows being locked. So row level locking might be ineffective for tables with bitmap indexes,  although a bitmap index might be OK for columns that are rarely updated.

Bitmap index lock contention will appear to be row level lock contention except that the object being waited for will be the relevant bit map index.   Figure 9 shows the phenomenon: the lock appears to be a row level lock but the object required is the index, not the table.

Internal locks

Most of the time, you will encounter locks on application objects – row, tables and occasionally indexes.   However, Oracle itself must apply locks when performing internal operations and sometimes sessions can contend for these locks as well.

Space transaction locks

In “dictionary managed” tablespaces (created with the EXETENT ALLOCATION DICTIONARY clause), sessions must obtain the Space Transaction (ST) lock whenever a new extent is allocated.  If sessions are performing activities that result in rapid extent allocation contention can occur.

The most common cause of this phenomenon is where a table – possibly a work table – is being filled with data and then truncated.  If the NEXT extent values specified for the table a small, then extents may be allocated and discarded at a rapid rate.  GLOBAL TEMPORARY tables are a better solution for these sorts of work tables since they avoid the ST lock altogether.

You won’t see this very often in 10g or 11g, since by default all tablespaces are locally managed.  However, if you’ve upgraded a database from an earlier release you may still have dictionary managed tablespaces and consequently might still be vulnerable to this type of lock contention.

Space Transaction waits are reported by the “enq: ST – contention” wait as shown in Figure 10.

Row Cache lock

The “row cache” is an area in Oracle’s shared pool that keeps data dictionary information in memory.  The “row cache” lock prevents two sessions from updating the same information in the row cache simultaneously.

Most of the time, row cache updates are infrequent since data dictionary information is fairly static.  However, if row cache information is being updated frequently then contention for the row cache lock can occur.

One scenario that can cause row cache contention is rapid sequence number generation from a sequence with an insufficient CACHE value.  By default, sequences only cache 20 numbers.  Every time the cache is exhausted, Oracle needs to update the row cache to get the next set of numbers.  If sequence numbers are being allocated very rapidly then row cache lock waits might become serious. 

Conclusion

Oracle provides a highly efficient row level locking capability that maximizes transaction concurrency.  Most of the time, lock contention is an inevitable consequence of application locking strategies.  Each application is different, but the most significant and widely applicatable design pattern you can employ to minimize locking is the optimistic locking strategy.

Certain scenarios can require that Oracle apply locks above the row level of granularity.  These include:

• • Unindexed foreign keys

  • Bitmap indexes

  • Inability to grow the Interested Transaction List (ITL)

Oracle sometimes applies internal locks and – in some circumstances – these can cause contention at the application level.  Two examples of problematic internal locking are:

• • Space Transaction (ST) locks in dictionary managed tablespaces

  • Row cache locks caused by sequences with low CACHE settings.