So called "Optismistic locking" is for database managment systems whereby write locks prevent readers from reading data (such as sqlserver, db2, informix, et.al.) I am a huge fan of so called pessimistic locking.The user has very clearly announced their intent to UPDATE THE DATA.The lockouts they refer to are easily handled with session timeouts (trivial) and the deadlock is so rare and would definitely be an application bug (in both Oracle and RDB).And to say that RDB only offers pessimisitic locking is false.
What the guy is saying is better to use optimistic locking with trigger to ensure concurrency. BTW: I never had your response back did I do something? Antoine I have this email in my list of "bad" emails: [email protected](i x'ed out your email name, you typed in the domain wrong) Perhaps, you used that one, the email bounced and I put it in my reject list for future questions.Watch your typing, the only way I can get back to you is to have you input a valid email address in the first place.If you have my book "Expert one on one Oracle", I cover this topic in detail.In a stateful environment (like client server, java servlets, things that maintain a session for you) I prefer pessimistic locking, whereby you select for update the data.It involves adding time stamp type columns to every table and having the update statement verify the timestamp. Is it possible Tom to have the response for the email bounced?
How annoying would it be for you to fill out a form, spend all of the time doing the work -- only to be told at the end "sorry, the data you are trying to update was updated -- PLEASE START ALL OVER AGAIN FROM THE VERY BEGINNING" Thats optimistic locking for you. More challenging than the technology is overcoming resistance from seasoned development professionals who have been using the trusted SELECT& FOR UPDATE for all of their Oracle careers. Optimistic locking is useful in stateless environments (such as mod_plsql and the like). Thanks again Tom, for your point of view it was excellent! My question was about Beginning Oracle Programming and Professional Oracle 8i Application Programming with Java, PL/SQL and XML. It's because I want to buy those books but I want to be sure of your contribution especially Professional Oracle 8i Application Programming with Java, PL/SQL and XML. October 06, 2002 - pm UTC I wrote one chapter in the Pro Oracle 8i -- not a very large contribution. My last question, if I want to be better with Oracle is it better to read a lot and experiment a lot, and be the only one DBA in a entreprise. - hence locking required to avoid duplicate While issuing, locking is required, to avoid 2 or more user to choose the same record. generation, I think it is better to use approach-2 (stock_number), so that rest of the users will not have any problem in accessing stock_table.