A lot of this thread has seemed confused to me.
Pessimistic verus optimistic is a coding strategy that is generally
unrelated to the DB/resource manager.
Pessimistic locking is named this way because the assumption is that data
will change unless you ensure it won't be changed while a "user" is
accessing the data for update. The idea is that a user will retrieve and
lock the data so that nobody else can update the data while the first user
has the data and the intent is to update the data. The locks are considered
to be held a "long time" because they are not released until the "user"
either updates the data or releases the locks (cancels the operation or
times out, for example). Concurrency is generally reduced because the data
is locked for a longer period of time. By holding the locks, the user is
assured that updates will be successful. The chance for deadlock increases
because locks are held longer.
Optimistic locking assumes the opposite, that conflicting updates are not
too likely. In this scheme, the user will retrieve the data, but it won't
lock it. If the user never updates the data, then nothing "bad" occurs.
Because the data is not locked, concurrency is higher. However, if the user
does update the data, then the DB/resource manager still has to lock the
data, check to see if any updates have occurred in the meantime since the
first user read the data, and if no changes have occurred, then the updates
occur and the locks are released. If the data was changed underneath, then
the transaction is generally aborted and the changes don't take place (and
the locks are released). Locks are still required, but they are held for a
short period of time, and hence deadlock is reduced and concurrency is
increased. (It's possible using optimistic locking to not rollback just
because the data has changed "in the row/record." For example, you may just
be incrementing a value (UPDATE tbl SET x=x+1), or you may be changing an
attribute that was not changed in some other transaction, etc. Generally,
though, most people do not do the update when there was an intervening
change, and instead let the user decide what to do after getting a fresh
copy of the data.)
Deadlock is quite separate and is related to locks and the order of updates.
You can have them regardless of whether optimistic or pessimistic locking is
used. They are probably reduced by optimistic only because the locks are
held for less time.
Isolation levels do matter for dealock since they affect whether the locker
is blocked or not. A read-lock, for example, will let others read the data
even if it's locked, but they won't be able to update it. A write-lock will
generally keep even a reader out -- to prevent dirty reads -- unless they
have specified that they'll allow dirty reads (each db has its own set of
isolation levels implemented).
David
===========================================================================
To unsubscribe, send email to [EMAIL PROTECTED] and include in the body
of the message "signoff EJB-INTEREST". For general help, send email to
[EMAIL PROTECTED] and include in the body of the message "help".
