Pessimistic vs optimistic locking in Oracle

Question:  What is the difference between the pessimistic and optimistic Oracle locking method?  I understand that pessimistic locking is good in same situations while optimistic Oracle locking is useful in other conditions.  What are the defaults for locking, pessimistic or optimistic, and how do I use them?

Answer:  Oracle allows you to choose the strategy for locking, either pessimistic or optimistic, depending on your needs.  This is the issue of pessimistic vs. optimistic locking:

Pessimistic locking:  The developer must declare their intent to update the row set.  This is done with the SELECT xxx FOR UPDATE clause.
 
Optimistic locking:  You re-read data and only update it if it did not change since the initial fetch.

When managing web-based Oracle databases, the traditional "select for update" locking is inappropriate, and Oracle professionals have struggled with alternative mechanisms to maintain data integrity using an "optimistic" coding strategy:

Re-read - When an update comes in from the internet, the Oracle code re-reads the data to ensure that there are no changes since the data was originally delivered.
 
Timestamp - Some Oracle shops add a timestamp column to enforce serial updates and prevent accidental overlaying of data.  Now in Oracle 10g and beyond we see the new rowscn pseudo-column and the rowdependencies clause.

Also, the ora_rowscn provides the System Change Number (SCN) associated with each individual row in a table when it was read.
 
One thing to note, by default Oracle will not track SCN's at the row level in a table, so you need to enable row level dependency tracking by using the ROWDEPENDENCIES keyword in the CREATE TABLE command.
 
create table customer (
  cust_id     number(10)   not null,
  cust_name   varchar2(50) not null
ROWDEPENDENCIES
tablespace users;
 
This rowdependencies clause changes the default behavior of Oracle to put an SCN on every row within the tables, and not just one SCN for every physical data block.  This is an answer to the optimistic strategy, much preferred over the pessimistic locking approach.

Also see my notes on row level locking.