Pessimistic Locking

[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.

Thanks
Aryan

[Paul Campbell]

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.

Thanks
Aryan

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.

[Aryan Khan]

Thanks Paul,
If they are not standards ..how can one ensure SQL portability. Many databases does not support "SELECT ...FOR UPDATE".
For concurrency and bottleneck, I agree!!!
Aryan

[Paul Sturrock]

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.