In object-oriented databases, locking can take place at the database level, the “container” within the database, or the object within the container. The concept of a container is new to the object-oriented databases. A container is defined as a partition of disk memory of arbitrary size that is used to hold objects. A container can be thought of as analogous to the “pages” that are used within CA-IDMS and DB2. In commercial object-oriented databases, most vendors only support locking at the container level, although most of the vendors recognize the necessity of providing object level locking mechanisms. As a general rule, as the locking level becomes finer, the demands on resources on the lock manager increase while the potential for database deadlocks decreases.

In some databases, the programmer has some control over whether a database lock is issued. In CODASYL databases such as CA-IDMS, the programmer may issue a GET EXCLUSIVE command to expressly hold a record lock for the duration of the transaction, and some relational databases allow for locks to be controlled with the SQL. Most relational databases offer commands that can allow an application to hold shared or exclusive locks on database rows.

Most commercial relational databases such as Oracle offer two types of locks: shared and exclusive. The most common type of locks are shared locks that are issued with SQL SELECT statements, and exclusive locks that are issued with DELETE and UPDATE statements. In shared locking, whenever a unit of data is retrieved from the database, an entry is placed in the database storage pool. This entry records the unit ID (usually a row or database page number). The usual size of a lock ranges from 4 to 16 bytes, depending on the database. This lock will be held by the database until a COMMIT, END, or ABORT message releases the lock. Most locking schemes use a “coexistence” method. For example, many clients may have shared locks against the same resource, but shared locks cannot coexist with exclusive locks. Whenever an update event occurs, the database attempts to post an exclusive lock against the target row. The exclusive lock will wait if any other tasks hold a shared lock against the target row (Figure 5.3).

Figure 5.3  Exclusive vs. shared locks.

Oracle Locking

Oracle maintains locks at either the row level or the table level. Unlike other databases, such as DB2, Oracle will never “escalate” locks to the table level if the database detects that a majority of the rows in a table are being locked. Consequently, the Oracle programmer must decide in advance whether to lock the entire table or allow each row of the table to be locked individually.

Two init.ora parameters control locking: serializable=false and row_locking=always. These default values should never be changed except in very rare cases.

Oracle supports two types of locks: row locks and table locks. These locks can be subdivided into several categories:

•  Row Share Table Locks (RS)—These locks are issued when an SQL transaction has declared its intent to update the table in row share mode. This type of lock will allow other queries to update rows in the customer table. For example:

lock table customer in row share mode;

SELECT customer_name
FROM CUSTOMER
FOR UPDATE OF CUSTOMER;

•  Row Exclusive Table Locks (RX)—These locks are issued automatically against a table when an UPDATE, DELETE, or INSERT statement is issued against the table.

•  Table Share Locks (S)—This type of lock is issued when the LOCK TABLE command is issued against the table. This indicates that the transaction intends to perform updates against some rows in the table, and prevents any other tasks from execution until the LOCK TABLE xxx IN SHARE MODE has completed.

•  Share Row Exclusive Table Locks (SRX)—These locks are issued with the LOCK TABLE xxx IN SHARE ROW EXCLUSIVE MODE command. This prevents any other tasks from issuing any explicit LOCK TABLE commands until the task has completed, and also prevents any row-level locking on the target table.

•  Exclusive Table Locks (X)—This is the most restrictive of the table locks and prevents everything except queries against the affected table. Exclusive locks are used when the programmer desired exclusive control over a set of rows until their operation has completed. The following command is used to lock the CUSTOMER table for the duration of the task:

LOCK TABLE CUSTOMER IN ROW EXCLUSIVE MODE NOWAIT;

In order to understand how the different types of locks interact with one another, we can reference Table 5.1, which describes how each lock type interacts with other locks. The NO values indicate those locks that cannot be simultaneously held.

Database Deadlocks

This locking scenario insures that all database integrity is maintained and that updates do not inadvertently overlay prior updates to the database. However, a penalty has to be paid for maintaining shared locks. In Oracle, each lock requires 4 bytes of RAM storage within the Oracle instance storage pool, and large SQL SELECT statements can create S.O.S. (Short On Storage) conditions that can cripple the entire database. For example, a SELECT statement that retrieves 1,000 rows into the buffer will require 4,000 bytes of lock space. This condition can also cause the “deadly embrace,” or a database deadlock. A deadlock condition occurs when two tasks are waiting on resources that the other task has locked, as depicted in Figure 5.4.

Figure 5.4  A database deadlock.

The majority of Oracle programmers do not realize that database deadlocks occur most commonly within a table index. It is important to note that a SELECT of a single row from the database may cause more than one lock entry to be placed in the storage pool. The individual row receives a lock, but each index node that contains the value for that row will also have locks assigned.