RelationalDBDesignRelationalDBDesign 


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

Date Time Stamp

Update each row with a 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 Slide Show below.
Date Time Stamp 1
1) 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.
Date Time Stamp 2
2) In our example, we assume that a column called date_last_updated has been added to the employee table.
Date Time Stamp 3
3) We need only check the value of this column when we retrieved it, with the existing value.
Date Time Stamp 4
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.

Date -Time Stamp
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 wraps up the module.