Lesson 10	Alternative concurrency mechanisms for Web applications
Objective	Implement alternative locking for a Web application

Date Time Stamp

Update each row with date-time stamp

The second tuning technique that you might use to replace the Oracle locking scheme requires that a date-time stamp column be added to each table that may be updated. The following SQL is to append a date-time stamp to every updated row.

Update employee
Set salary = salary*1.1
WHERE
Emp_name = "Gould"
AND
Date_last_updated = :host_date_value;

This method is described in the following series of images below.

Date-time stamp

2) In our example, we assume that a column called date_last_updated has been added to the employee table.

3) We need only check the value of this column when we retrieved it, with the existing value.

4) Of course, since the row has changed, the date-time stamps will not match, and the transaction will fail with a not found SQL code.

When issuing any update SQL, all applications are required to select the date-time stamp column and include it in the WHERE clause.If the row has not changed, the update will be successful.
In our example, we assume that a column called date_last_updated has been added to the employee table.
We need only check the value of this column when we retrieved it, with the existing value.
Of course, since the row has changed, the date-time stamps will not match, and the transaction will fail with a not found SQL code.

It is important that the display data is not changed when executing alternative locking for a Web application. These techniques minimize the use of the SELECT FOR UPDATE to ensure that it is unaltered.

Alternative Locking Methods - Exercise

Click the Exercise link below to apply these two locking method alternatives.
Alternative Locking Methods - Exercise The next lesson concludes this module.