Concurrency management for Oracle Web Applications
Concurrency is the capability to perform many functions at the same time. Oracle provides for concurrency by allowing many end-users to access the database simultaneously. A method must make certain that each update transaction does not overlay the updates
of the other end-users. This method is generally called concurrency management, and it is a very important topic for Oracle Web applications.
Oracle prevents lock contention by holding
locks for the duration of all SQL UPDATE and INSERT transactions, as well as for pages that are locked so that the current values do not change before an update (SELECT FOR UPDATE). For in-house transactions over secure networks, locks are very effective. However, when using the Web as a front-end it is common to see connections terminated.
Lock contentions arise when one transaction attempts to read or write a piece of data, a field, row, table, or schema, that has been locked, from reading, writing, or both by another transaction process. Lock contentions can often cause long return times, and are best diagnosed with the use of log files.
General Concurrency and Performance
As discussed in Chapter 8, Oracle has excellent support for concurrency and performance
in OLTP systems. Some of the key features relevant to OLTP are as follows:
Nonescalating row-level locking
Oracle locks only the rows a transaction works on and never escalates these locks to page-level or table-level locks. In some databases, which escalate row locks to page locks when enough rows have been locked on a page, contention can result
from false lock contention when users want to work on unlocked rows but contend for locks that have escalated to higher granularity levels.
Lock pool Resources Database Deadlocks
Terminated connections can lead to serious performance problems. When a Web client disconnects from Oracle while holding row locks, the locks may remain in the shared pool from Oracle, waiting for the task to resume.
This leads to two problems:
The shared pool memory becomes clogged with zombie row locks.
Access to Oracle rows is blocked by unnecessary locks.
Locking in existing values
There are cases where your users rely on the existing values of displayed data. In this case, the Web application must lock the display values during the transaction.
Explicit locks in Closed/ Open Networks
In traditional client-server systems, a programmer can use the select...for update clause to explicitly lock a row, or a set of rows, prior to issuing the update operation. This will cause the database to issue exclusive locks at the time of retrieval and hold these exclusive locks until the task has committed or ended. In the following SQL code, an exclusive lock is placed on the target row, and no other tasks can retrieve that row until the update operation has completed.
where emp_name = "Gould"
for update of salary;
While this works well in a closed network, lock pool resources and database deadlocks can wreak havoc for Web applications. When a Web client becomes disconnected, the locks may be held indefinitely within the Oracle database.
Alternatives to shared and exclusive locks
The problems of lock pool resources and database deadlocks have lead to some creative alternatives to shared and exclusive locks.
Locking can be turned off in any database by issuing a commit statement immediately after the select statement.
Without long-term shared locks, lock pool utilization is reduced, and the potential for database deadlocks is eliminated.
The next lesson looks at how to implement tuning techniques to replace the Oracle locking scheme.