User CRUD operations is very common during the HANA XS application development process.
And let's have a try and discovery on the concurrency control and the lock mechanism design.
1.Assumed use case.
In the assumed scenario:
The database table has 4 fields, DATE,PERSON_ID,NAME and PERSON_INFO.
DATE and PERSON_ID make the primary key.
Here is two pages in the application:
The page shows the content exposed from HANA database.
And you could click on the "Edit Content" button in the head of the table.
The designed scenario is "Multiple users cannot edit the same record at the same time period".
For example, while user1 is editing content of PersonID 1001 on DATE '2015-02-09', user2 cannot edit PersonID 1001 on '2015-02-09' but user2 is allowed to edit PersonID 1001 on '2015-02-10' or PersonID 1002 on '2015-02-09'.
If no confilcts happens, user would be directed to this page, then do the save or cancel operations.
If someone is editing, the page would give an alert:
2.Business requirement analysis
Here I draw a very simple business requirment process flow diagram for better understanding.
There is some point need to be noticed:
(1) When user click on the "edit" button, the application should decide whether it should be directed to the next page or alert a notice that other user is editing the same record.
(2) For situation when user forget to terminal the session, the system should have a default time-out mechnism and release the lock.
3.technical design
I came up an idea which is writing all the users' opertions down and maintaining the opertion log in a trasaction table.
When user click on the "edit" button, the service would check the transaction log first and then decide whether the user could enter the next page.
Lock Table design:
This table has four fields, firstly it should contain the primary key as the database table has.
Then the LOCK_TIMESTAMP is used for tracking the time when the click event session starts.
The SESSSION_USER is used for taking notes of the operator of this session.
Beacuse this table is used for OLTP scenarios and it always to be fetched or looked up by record, so row store is a better choice.
Here is the enhanced process flow:
Whenever the user click on the "Edit" button, the service would first look up the transaction table:
Three situations would cover:
(1) If no log exist for this record, then add a new log for this record. Return yes to the user and let him/her view the next page.
(2) If log exist, check the difference of the timestamp:
(a) Timestamp difference is larger than the value we set, overwrite the log with current timestamp, return yes.
(b) Timestamp difference is smaller than the value we set, keep the log same, return no and the session_user value, then alert a message to the user and tell him/her someone is editing the records.
4. implement and show the code
Let's have a look at the code:
I use XSJS service to implement this:
function EditRecord(){
var PersonID= $.request.parameters.get('PersonID'); //Get the PersonID which user inputs
| var Date= $.request.parameters.get('Date'); | //Get the Date which user inputs |
var diffTime; //Store the value for differnce between current timestamp and the timestamp value in the transaction table
var username; //Store the lock user name in the transaction table
var conn=$.db.getConnection();
var pstmt;
var query ="select seconds_between(time_lock,CURRENT_TIMESTAMP),DATE,SESSION_USER from \"XXX\".\"LOG_FOR_LOCK\" where PERSON_ID= ?";
pstmt=conn.prepareStatement(query);
pstmt.setString(1,PersonID)
var rs=pstmt.executeQuery();
while(rs.next()){
diffTime=rs.getInteger(1);
LockTableDate=rs.getDate(2);
username=rs.getString(3);
if(Date.getTime()-LockTableTime.getTime() ==0 && diffTime<1200 && username!==$.session.getUsername())
{$.response.setBody(-1);return -1;}
}
// We check whether the DATE are the same by using the getTime() function. And I set the time-out value to be 1200 seconds.
// Also, here we allow the same user to open multiple sessions. Otherwise, it is possible to let the user be locked by himself.
// if the function does not end, which means whether there is no log for the records or the log is out-dated.
// So we should insert or update the log information with the current session
var query1="upsert \"XXX\".\"LOG_FOR_LOCK\" values('"+ Date + "','" + PERSON_ID +
"',now(),'"+ $.session.getUsername()+ "') where DATE= " + DATE + " and PERSON_ID= " + PersonID ;
pstmt=conn.prepareStatement(query1);
pstmt.execute();
pstmt.close();
conn.commit();
conn.close();
$.response.setBody(1);
return 1;
}
// When the application page call this service, it could decide whether jump to the next page according to the return value.
// I wrote this code just for test use, no gurantee for 100% secure or accurate.
5. Summary & FAQ
Absolutely, there is no best solution for every scenario.
This lock mechanism is designed for only some kind of situation and it may contain some defects.
I just implement for a test use case and the code I wrote may contain some flaws and it is not an official recommanded mechism.
Different methods would effects according to different design.
FAQ:
1. How about orphan lock situation?
The service would check the timestamp between current timestamp and the timestamp stored in the log_for_lock table.
2. What is the most siginificant difference between this application lock and database concurrency controll.
As my understanding, this application is meant to detect confilct when user click on the "edlt" button.
And database concurrenct controll is a lock mechism effect when comit action happens.
For other methods, please refer to below links for detailed information:
1.eTag:
http://www.odata.org/documentation/odata-version-2-0/operations/#ConcurrencycontrolandETags
2. Optimistic Lock:
Just Google it and it would bring many good results.
3. HANA MVCC controll:
Special thanks for help from Thomas, Neil, Yuan and Antonie.
Please feel free to point out if there is any other flaw in this design and have discusion with me. :smile:
Thanks for reading.
Have a nice day.
Kevin
- SAP Managed Tags:
2 Comments
