Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2004
    Provided Answers: 4

    Unanswered: isolation level and optimistic locking

    I read about the different isolation levels (Repeatable Read, Read Stability, Cursor Stability and Uncommitted Read). But it's still unclear when i should use a certain isolation level.

    We use optimistic locking to prevent concurrency problems. If this would be the golden bullet solution, there would be no need for these isolation levels. What am I missing ?
    With kind regards . . . . . SQL Server 2000/2005/2012

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  2. #2
    Join Date
    May 2003
    The isolation level determines how long a read lock is held, except for a few exceptions. One exception is UR, which means no locks are held, and existing locks are ignored by the SQL select run with UR.

    Update locks are held until a commit takes place, regardless of isolation level.

    For maximum concurency of most applications, use CS. This will release the row lock on a read when the cursor moves off the row and DB2 moves to the next row (if using cursor processing).

    If you need the result set to remain consistant while the select statement is processing the rows in your query, you may need RS, which holds the read locks until the SQL select statement is finished.

    If you need to read the same data more than once in a unit of work and it must be the same each time, then use RR, which hold the read locks until a commit.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

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