 Library |
 Product |
|
Locking is the mechanism by which Oracle Rdb controls concurrency and enforces the logical and physical integrity of the database. Locks are used to:
Your program does not explicitly control locking. Rather, you specify your needs for protection and access. Oracle Rdb then determines what locking scheme meets your needs while still allowing the most user concurrency and protecting the database's integrity. The actual locks Oracle Rdb acquires might not be what you expect or even what you request.
The following sections describe locking for read/write transactions. A
read-only transaction does not lock tables and rows unless snapshots
are disabled. If snapshots are disabled, a read-only transaction
behaves the same as a read/write transaction. Section 16.5.4 explains
read-only transactions and snapshot files.
16.5.1 Locking Strategies
Oracle Rdb uses a simple strategy for locking objects:
In general, Oracle Rdb tries to lock at the highest level possible.
For instance, if you select one row from a 500,000-row table, and no
other transaction is using that table, Oracle Rdb might choose to
place the lock on the entire table. Thus when you select other rows, no
new locks need to be acquired. Only if another transaction requested
compatible access to the table would smaller portions of the
table---index nodes or individual rows---be locked. This is called
adjustable lock granularity.
16.5.2 Intent Locks
Whether you start a default transaction or you explicitly reserve tables for a particular access and share mode, Oracle Rdb takes out locks on a table-by-table basis. In a transaction without a RESERVING clause, Oracle Rdb does not lock the tables until an SQL statement accesses them; at that time, Oracle Rdb chooses an appropriate lock based on the operation being performed. When you explicitly reserve tables, Oracle Rdb locks each table at the start of the transaction, placing an intent lock on the table.
Table 16-2 shows the intent lock modes, their meaning, and the allowed activities.
| Lock type | Meaning | Allowed Activities |
|---|---|---|
| CR | Concurrent Read | The current transaction explicitly locks every record it reads. Other transactions may read from and write to the same table. |
| CW | Concurrent Write | The current transaction explicitly locks every record it reads or writes. Other transactions may read from and write to the same table. |
| PR | Protected Read | The current transaction does not need to lock any records it reads. Other transactions may read from, but not write to, the same table. |
| PW | Protected Write | The current transaction does not need to lock any records it reads, but it locks every record it modifies or adds. Other transactions may read in CR mode. |
| EX | Exclusive | The current transaction does not need to lock any record it reads or modifies because no other access to the table is allowed. Other transactions must wait until this transaction finishes before they can have any access to the table. |
By default, each transaction holds all locks until the transaction ends. Other transactions cannot change the rows you have locked. In many circumstances, they cannot even read the same rows. Further, depending on how the table is structured and how Oracle Rdb processes your query, it is possible to lock records your transaction does not directly request. For instance, if you request a row that must be located by going through the table one row at a time, you lock the entire table one row at a time. Your transaction may also lock tables to which constraints and triggers refer. Again, these locks are held until your transaction ends. Other transactions cannot use any records you have locked in a manner that is inconsistent with the usage you have requested.
Similarly, if another transaction is already using any of the rows, tables, or indexes that you need, you have to wait for the resource to be released or else handle the lock-conflict error. (Section 16.5.3 explains lock conflicts. Section 10.5 tells how to handle lock conflict and deadlock situations in your program.)
If you cannot gain the access you requested to all the tables you want to reserve, your transaction will not start.
The kind of lock (read or write) placed on a row is determined by the nature of the statement that is processing the row. An OPEN statement, for example, places only read locks on the rows in a cursor, even if you specify the write lock type for the table in a RESERVING clause. You must fetch a row from the opened cursor and then execute an UPDATE or DELETE statement on the row before Oracle Rdb upgrades the read lock to a write lock.
When you reserve tables using the protected or exclusive mode, you minimize the time it takes for your transaction to complete because you reduce the availability of data to other users. The exclusive share mode, for example, does not allow other users to access a table. If a table is not available to other users, you prevent someone else's transaction from locking rows you plan to process.
Deciding if you want to specify share modes and lock types is important
only when you start a read/write transaction. The decision is not
important for either read-only or batch-update transactions.
16.5.3 Lock Conflicts
When you lock a row, you may or may not conflict with other users, as shown in Figure 16-5. When a conflict occurs, if the second transaction has specified the NOWAIT option, it receives an error immediately. If the second transaction has specified the WAIT option, it waits until one of the following occurs:
Figure 16-5 shows when other users wait for row locks to be released and when they encounter a lock-conflict error. Assume that the wait characteristic (the default) applies to other users' transactions. If the no-wait characteristic applies, "a conflict" would appear wherever the chart indicates "a wait." (Note that the table does not indicate when users encounter deadlock errors. Unlike lock-conflict errors, deadlock errors are not easily predicted.)
Remember that other users must wait until you end your transaction for locks to be released. A read/write transaction with shared read access to a table places read locks on all rows that it processes. This may mean that all the rows in a table are locked if you are sequentially searching rows in the table rather than accessing rows by unique index values. Because other users cannot update rows on which your transaction has read locks, you may prevent users from updating any row in the table until your transaction ends.
Figure 16-5 Chart of Database Access Conflicts
In all update cases, Oracle Rdb does not allow other read/write transactions to read changed rows until the updating transaction commits or rolls back the transaction. Because Oracle Rdb locks your rows against access by other transactions, you can display the changes you have made to those rows. This row locking assures the consistency and integrity of database rows.
To improve concurrent access to tables, it is particularly important to be as specific as possible when you specify rows in cursor declarations. As soon as it opens a cursor, your transaction places read locks on all rows in the table associated with the cursor and (if you do not access tables by index) other rows as well. In a read/write transaction, if you open a cursor that accesses a large number of rows, you lock out other users who wish to update the database until you commit or roll back the transaction. For example, if you write an application that starts a transaction, opens a cursor, lets a data entry clerk examine each row and update or delete the row, and commits all the changes at once, your application will probably stop operations, even retrieval tasks, of other users who start read/write transactions after you do. As a general rule, cursors opened for an interactive update should be limited to one or a few rows and should be closed as quickly as possible with a COMMIT or ROLLBACK statement. (Executing a CLOSE statement does not release locks.)
Keep in mind that Oracle Rdb may lock a table for exclusive access
even if you request shared access, if it is necessary to protect
database integrity and consistency. This occurs during certain data
definition statements and during other statements as needed.
16.5.4 Read-Only Transactions and the Snapshot File
When snapshots are enabled for a database (the default), read-only transactions do not lock the rows they read. Rather, when an update occurs, Oracle Rdb writes the previous version of rows, the before-images, to the snapshot file. The read-only transaction reads the before-images directly from the snapshot file.
If a row is updated multiple times, multiple before-images are written to the snapshot file. All before-images for a row are retained long enough to guarantee that other transactions can read the version of the record that was current when the update transaction started.
Read-only transactions that access an Oracle Rdb database with the snapshot file disabled, however, do place read locks on rows processed during the transaction. When there is no snapshot file (and you are not accessing a read-only storage area), read-only transactions are processed as read/write transactions.
Read-only storage areas do not have snapshot files because you cannot
update data in read-only storage areas.
16.5.5 Encountering Lock-Conflict Errors with Read-Only Transactions
Transactions that reserve tables in the exclusive share mode update the recovery-unit journal file but not the snapshot file. Eliminating the chore of updating a snapshot file improves the performance of a read/write transaction by reducing the amount of disk I/O operations associated with an update. However, because read-only transactions depend on snapshot file update, a transaction that reserves tables in the exclusive share mode disables all read-only transactions that attempt to access the same tables. If your transaction is a high-priority transaction and you want it to encounter the least interference possible from other read/write transactions, but do not want to interfere with read-only transactions, reserve tables for protected write.
Read-only transactions that access a snapshot file (and normally should not encounter locks at all) sometimes encounter lock-conflict errors. This happens when a read-only transaction tries to access a table that has been reserved by a read/write transaction using the exclusive write option, or when any table in the database is being accessed by a batch-update transaction. (Batch-update transactions reserve all tables in the database using the exclusive write option.) When a read-only transaction encounters either kind of conflict, the read-only transaction is always treated as though it had the no-wait characteristic and it encounters a lock-conflict error.
The following steps illustrate the conflict between read-only transactions and read/write transactions that reserve a table in exclusive-write mode:
A transaction that specifies EXCLUSIVE WRITE does not write data to the
snapshot file. However, all data committed to the database before a
read-only transaction starts must be available to that read-only
transaction. Oracle Rdb cannot determine whether a transaction in
exclusive share mode has written data to the snapshot file; therefore,
the read-only transaction's requirements can never be satisfied. In
this case, the WAIT option specified by User B is rejected; thus, User
B receives an error rather than waiting forever.
16.5.6 Improving Concurrent Access
Indexes that contain unique values and well-designed indexes that contain duplicate values almost always improve multiuser access to single tables when the following conditions apply:
To improve concurrent access to a table, use a unique index to search the table, instead of a sequential (nonindexed) search. Nonindexed searches process rows from the beginning of the table to the end and place read locks on every table row. When indexes are unique, a user searching a single table by one index value is unlikely to lock out users who need to use the index to search for other values. When you retrieve rows by unique index value, you are most likely to place read locks only on the table rows you need.
However, indexes are database structures whose entry points (nodes) can be locked by user access just as rows in tables can be locked by user access. Therefore, your transaction may encounter a lock-conflict error or may have to wait for another transaction to end because of a lock on the node for a sorted index. Such a lock may be part of the reason why a user encounters a deadlock error.
Sorted indexes are most likely to cause such conflicts. The unique index values in columns that hold ID numbers, timestamps, order numbers, and so forth frequently increase by 1, and often the most recent rows are the rows most in demand. The likelihood that several needed records will appear in the same index node is quite high. Hashed indexes are less likely to cause contention because they are distributed more evenly through the database. However, hashed indexes can be defined only for multifile databases. Partitioning indexes so that different applications can access a different set of nodes may also help.
Searching tables using sorted indexes that allow duplicates can lock out other users as effectively as a sequential search of the table. The problem is related to the way a sorted index is structured, and arises when there are few index values (for example, only three department code values in an index based on department code). In this case, the structure for the sorted index has so few nodes that it can be completely locked by only one user (who is searching for only one index value).
To avoid this problem, create a sorted index that has fewer duplicates---perhaps basing it on more than one column. For example, if there are few department code values, a sorted index based on both department code and supervisor identification number (ID) increases the number of values in the sorted index and thereby increases the number of nodes for the index. Another solution is to create a hashed index in addition to a sorted index. Keep in mind that Oracle Rdb chooses which index to use depending on whether your query is a range retrieval or an exact match retrieval.
It is important to emphasize that you cannot predict whether a table will be searched sequentially or by index, especially when the query involves a join operation or accesses a view based on multiple tables. For these cases, the Oracle Rdb query optimizer decides which table is searched first and whether the search is done sequentially, by index, or (if both hashed and sorted indexes are available) by a particular index. The query optimizer makes this decision based on a variety of factors, among them the relative size of the tables being joined.
If the query optimizer determines that a join operation in your transaction can be executed most quickly by searching a particular table first, and that it must look at all the rows in that table, your transaction places read locks on all the rows in that table. Other users will not be able to update any rows in that table until your transaction ends. Conversely, your transaction may encounter a lock-conflict error or delays if another transaction has already obtained a conflicting lock on the table.
For example, to evaluate the following query, the query optimizer may read every row in one table to find a starting set of values for which the other tables can be checked. The query optimizer may decide that using the EMPLOYEES table for the starting set of values is fastest because EMPLOYEES is the smallest table. Conversely, the query optimizer may decide that overall query performance is best served by first searching the larger tables to eliminate rows with end-date values that are not null. In any event, you can be reasonably sure that the following query will place read locks on every row of at least one table:
SELECT LAST_NAME, FIRST_NAME, JOB_CODE, JOB_START, SALARY_AMOUNT
FROM EMPLOYEES E, JOB_HISTORY JH, SALARY_HISTORY SH
WHERE (
(JH.EMPLOYEE_ID = E.EMPLOYEE_ID)
AND
(SH.EMPLOYEE_ID = JH.EMPLOYEE_ID)
)
AND (JOB_END IS NULL)
AND (SALARY_END IS NULL);
Thus, your application has only indirect control over what locks will be placed on which objects. Accessing all tables by index values is still the best policy. By joining tables on columns that are indexed for all tables, you allow the query optimizer to choose among access methods rather than forcing it to perform a sequential search of a particular table.
The Oracle Rdb7 Guide to Database Design and Definition provides more information about indexes. The Oracle Rdb7 Guide to Database Performance and Tuning tells you how to locate and debug locking problems and explains the query optimizer and its potential strategies in detail and offers suggestions for dealing with problems.
|
Copyright ©1996, Oracle Corporation All rights reserved.