Originally posted by Aryan Khan: Hi all, Is Pessimistic locking (SELECT...FOR UPDATE ) an ANSI standard or any other standard? Or they are specific extensions from database vendors?
Also can pessimistic locking be achieved using only JDBC API and not using "SELECT ....FOR UPDATE " as part of my queries.
Pessimistic locking (and Optimistic Locking) are strategies for dealing with database collisions. They are best practices, not standards or vendor extensions.
You have to use Select for Update for pessimistic locking... however, keep in mind that pessimistic locking is not a scalable strategy. As you add users and transactions... wait for lock situations impact your transactions.
If you are just using JDBC, you'll need to provide per-database implementations for each pessimistic locking syntax. If you are using something like a DAO pattern this shouldn't have too much of an impact.
The alternative would be to use an ORM tool, which will give you portability without having to write per-dataabse code.