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 Slide Show below.
- 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.
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