The mechanisms to link with other databases often ONLY permit optimistic buffering (Oracle is one exception I *think*), so rather than being the most useful, it's often all there is.

Some databases do not use (or may, for efficiency, have not selected) single-row locking - e.g. they may lock a 'page' of records rather than just one (e.g. Access / SQL Server). For pessimistic locking this granularity is most politely described as 'awkward': until you release the lock(s) the other records on the page (or whatever) are locked also (this is what happens in Access).

In systems where conflicts between users are rare, it can be more efficient to deal with locking issues when they occur rather than having the overhead of always issuing locks. Rolling back the operation may be quite involved though.

Pessimistic locking can lead to a deadly-embrace (a.k.a. deadlock) which may lock your system solid.

Pessimistic locking is...er...problematic on Wide Area Networks - consider issuing a lock in New Zealand against a database in the UK - things are likely to be sloooowww. There are techniques for locking across distributed databases and ensuring the serializability of transactions (variants on 2-phase locking, 2- and 3-phase commits, record-ownership-tokens, etc.)

Offline views and synchronisation of databases usually have to be performed optimistically.

Optimistic locking *can* lead to more problems with lost-updates, inconsistant-analysis (the value of a referred field changing during the update) - pessimistic locking may allow you to 'block' a file preventing reads / writes to other files without 'locking' them (i.e. mimicking a read-lock).

There really is no 'best' - it's a matter of what is needed by the application or available on the platform you are using. If you are designing a system that you think may be moved to a C/S environment without pessimistic locking you are probably better off designing it that way from the start.

With optimistic locking you can usually catch the conflict and decide what to do about it in code: it's not neccessarily the case that the 'newest' user gets the update rights.

Also, you can mimic pessimistic locking with optimistic locking if you really have to - e.g. have a 'lock' field on each record. You will need to have mechanisms for releasing locks in the event of failure or timeout.


Database Systems, A practical Approach (Connolly, Begg, Strachan) Addison-Wesley ISBN 0-201-42277-8 has a very good section on concurrency and distributed databases.