Say I have a database which dosn't have record locking. I want to put in Record Locking through my program... how can i go about it.. For eg : If Two people read the same record - modify it and then try to update.. I would like to lock the record (not the DB) so that only one version is put at a time.. Can anybody suggest a strategy.... Thanx
Originally posted by Vivek Sharma: Say I have a database which dosn't have record locking. I want to put in Record Locking through my program... how can i go about it.. For eg : If Two people read the same record - modify it and then try to update.. I would like to lock the record (not the DB) so that only one version is put at a time.. Can anybody suggest a strategy.... Thanx
If you are getting into Java DB, as I am, there is a really good book that I found "JDBC API Tutorial and Reference, Second Edition", "Universal Data Access for the Java 2 Platform". It has a lot of information on all subjects, including what you are looking for. Hope this helps.
First off, I would recommend that you use a database that does have some form of record locking. The amount of extra DB access that will be necessary to implement this will significantly slow access down, especially if the DB is accessed over a network, and the solution will get quite complex. Is this a hypothetical question or do you really have such a DB ? Real RDBMS have two approaches to locking: by page (say 2K block of memory) or row (called Row Level Locking, whereby an individual record is locked). You will have to implement the latter with some extra flag field in all your tables. DB access should be abstracted as a matter of principle, this abtraction layer should implement a generic locking solution using this field. When a client wishes to update a record they must first successfully 'grab' the flag, then perform the changes, then (crucially) release the flag. You can implement a wait_for_flag mechanism (with appropriate timeout). Hopefully this is giving you a feel for the complexity. If not, ask yourself the following questions: 1) What if the client crashes out part way through, who will release the flag ? 2) You will need diferent locking types. What if a record is locked for delete and another client then requests a lock for update ? 3) How do you handle multiple updates to the same record ? Be aware that this solution will not sit well with DB transactions (you will need to lock all the records to be changed before starting the transaction) 4) Are you aware of transaction isolation levels ? Does your feature-weak DB offer transactions ? Other clients will probably experience dirty reads and you need to allow for this. If you still want to proceed, and your DB is networked, seriously consider writing a DB Server that sits on the same machine as the DB and administers changes (read-only access can still go direct but with the aforementioned dirty reads). Hope this puts you off the whole idea, Gary
If your RDBMS will not support what you need, one thing you can do is add one additional column to the table(s) that need row level locking. That column can simply be a timestamp. When the row is retrieved and inflated into an object, the timestamp is also retrieved. When the row is attempted to be updated, the where clause needs to be extended to indicate where the timestamp equals the value that was originally read. Also, the timestamp column needs to be updated as well upon success, but this simply extends the columns being updated. The first update will succeed. Subsequent ones that have the old timestamp will not because the where clause will return zero rows. Kind of brute force, but this mechanism does work.
Well, all these answers about using database locking aren't quite accurate if you need a more real time solution (such as an event / listener mechanism for instance). This is becoming quite necessary in larger and more robust business applications. In these cases, application level "locking" is necessary - database level locking just won't do. In essence, the type of locking you need is definitely based on the type of application that is being built.
<B>Rich Wardwell</B><BR> <A HREF="mailto:firstname.lastname@example.org" rel="nofollow">email@example.com</A> <BR>Sun Certified Programmer for the Java 2 Platform
Joined: Mar 10, 2000
Thanx Gary, dgschwind and Rich for your help, Let me be more specific.. I am actually sitting for the SCJD and there is this requirment on the Database (Random access file - Binary Db) of locking... their are methods with signature public void lock(int) and public void unlock(int) where int is the record id...which have to be implemented... Well the requirment is that multiple reads are possible but before writting I need a lock.. also -1 locks the file... So it is in this context that I asked the question.. Yes I am aware of the complexities as it is a network based system but I cant help it..... Thay is why I posted this.. I want to be sure of all the problems which will come my way... Any ideas or suggestions are welcome... my email is firstname.lastname@example.org (No I am not a hacker )