Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2004
    Location
    Sweden
    Posts
    3

    Smile Unanswered: pessimistic vs optimistic concurrency control

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

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: pessimistic vs optimistic concurrency control

    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.

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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

  4. #4
    Join Date
    Mar 2004
    Location
    Sweden
    Posts
    3
    Ok! Thanks a lot for the info!

    -Mr Monkeyboy

  5. #5
    Join Date
    May 2004
    Location
    Northern Ontario
    Posts
    1

    Smile

    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"

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Thanks, I hadn't come across that term before.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •