RelationalDBDesignRelationalDBDesign 


Web Applications   «Prev  Next»
Lesson 9Alternative concurrency mechanisms for Web applications
ObjectiveImplement alternative locking for a Web application

Tuning techniques to replace the Oracle locking scheme

There are several reasons, described in the previous lesson, to consider alternatives to shared and exclusive locks. There are two common tuning techniques that are used to replace the Oracle locking scheme. The first is to issue all updates with a WHERE clause.

Update with a WHERE clause

Let's look at an example of this in the Slide Show below.
Update where statement 1
1) Gould's row contains the performance_flag, salary and name column.
Update where statement 2
2) The UPDATE required is sent with a WHERE clause.
Update where statement 3
3) Each of the columns, are specified in the update command to ensure that they have not changed since the transaction began.
Update where statement 4
4) This re-validates all of the existing new values from the original display.
Update where statement 5
5) If any of these values have changed since the row was initially retrieved, Oracle willl reject the transaction, not perform the update, and will return a non-zero error status.
Update where statement 6
6) The database will return with a NOT FOUND SQL code.

Update Where Statement
Note: As a further enhancement, the WebServer application can interpret the NOT FOUND error code returned after this transaction, re-retrieve the updated record, and present it to the user with its new value.
The next lesson discusses date time stamp.