Managing Concurrency for 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.

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.

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.

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.

Select *
from employee
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.

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.