I'am working on a typical workflow application . Records can be added ,Updated , sanctioned, Authorized,Deleted & viewed in the system. Business rules are :- 1)Only fresh & Sanctioned records can be modified 2)Only Sanctioned records can be Authorized 3)Only non Authorized records can be updated 4)Only non Authorized records can be Cancelled 5)Only non Authorized records can be Deleted. Now assuming i want to Delete a non Authorized record,I populate my transaction screen in Delete mode.The transaction screen's fields are populated from the database. I DO NOT HOLD ANY LOCKS ON THE RECORD ! CAN THIS BE DONE USING JDBC ALONE / OR BY ANY MODIFICATIONS TO THE QUERY WHICH IS USED TO FETCH THE VALUES TO POPULATE THE TRANSACTION SCREEN ?? Presently what is happening is that before I Delete this record if another user Authorises the same record , my application logic goes for a toss. This is because at the time both the records were selected from the search screen , they were both fresh records & both Deletion and Authorization was possible .
How do i take care of this scenerio ? I know that i could take care in the Delete Query by using the not in Keyword & specify the required record status there .Also a time stamp approach (Optimistic Locking approach) could be used .
But is there a cleaner way to do this .... I repeat ..is there a way to lock a row using only JDBC.
subject: How to implement pessimistic locking using plain JDBC