Could someone explain to me what the difference between pessimistic (for examle 2PL) and optimistic concurrency control is?

I have never heard of "2PL" before, but anyway:

Pessimistic means you actually lock the data when you select it to make sure nobody else can update it before you do. For example in Oracle you would SELECT ... FROM ... WHERE ... FOR UPDATE;

Optimistic means that you do not lock the data when you select it, but when you subsequently update it you check that it has not been updated by someone else in the meanwhile, otherwise your update fails. There are various ways to do this - record version numbers, last_update timestamps, or just check all the data values like this:

UPDATE ...
SET val1 = :new_val1, val2 = :new_val2, ...
WHERE key = :key
and val1 = :old_val1, val2 = :old_val2, ...;

(:old_val1, :new_val1 are variables holding the selected and modified values for column val1).

Pessimistic locking requires that a database session is maintained between the select and the update. In web-based applications, no database connection is maintained and so optimistic locking must be used.

I suspect that mrmonkeyboy meant 2PC, a common abbreviation for two phase commit. I can see a slight resemblance between pessimistic locking and two phase commit... Both of them are intensely statefull.

-PatP

Ok! Thanks a lot for the info!

-Mr Monkeyboy

FYI: Two Phase Locking Protocol ( 2PL )

"In a given transaction, all locks precede all unlocks, i.e. once a transaction has released a lock it cannot acquire any more locks.

2PL guarantees serializability for all transactions that go to conclusion"

Thanks, I hadn't come across that term before.